In this article, we will discuss how to work with the VBA MsgBox variable. We will explain the different types of variables available in VBA and use them in VBA code to perform different actions like checking variable types in an Excel sheet, using only one variable to store different types of data, choosing the proper variable type, and so on.
How to Launch VBA Editor in Excel
In this section, we are going to demonstrate how to launch VBA Editor & create a VBA module in Excel. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.
- First, we go to the Developer
- Then we will select Visual Basic.
Then a new window will pop up. Then we are going to follow these steps,
- First, we will select Insert,
- Then we are going to select Module.
- A new Module will be created.
How to Display Value of a Variable in MsgBox in Excel VBA
There are two ways to display a variable in MsgBox. The first one is declaring the variable and then using the MsgBox command later. The following code is an example of that.
num = 23
MsgBox num
var1 = "workbook"
MsgBox var1The other way of doing this is to declare the variable in MsgBox command indeed.
MsgBox 24
MsgBox "World"One thing is worth mentioning here. While using MsgBox to display the string, we have to use quotation marks. But it is not required in case of numbers.
5 Different Cases That Will Teach You to Work with a Variable in Excel VBA MsgBox
In this section, we will demonstrate five practical examples of using VBA MsgBox Variable in Excel with appropriate illustrations.
1. MsgBox with Improper Variable Type Declaration
We face different types of errors while working in Excel VBA. Run-time error ‘13’ is one of them which occurs when the variable is properly not defined.
Sub Improper_variable()
Dim var As Integer
var = "I love Excel"
MsgBox var
End SubIn the above code, we defined the “var” as integer but assigned a string value. So, when we run the code, the warning appeared. To avoid this error,there is a nice trick of declaring the variable as Variant. Now, we will declare the variable as a variant and observe the output.
Sub Proper_variable()
Dim var As Variant
var = "I love Excel"
MsgBox var
End SubThe code in the Editor Window will look like the image below.
🔎 How Does the Code Work?
Dim var As VariantDeclaring “var” as Variant type variable.
MsgBox varShowing the value of “var” in the MsgBox
Execution and Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, we will see the following result.
Read More: VBA MsgBox Format in Excel
2. VBA MsgBox Variable Without Declaring Data Type
Sometimes we need to use a single variable to store more than one data type. In the following code, we will assign 3 types of variables in “var1”.
Sub Var_Without_type()
Dim var1
var1 = 76
MsgBox var1
var1 = "Microsoft excel"
MsgBox var1
var1 = 55.75
MsgBox var1
End SubThe code in the Editor looks like the image below.
🔎 How Does the Code Work?
var1 = 76Assigning an Integer value to “var1”
var1 = "Microsoft excel"This line of the code assigns a String to “var1”.Also,it deletes the previous value of “var1”.
var1 = 55.75When the code executes this line, the value of “var1” becomes 55.75
Execution and Output
We will execute the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, we will see 3 consecutive MsgBox as output. All of them are various values of “var1”. We click ok on the MsgBox and immediately the code updates the value of “var1”. The last MsgBox looks like the image below.
3. VBA MsgBox with Static Vs Non-Static Variable
We have to handle different types of variables in VBA. Static and Non-static are two of them. In the following section, we will see the use of these 2 variables in Excel VBA.
Sub static_var()
Dim var1 As Integer
Static num As Integer
var1 = var1 + 1
num = num + 1
MsgBox "The value of var1 is: " & var1 & _
" and the value of num is: " & num
End SubAfter writing the code in VBA Editor, it looks like the image below.
🔎 How Does the Code Work?
Dim var1 As IntegerThis line declares a Non-static integer variable named “var1”.
Static num As IntegerDeclaring a variable named “num” as a static integer variable.
var1 = var1 + 1
num = num + 1Each time we execute the code, it updates the value of “var1”,not the value of “num”. It’s because we have declared “num” as static variable.
Execution and Viewing Output
In the code, we do the same operation on both “num” and “var1” variables. But when we execute the code several times,we see that the value of “num” remains the same while “var1” will remain the same. When we run the code for the first time, the output will look like below.
If we run the code several times, the output MsgBox will look like the image below.
Read More: How to Use Excel VBA MsgBox Title
4. Combining Integer and String Type Variables in MsgBox
In the MsgBox, it is possible to show both string and integer type data. The following example will help you do that.
Sub comb_var()
Dim customer As String
Dim val As Integer
Dim currencyVal As Currency
customer = InputBox("Enter the customer serial No:")
val = Cells(customer + 4, 4)
customer = Cells(customer + 4, 3)
currencyVal = val ' Convert the value to the Currency data type
MsgBox "The customer " & customer & " has purchased " & _
Format(currencyVal, "$#,##0.00") & " from the store"
End SubWhen we write the code in VBA Editor, it looks like the image below.
🔎 How Does the Code Work?
customer = InputBox("Enter the customer serial No:")
val = Cells(customer + 4, 4)This code snippet takes the Serial number of the customer as input and then assigns the corresponding purchase of that particular customer into the variable “val”.
MsgBox "The customer " & customer & _
" has purchased " & FormatCurrency(val) & " from store"This is our desired line in the whole code. This line shows the way to join two types of variables in MsgBox. We use the Ampersand operator to combine them. Important thing to note here is the use of space when required. Perfect use of space will make the MsgBox look perfect.
Executing the Code
Now, let us run the code by pressing F5 or by opening the Macro window from the Developer tab and we will see the output as follows.In the MsgBox we see both the string and Integer combined,where the string value is the name of the customer and the integer value is the amount purchased by the customer.
Note: We can use a defined variable as different arguments too like we have used here with “customer” while defining “val”.
Read More: Excel VBA: Show Multiple Values with Multiple Variables in MsgBox
5. Showing Variable Types in MsgBox
In order to check the variable type, we will run the code below.
Sub CheckVariableType()
Dim cell As Range
Set cell = Selection 'Assigning the address of selected cell to cell
'Now,check the type of variable
If IsNumeric(cell.Value) And cell.Value = Int(cell.Value) Then
MsgBox "The cell contains an integer."
ElseIf varType(cell.Value) = vbLong Then
MsgBox "The cell contains a long integer."
ElseIf varType(cell.Value) = vbDouble Then
MsgBox "The cell contains a double-precision floating-point number."
ElseIf varType(cell.Value) = vbCurrency Then
MsgBox "The cell contains a currency value."
ElseIf varType(cell.Value) = vbDate Then
MsgBox "The cell contains a date value."
Else
MsgBox "The cell contains an unknown data type."
End If
End SubWhen we write the code in the VBA editor, the editor looks like the image below.
🔎 How Does the Code Work?
If IsNumeric(cell.Value) And cell.Value = Int(cell.Value) Then- IsNumeric(cell.Value) checks if the value in the cell is a numeric value. If it is not a numeric value, the condition will return False and the code inside the If statement will not execute.
- Value = Int(cell.Value) checks if the value in the cell is an integer. If it is not an integer, the condition will return False and the code inside the If statement will not execute.
ElseIf varType(cell.Value) = vbCurrency Then- varType(cell.Value) gets the data type of the value in the cell.
- vbCurrency is a constant that represents the currency data type in VBA
Execution and Viewing Output
In order to see the output, we assign the code to a button named “Variable Type” since we have to run the code several times and check the variable type. After that we select a cell and click on the button. The button will execute the code and show the variable type in that cell. The output will look like the image below.
Read More: VBA MsgBox to Return Values in Excel
How Display Values of Multiple Variables in MsgBox with Excel VBA
Sometimes we have to work with more than one variable and display them in a single MsgBox. In the following example, we will see how to write a code for this particular work.
Sub MultiVariables()
Dim var1 As String
Dim var2 As Integer
Dim var3 As Double
Dim var4 As Date
Dim var5 As Boolean
var1 = "Moscow"
var2 = 1311
var3 = 20.19
var4 = Date
var5 = True
MsgBox "Variable 1: " & var1 & vbCrLf & _
"Variable 2: " & var2 & vbCrLf & _
"Variable 3: " & var3 & vbCrLf & _
"Variable 4: " & var4 & vbCrLf & _
"Variable 5: " & var5, _
vbInformation, "Variables"
End SubThe above code in the Editor looks like the image below.
🔎 How Does the Code Work?
MsgBox "Variable 1: " & var1 & vbCrLf & _Variable 1: is a string that represents the label of the first variable.
& : The concatenation operator in VBA. It combines two expressions into a single string.
vbCrLf : A constant that represents a line break in VBA. We use it to add a new line between the first and second variables we want to display.
vbInformation, "Variables"vbInformation: This is a constant in VBA that specifies the type of message box to display. In this case, it specifies that an information message box should be displayed.
Variables: This specifies the title of the message box.
Execution and Viewing Output
To view the output of the code, we press F5 and see the output like the image below.
How to Store MsgBox Input in a Variable with Excel VBA
Sometimes we need to store the response of the user into a variable. The following example will cover the topic with a VBA code.
Sub StoreMsgBoxInput()
Dim userInput As Variant
userInput = MsgBox("Do you want to add a new sheet?", _
vbYesNo, "Question")
If userInput = vbYes Then
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Sheets.Add
newSheet.Name = "New Sheet"
Else
MsgBox "New Sheet not created"
End If
End SubThe VBA Editor with code looks like the image below.
🔎 How Does the Code Work?
userInput = MsgBox("Do you want to add a new sheet?", _
vbYesNo, "Question")When the user clicks one of the buttons in the message box, the MsgBox function will return a value that corresponds to the button that was clicked. The “userInput” variable is then assigned the value returned by the MsgBox function.
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Sheets.AddThe first line declares a variable called “newSheet” and specifies that it should be of the Worksheet data type. While the second line creates a new worksheet in the current workbook using the Add method of the Sheets collection.
Execution and Viewing Output
If we run the code, a MsgBox will appear with 2 options. If we click “Yes” then a sheet will be added to the workbook. While choosing “No” will display a MsgBox like the image below.
Read More: How to Use MsgBox and InputBox in VBA Excel
Formatting Values in MsgBox with Excel VBA
If we have a string containing a number, we can convert that to either currency,percentage or date format depending on demand. In the following code,we will convert a string to currency value.
Sub Format_Currency()
Dim cur As String
cur = Format(32.22574, "Currency")
MsgBox cur
End SubWhen we write the code in the VBA Editor, It looks like the image below.
🔎 How Does the Code Work?
cur = Format(32.22574, "Currency")This code snippet converts the string “cur” into a currency value. Here, 32.22574 is the value that we want to convert and “Currency” is the format into which we want to convert the string.
Execution and Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, we will see the following result.
Note: We may need to convert the string into other formats like percentage, date or other. In that case, we just need to replace the “Currency” with “Percent” or “Long Date” accordingly.
How to Create MsgBox with Options in Excel VBA
vbYesNo Option in VBA
There are a good number of options available in VBA MsgBox. In the following example,we will see the Yes/No option in MsgBox.
Sub MsgBox_option()
If MsgBox("Do you want to sort the worksheet?", vbYesNo) = vbYes Then
Dim sortColumn As Long
sortColumn = 3 ' Column that we want to sort
Dim lastRow As Long
lastRow = Cells(Rows.Count, sortColumn).End(xlUp).Row
Dim temp As Variant
For i = 5 To lastRow
For j = i + 1 To lastRow
If Cells(i, sortColumn).Value > Cells(j, sortColumn).Value Then
' Swap the values of the desired column
temp = Cells(i, sortColumn).Value
Cells(i, sortColumn).Value = Cells(j, sortColumn).Value
Cells(j, sortColumn).Value = temp
'Swap the values of other column as well
temp = Cells(i, sortColumn - 1).Value
Cells(i, sortColumn - 1).Value = Cells(j, sortColumn - 1).Value
Cells(j, sortColumn - 1).Value = temp
End If
Next j
Next i
Else
MsgBox "No Change in Worksheet"
End If
End SubThe above code in the Editor looks like the image below.
🔎 How Does the Code Work?
If MsgBox("Do you want to sort the worksheet?", vbYesNo) = vbYes ThenWhen this line is executed, a Msgbox appears with the question and 2 buttons Yes/No respectively. If the user presses “Yes” then something happens.
For i = 5 To lastRow
For j = i + 1 To lastRowThis code represents a nested For loop in VBA. The loop starts at row 5 and iterates through each subsequent row up to the lastRow variable.
If Cells(i, sortColumn).Value > Cells(j, sortColumn).Value Then- For each row, the loop compares the value in the current row (i) to the value in the next row (j) in the column specified by sortColumn.
- If the value in row i is greater than the value in row j, then the two rows are out of order and need to be swapped.
Execution and Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, we will see the following result.
vbOkCancel Option
Often we have to interact with users by generating a prompt and do something according to their response. We can do it easily by vbokcancel.
Sub vbokcancel()
Dim var1 As Integer
var1 = MsgBox("Do you want to delete this row?", vbokcancel, "Delete Selected Row")
If var1 = vbOK Then
ActiveSheet.Rows(Selection.Row).Delete
Else
MsgBox "Deletion cancelled.", vbInformation, "Cancelled"
End If
End SubIf we write the code in the Editor, it will look like the image below.
🔎 How Does the Code Work?
MsgBox("Do you want to delete this row?", vbokcancel, "Delete Selected Row")vbokcancel is a constant that specifies the type of message box to be displayed. It indicates that the message box should have an “OK” and “Cancel” button.
If var1 = vbOK ThenIt checks if the value of the variable “var1” is equal to the constant “vbOK”.
MsgBox "Deletion cancelled.", vbInformation, "Cancelled"“vbInformation” is a constant that specifies the type of message box to be displayed while “Cancelled”, is the title of the message box.
ActiveSheet.Rows(Selection.Row).DeleteThis code will delete the entire row of the currently selected cell in the active worksheet.
Execution and Output
In order to see the output,we will run the code by opening the Macro window from the Developer tab. We will select a row and select the Macro that we want to run. A MsgBox will appear like the image below.
If the user press “OK” then there will be some operation on the worksheet and the code will delete the 7th row(selected row) of the worksheet.
vbAbortRetryIgnore Option in MsgBox
There is another type of MsgBox containing the options “Abort”,”Retry”,”Ignore”. In the following code, we will see how to use this MsgBox in VBA Code.
Sub vbRetry_Ignore()
Dim var1 As VbMsgBoxResult
var1 = MsgBox("An error occurred. Do you want to abort, retry, or ignore?" _
, vbAbortRetryIgnore, "Error")
Select Case var1
Case vbAbort
MsgBox "Operation aborted.", vbExclamation, "Abort"
Case vbRetry
MsgBox "Operation retried.", vbInformation, "Retry"
Case vbIgnore
MsgBox "Error ignored.", vbInformation, "Ignore"
End Select
End SubThe code in the Editor looks like the image below.
🔎 How Does the Code Work?
MsgBox("An error occurred. Do you want to abort, retry, or ignore?" _
, vbAbortRetryIgnore, "Error")This code displays a message box with a prompt for the user to choose one of three options: “Abort”, “Retry”, or “Ignore”.
Case vbAbort
MsgBox "Operation aborted.", vbExclamation, "Abort"This block of code is executed when the user chooses the “Abort” option in response to a message box prompt. The code displays another message box with a message indicating that the operation has been aborted.
Execution and Output
We will run the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, we will see the following result.
Frequently Asked Questions (FAQs)
1. What is a MsgBox in VBA?
MsgBox is a built-in VBA function that displays a message box with a message and buttons for the user to respond.
2. How do I use a variable in a MsgBox in VBA?
To use a variable in a MsgBox in VBA, you can concatenate the variable with the message using the Ampersand( & ) operator.
3. What are the different types of buttons available in a MsgBox in VBA?
The different types of buttons available in a MsgBox in VBA are vbOKOnly, vbOKCancel, vbAbortRetryIgnore, vbYesNoCancel, vbYesNo, and vbRetryCancel.
4. Can I use a MsgBox to prompt the user for input in VBA?
Yes, you can use a MsgBox to prompt the user for input in VBA. You can use the InputBox function instead of the MsgBox function to get input from the user.
Things to Remember
- We can capture the user’s response to a message box using a variable and use it in the code to perform different actions depending on the response.
- When using VBA MsgBox variables, it is important to consider the user experience and make the message box as clear and concise as possible.
- Test the macros thoroughly, including the message boxes, to ensure they work as intended.
- Using VBA MsgBox and prompting the user only when it is necessary.
- We can use VBA MsgBox variables in other Office applications besides Excel.
Download Practice Workbook
You may download the following workbook to practice yourself.
Conclusion
That is the end of this article regarding Excel VBA MsgBox Variable. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.
