Method 1 – MsgBox with Improper Variable Type Declaration
While working in Excel VBA, we encounter various errors. Run-time error ‘13’ occurs when a variable is not properly defined.
Sub Improper_variable()
Dim var As Integer
var = "I love Excel"
MsgBox var
End SubWe defined the “var” as integer but assigned a string value. We ran the code, and the warning appeared. To avoid this error, there is a nice trick of declaring the variable as Variant. 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 opening the Macro window from the Developer tab. We will see the following result.
Method 2 – VBA MsgBox Variable Without Declaring Data Type
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"The code assigns a String to “var1”.Also,it deletes the previous value of “var1”.
var1 = 55.75The code executes this line, the value of “var1” becomes 55.75
Execution and Output
We will execute the code by clicking F5 or opening the Macro window from the Developer tab. See three consecutive MsgBoxes 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.
Method 3 – VBA MsgBox with Static Vs Non-Static Variable
Handle different types of variables in VBA. Two of them are static and non-static. In the following section, we will see how to use 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 perform the same operation on both “num” and “var1” variables. However, when we execute the code several times,we see that the value of “num” remains the same while “var1” remains the same. When we run the code for the first time.
Run the code several times, and the output MsgBox will look like the image below.
Method 4 – Combining Integer and String Type Variables in MsgBox
In the MsgBox, 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 SubWrite 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 customer’s Serial number as input and then assigns the corresponding purchase of that particular customer to the variable “val.”
MsgBox "The customer " & customer & _
" has purchased " & FormatCurrency(val) & " from store"This is the desired line in the whole code. It shows how to join two types of variables in MsgBox using the Ampersand operator. An 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; we have used here with “customer” while defining “val”.
Method 5 – Showing Variable Types in MsgBox
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 SubWrite 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
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. 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.
How Display Values of Multiple Variables in MsgBox with Excel VBA
Sometimes we have to work with multiple variables and display them in a single MsgBox. See how to write 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 code output, we press F5 and see the output like the image below.
How to Store MsgBox Input in a Variable with Excel VBA
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. The second line creates a new worksheet in the current workbook using the Add method of the Sheets collection.
Execution and Viewing Output
Run the code, a MsgBox will appear with 2 options. If we click “Yes” then a sheet will be added to the workbook. Choosing “No” will display a MsgBox like the image below.
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 a 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. 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
Run the code by clicking F5 or by opening the Macro window from the Developer tab. 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
A good number of options are 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
Run the code by clicking F5 or by opening the Macro window from the Developer tab. See the following result.
vbOkCancel Option
Have to interact with users by generating a prompt and doing something according to their response. 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
See the output,we will run the code by opening the Macro window from the Developer tab. 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, 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 code block 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.
