30 Advanced Excel Tips for 2026

Excel Champs / VBA / Variable in a Message Box
By Puneet Gogia (Microsoft MVP)

Variable in a Message Box

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

Put the variable after MsgBox: MsgBox total shows its value. To add a label, join text and the variable with &: MsgBox "Total sales: " & total.

Use vbNewLine to put values on separate lines, and the third argument for a title:

MsgBox "Region: " & region & vbNewLine & _
       "Sales: " & Format(total, "$#,##0.00"), vbInformation, "Summary"

Format numbers and dates before showing them. MsgBox shows the raw value, such as 1234.5 instead of $1,234.50, and dates in the PC’s regional format. Use Format(total, "#,##0.00") or Format(Date, "dd mmm yyyy").

Join with &, not +: "Total: " + total stops with a type mismatch when total is a number. If the box comes up empty, the variable name is probably misspelt; Option Explicit at the top of the module catches that before the code runs.

You can also use a variable in a message box. Let’s say you have a variable that stores a specific value.

Now you can use that variable in the message box to show the value to the user. In the following example, we have used a macro with a For loop and a function to generate a random number.

vba-variable-messagebox

When you run this code, it loops 10 times, and in each loop, it generates a random number and stores that number in the variable. In the next line of code, we used the same variable to show the value in the message box. So you get 10 message boxes, one after another.

Sub generateRandomNumber()

Dim i As Integer, rNumber As Integer
i = 10

    For i = 1 To i
        rNumber = WorksheetFunction.RandBetween(1000, 2000)
        MsgBox rNumber         
    Next i

End Sub

In this code, the loop’s end value (i, which is 10) is read only once, when the loop starts. So it runs 10 times even though the same variable i is also the loop counter. In your own code, it’s clearer to use a separate variable or just write For i = 1 To 10.

Combine Text and a Variable in a Message Box

Most of the time, you don’t want to show just the value. You want to tell the user what that value is. For this, you can join the text and the variable with the & operator.

Sub messageWithVariable()
Dim userName As String
userName = Application.UserName
MsgBox "Hello " & userName & ", your report is ready."
End Sub

Make sure to add a space inside the quotation marks wherever you need one, as the & operator doesn’t add any space on its own.

Show Multiple Variables on Separate Lines

If you want to show more than one variable, you can put each one on its own line using vbNewLine (you can also use vbCrLf).

Sub multipleVariables()
Dim totalSales As Double, totalOrders As Long
totalSales = WorksheetFunction.Sum(Range("B2:B11"))
totalOrders = WorksheetFunction.Count(Range("B2:B11"))
MsgBox "Total Sales: " & Format(totalSales, "#,##0.00") & vbNewLine & _
"Total Orders: " & totalOrders
End Sub

Here, I have also used the Format function to show the sales value with a thousands separator and two decimals. You can use Format in the same way for a date variable, like Format(myDate, “dd-mmm-yyyy”).

Use a Variable for the Title and Get the User’s Answer

A variable can go into the title of the message box as well. And when the message box has buttons like Yes and No, you can store the button the user clicks in another variable and use it in your code.

Sub variableTitleAndAnswer()
Dim sheetName As String, answer As VbMsgBoxResult
sheetName = ActiveSheet.Name
answer = MsgBox("Do you want to clear this sheet?", vbYesNo + vbQuestion, sheetName)
If answer = vbYes Then
ActiveSheet.Cells.ClearContents
End If
End Sub

In this code, the sheet name shows up as the title of the message box. If you leave the title out, Excel shows its own name there.

Show a Cell Value from a Range Variable

If your variable is a range, use its Value property in the message box. This works for a single cell. A range with more than one cell gives you a “Type mismatch” error, so pick one cell from it or loop through the cells.

Sub rangeVariable()
Dim myCell As Range
Set myCell = Range("A1")
MsgBox "The value in " & myCell.Address & " is " & myCell.Value
End Sub

Frequently Asked Questions

How do I show a variable without 10 message boxes in a loop?

Add each value to a text variable inside the loop, and show that variable in one message box after the loop. Or use Debug.Print instead of MsgBox to send the values to the Immediate Window (Ctrl + G in the VBA editor).

Is there a limit to how much text a message box can show?

Yes. The message (the prompt) can be about 1,024 characters long, depending on the width of the characters. Anything longer than that gets cut off.

Why do I get an error when I show a variable in a message box?

The two common reasons are a range variable that holds more than one cell (Type mismatch) and a variable that holds Null (Invalid use of Null). An object variable that was never set with Set also gives an error.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel