Different Cases That Will Teach You to Work with a Variable in Excel VBA MsgBox: 5 Methods

Method 1 – MsgBox with Improper Variable Type Declaration

Code with Improper Variable 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 Sub

We 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 Sub

The code in the Editor Window will look like the image below.

Code with Proper Variable Declaration

How Does the Code Work?

Dim var As Variant

Declaring “var” as Variant type variable.

MsgBox var

Showing 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.

VBA MsgBox Variable Properly Defined


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 Sub

The code in the Editor looks like the image below.

Variable Without Declaring Type

How Does the Code Work?

var1 = 76

Assigning 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.75

The 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.

Output of VBA MsgBox Variable Without Declaring Type


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 Sub

After writing the code in VBA Editor, it looks like the image below.

Code showing Difference of Static and Non-static Variable

How Does the Code Work?

Dim var1 As Integer

This line declares a Non-static integer variable named “var1”.

Static num As Integer

Declaring a variable named “num” as a static integer variable.

var1 = var1 + 1
num = num + 1

Each 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.

First Output of the Code

Run the code several times, and the output MsgBox will look like the image below.

Output After Several Runs

 


Method 4 – Combining Integer and String Type Variables in MsgBox

Combining Integer and String Variable

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 Sub

Write the code in VBA Editor, it looks like the image below.

Code to Combine Integer and String Type Variable

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.

Output of Combining String and Integer Variables

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

Overview of checking Variable type

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 Sub

Write the code in the VBA editor, the editor looks like the image below.

Code to Identify Variable Type

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.

Determine the Variable Type by Clicking Buttons

 


How Display Values of Multiple Variables in MsgBox with Excel VBA

Display Multiple Variables in MsgBox

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 Sub

The above code in the Editor looks like the image below.

Code to Display Multiple Variable in VBA

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.

Output of Multiple Variable in MsgBox


How to Store MsgBox Input in a Variable with Excel VBA

Overview of Storing MsgBox Input in Variable

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 Sub

The VBA Editor with code looks like the image below.

Code to Store Input in a Variable

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.Add

The 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.

Output of Storing MsgBox Input in a Variable

 


Formatting Values in MsgBox with Excel VBA

Formatting MsgBox for Currency Value

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 Sub

When we write the code in the VBA Editor, It looks like the image below.

Code to Convert String Variable

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.

String Converted into Currency

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 Sub

The above code in the Editor looks like the image below.

VBA Code to Show Yes-No Option in MsgBox

How Does the Code Work?

If MsgBox("Do you want to sort the worksheet?", vbYesNo) = vbYes Then

When 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 lastRow

This 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.

Excel VBA MsgBox with Yes-No Option


vbOkCancel Option

Overview of vbokcancel

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 Sub

If we write the code in the Editor, it will look like the image below.

Code of vbokcancel Option in VBA

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 Then

It 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).Delete

This 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.

Output MsgBox with vbokcancel

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.

Final Output of the Code


vbAbortRetryIgnore Option in MsgBox

Overview of vbAbortRetryIgnore Option in VBA

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 Sub

The code in the Editor looks like the image below.

Code of vbAbortRetryIgnore in the VBA Editor

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.

Output of the Code


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.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Junaed-Ar-Rahman
Junaed-Ar-Rahman

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo