In Excel VBA, display a variable in a message box by passing it to MsgBox, for example MsgBox customerName. To combine a value with explanatory text, join the pieces with the VBA concatenation operator &, as in MsgBox "Customer: " & customerName.
The Basic MsgBox Pattern
MsgBox opens a dialog and shows the prompt you provide. The prompt can be a string variable, a literal message, or an expression that combines text and values. This small macro assigns a value and displays it:
Sub ShowName()
Dim customerName As String
customerName = "Riley Chen"
MsgBox customerName
End Sub
Put the procedure in a standard VBA module, then run it. The dialog displays Riley Chen. To add context, build a message from text and the variable:
Sub ShowNamedMessage()
Dim customerName As String
Dim messageText As String
customerName = "Riley Chen"
messageText = "Current customer: " & customerName
MsgBox messageText, vbInformation, "Customer lookup"
End Sub
The second argument selects the buttons and icon; vbInformation shows an information icon. The third argument supplies the dialog title. Both are optional, so a one-argument call is enough for a quick check. The basic syntax is MsgBox(prompt, buttons, title), with additional optional help-related arguments available for specialized applications.
How the Prompt Handles Different Values
A message box ultimately displays text, while VBA variables can hold numbers, dates, Boolean values, or other data types. VBA can often convert a simple value for display, but using CStr or a formatting function makes the conversion intentional and easier to read. For example, with an amount stored as a number, concatenate the text and the formatted result:
Sub ShowAmount()
Dim amount As Currency
Dim messageText As String
amount = 1250.5
messageText = "Amount: $" & Format$(amount, "#,##0.00")
MsgBox messageText, vbInformation, "Amount"
End Sub
This displays Amount: $1,250.50. The format string requests a thousands separator and two decimal places. The dollar sign in this example is literal text; change it if the workbook uses another currency. For an ordinary integer, CStr(count) converts the number to a string. For a date, Format$(dueDate, "yyyy-mm-dd") gives a clear year-month-day display rather than relying on the computer’s regional date format.
Use & to join values and text. The plus sign can mean numeric addition in VBA, so using it to assemble a message can produce confusing behavior when the values are numbers. Explicit concatenation communicates that the goal is to build text, not calculate a sum.
Display a Cell’s Value
To show a worksheet value, read it into a variable and pass a text version to the message box. This example reads cell A1 on a specifically named worksheet:
Sub ShowCellA1()
Dim cellValue As Variant
Dim messageText As String
cellValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
messageText = "A1 contains: " & CStr(cellValue)
MsgBox messageText, vbInformation, "Cell value"
End Sub
Replace Sheet1 with the actual worksheet tab name. ThisWorkbook means the workbook containing the VBA code; that is often preferable to ActiveWorkbook, which means whichever workbook is currently active. A Variant is useful here because the cell might contain different kinds of values. For a known numeric cell, you can instead declare an appropriate type, such as Double or Currency, and format it as needed.
A cell can also contain an Excel error such as #N/A. Calling CStr on an error value can cause a type mismatch, so a macro intended for arbitrary cells should check for special values first:
Sub ShowCellValueSafely()
Dim cellValue As Variant
Dim messageText As String
cellValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
If IsError(cellValue) Then
messageText = "Cell A1 contains an Excel error."
ElseIf IsNull(cellValue) Then
messageText = "Cell A1 contains Null."
ElseIf IsEmpty(cellValue) Then
messageText = "Cell A1 is empty."
Else
messageText = "A1 contains: " & CStr(cellValue)
End If
MsgBox messageText, vbInformation, "Cell value"
End Sub
Excel worksheet cells are more commonly blank or contain an error than contain VBA’s special Null value, but the check avoids trying to convert Null if the variable can receive it from another source. Note that a formula returning an empty text string is not necessarily the same thing as a truly empty cell; its displayed result may look blank even though the cell contains a formula.
Choose the Message, Buttons, and Title
Use a message box when a person needs to notice a short result, warning, or confirmation. Its optional buttons argument can choose common button and icon combinations. For example, vbOKOnly displays an OK button, while vbYesNo displays Yes and No buttons. The return value tells the macro which button was selected, so a confirmation dialog should inspect that result rather than merely showing the question.
Sub ConfirmOverwrite()
Dim answer As VbMsgBoxResult
answer = MsgBox("Replace the existing report?", vbYesNo + vbQuestion, "Confirm")
If answer = vbYes Then
MsgBox "The answer was Yes."
Else
MsgBox "The answer was No."
End If
End Sub
Here vbYesNo + vbQuestion combines a button choice with an icon choice. The returned value is stored as VbMsgBoxResult, an enumeration designed for message-box responses. In a real macro, put the work that should happen after Yes inside the corresponding branch. Do not assume a user will always choose the affirmative option.
Keep prompts brief and specific. A useful title identifies the task, and the prompt tells the reader what the value means. “42” may be technically correct but ambiguous; “Rows processed: 42” is much more helpful. A message box pauses the running procedure until the user dismisses it, so it is best for occasional interaction rather than reporting every value in a long loop.
Where to Put and Run the Macro
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module in the editor, and paste a complete procedure into the module. Return to Excel and run it from Developer > Macros, select the macro, and choose Run. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.
On Excel for Mac, use Developer > Visual Basic to open the editor if the Developer tab is available, then add a standard module and run the procedure through Developer > Macros. Keyboard function keys can depend on the Mac’s keyboard settings, so the ribbon route is a reliable alternative. Excel for the web does not run VBA macros; open the workbook in the desktop Excel application to use this code.
Save a macro-enabled workbook as an .xlsm file if you need to retain the VBA code. Keep a separate copy of the workbook before running a macro that changes data: VBA actions cannot be undone with Ctrl+Z. A simple message-box macro only displays information, but later edits to a procedure may alter cells or other workbook content. Enable macros only in workbooks you trust.
Common Problems and Their Fixes
- The dialog shows a variable’s name instead of its value. Check that the variable is followed by the variable itself, not enclosed in quotation marks.
MsgBox customerNamedisplays its contents;MsgBox "customerName"displays the literal word customerName. - The macro reports a type mismatch. Check whether the value is an Excel error or Null before converting it with
CStr. Use aVariantwhen a cell may contain several data types, then handle special cases. - A number appears with an unexpected format. A raw conversion may use regional conventions or omit desired decimals. Use
Format$with a format string when the display needs a particular number of decimal places or date layout. - The macro is not listed or will not run. Confirm that the procedure is a
Subin a standard module, the workbook is saved as.xlsmif the code must be retained, and macros are permitted by the organization’s security settings. Excel for the web cannot execute VBA. - The dialog interrupts a repeated process. A message box waits for a click every time it runs. Move it outside a loop or use a noninteractive debugging method when monitoring many values.
For troubleshooting, a message box is convenient because it makes a single value visible immediately, but it changes the macro’s flow by waiting for a response. In the VBA editor, Debug.Print can send diagnostic text to the Immediate window without opening a dialog. That is often better when a procedure processes many rows. Remove temporary diagnostic statements or leave clear comments so they are not mistaken for normal user-facing output.
When a MsgBox Is Not the Best Display
A message box suits a short, temporary notice or a decision that needs a response. It is not a durable place to present a report: the user must dismiss it, the text cannot be conveniently compared with nearby worksheet values, and repeated pop-ups become disruptive. If the goal is to show a calculated result as part of the workbook, write it to a cell instead. If the goal is to monitor code while developing, use the Immediate window or the VBA editor’s debugging tools.
For example, a one-off macro can show a total with a message box, while a recurring report is usually clearer when the macro writes that total into a labeled worksheet cell. A worksheet cell remains visible, can be formatted, and can be referenced by other formulas. The choice is not about which method is more correct; it depends on whether the information is a brief interaction, a persistent result, or a developer diagnostic.
Frequently Asked Questions
How do I show text and a variable together in a VBA MsgBox?
Join the text and variable with &, for example MsgBox "Total: " & CStr(total). Add a title as a later argument if a descriptive dialog heading would help.
Can I display a cell value in a MsgBox?
Yes. Read the cell into a variable, such as cellValue = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value, then display it. Check for an error value before converting arbitrary cell contents to text.
Why does my MsgBox show the variable name?
If the name is inside quotation marks, VBA treats it as literal text. Remove those quotes when you want the variable’s contents, and make sure the variable has been assigned a value before the call.
How do I format a number in a VBA message box?
Format it before concatenation, such as Format$(amount, "#,##0.00"). Choose a format string that suits the value and the display you want; add currency text deliberately rather than assuming every workbook uses the same currency.
Does VBA MsgBox work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro, subject to the workbook’s macro security settings.
Related
- Excel Text to Columns: How to Make Results Vertical
- Excel Table Structured References: Rules, Examples & Tips
- Excel Take Data From a Table: Methods and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find Position of a Substring in a StringIn VBA, use InStr to find a substring’s position within a string: it returns the position of the…
- Excel VBA Array Delete: How to Remove Items SafelyIn Excel VBA, use Erase to clear an entire array; it does not remove just one element. To…
- Array Errors in Excel VBA: Causes and FixesAn “array error” in Excel VBA is not one specific error: it usually means code has requested an…
- Excel VBA SaveCopyAs: Save a Copy Without RenamingIn Excel VBA, use Workbook.SaveCopyAs to save a copy of an open workbook without changing the name or…
- VBA Input Box for Password: Masking and Safer OptionsVBA’s built-in InputBox cannot mask a password: characters appear as the user types. To show dots or asterisks…
- Find Unique Values in VBA: Methods and ExamplesTo find distinct values in VBA, read the source cells into a Scripting.Dictionary and add a value only…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.