To show a VBA variable in a message box, join it to the message text with the ampersand operator: MsgBox "Hello, " & userName. In Excel VBA, the variable can hold text, a number, or other displayable data; you can also add a title, buttons, and a question or information icon.
The basic MsgBox pattern
MsgBox displays a dialog and, when the user closes it, returns a value indicating which button was clicked. Its common syntax is MsgBox(prompt, buttons, title). The prompt is required; the buttons and title are optional. For a simple message, the prompt can be a string, a variable, or a combination of both.
In the expression "Hello, " & userName, the ampersand joins two pieces of text. If userName contains Morgan, the dialog displays Hello, Morgan. Put the prompt in parentheses if you are assigning the return value to a variable, but parentheses are not required for a basic call used as a statement.
This small Excel macro shows the full pattern. In the Windows desktop app, open the Visual Basic Editor with Alt+F11, insert a standard module, and place the code there. On Mac, use Option+F11 (on some keyboards, Fn+Option+F11) or open the editor from Tools > Macro > Visual Basic Editor.
Sub ShowGreeting()
Dim userName As String
' Set the text that will appear in the message.
userName = "Morgan"
MsgBox "Hello, " & userName, vbInformation, "Greeting"
End Sub
Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z. To run this example, click inside the procedure in the editor and press F5, or return to Excel and run it from the Macro dialog. Store a workbook containing VBA in the Excel Macro-Enabled Workbook format (.xlsm).
Putting a cell value into the message
A common reason to use a variable is to report a value read from a worksheet. For instance, the macro below reads the customer name from cell B2 and includes it in the prompt. The sheet reference is deliberately simple; in a larger workbook, qualify Range with a worksheet so the result does not depend on which sheet is active.
Sub ShowCustomer()
Dim customerName As String
' Read a name from the active sheet's B2 cell.
customerName = Range("B2").Value
MsgBox "Customer: " & customerName, vbInformation, "Order lookup"
End Sub
For example, if B2 contains Riley Chen, the message reads Customer: Riley Chen. If the value should always come from a particular sheet, use a worksheet variable and qualify the range, such as Worksheets("Orders").Range("B2").Value. The worksheet name must match the tab name. A reference such as Range("B2") without a worksheet qualifier refers to the active sheet, which may not be the sheet the macro author expected.
The ampersand is generally the clearest operator for joining text in VBA. Avoid using + for this job: VBA may treat plus as arithmetic, and mixed text and numeric values can lead to confusing conversions or errors. If the cell contains an Excel error value rather than ordinary text, assigning it directly to a String may fail; check the cell or handle the error before building the prompt.
Combining multiple values and formatting them
You can join several variables and literal text in one prompt. Add spaces, punctuation, or labels explicitly; VBA does not insert separators automatically. A number can often be joined directly, but converting or formatting it first makes the intended display clearer. For example, a calculated amount might otherwise show more decimal places than a reader needs.
Sub ShowOrderTotal()
Dim orderNumber As Long
Dim orderTotal As Double
Dim messageText As String
' Illustrative values; replace them with values from your workbook.
orderNumber = 1042
orderTotal = 87.5
messageText = "Order " & CStr(orderNumber) & _
" total: $" & Format$(orderTotal, "0.00")
MsgBox messageText, vbInformation, "Order summary"
End Sub
The example produces Order 1042 total: $87.50. CStr explicitly turns the order number into text, while Format$ returns formatted text for the amount. The dollar sign is literal in this example, so change it if the workbook uses another currency. Formatting masks can also control decimal places and thousands separators, but the exact display can depend on regional settings. For dates, use a deliberate date format rather than relying on the computer’s default conversion.
Building the prompt in a separate variable, as messageText does here, is useful when the message is long or assembled from several fields. It makes the final text easier to inspect in the editor and to modify later. Keep variable names meaningful, and declare their types: String for text, Long for whole-number identifiers or counts, and a suitable numeric type such as Double for values with decimals.
Adding line breaks to a prompt
For a message with several details, use vbNewLine to start a new line. This makes labels and values easier to scan than one long sentence. You can combine the line break with variables just as you combine ordinary text. A short message is still preferable: a MsgBox is a small dialog, not a report or a place to display a large table.
Sub ShowContactDetails()
Dim contactName As String
Dim cityName As String
' Example values for a compact, multi-line prompt.
contactName = "Avery Patel"
cityName = "Denver"
MsgBox "Contact: " & contactName & vbNewLine & _
"City: " & cityName, vbInformation, "Contact details"
End Sub
Here the prompt appears on two lines, with the title displayed separately in the dialog’s title bar. In older VBA examples you may also see vbCrLf, which inserts a carriage return and line feed. vbNewLine is a readable built-in constant for a new line and is a convenient choice for a prompt. Use the line continuation character, a space followed by an underscore, only to split a long statement across code lines; it does not add a line break to the message itself.
Choosing buttons, icons, and a title
The optional buttons argument controls which buttons and icon appear. VBA provides built-in constants, so use them rather than memorizing numeric values. For example, vbYesNo displays Yes and No buttons, while vbQuestion requests a question icon. Combine applicable constants with the plus sign, as in vbYesNo + vbQuestion. The optional title argument supplies a useful label in the title bar.
Choose buttons that match what the macro will do next. A confirmation question should not be presented as an information-only message, and a choice should be captured if it affects later steps. This example stops unless the user chooses Yes:
Sub ConfirmAction()
Dim answer As VbMsgBoxResult
' Ask before continuing with a consequential action.
answer = MsgBox("Continue with the update?", _
vbYesNo + vbQuestion, "Confirm update")
If answer = vbYes Then
MsgBox "The update can continue.", vbInformation, "Status"
Else
MsgBox "No changes were requested.", vbInformation, "Status"
End If
End Sub
VbMsgBoxResult is the result type used for the returned button choice. In a Yes/No dialog, compare the result with vbYes or vbNo; do not assume that a button’s position means a particular result. Other button sets can return constants such as vbOK, vbCancel, vbRetry, or vbIgnore. You can also choose a default button with constants such as vbDefaultButton2. Make the default choice carefully, especially when the action could alter workbook data.
Variables, conversion, and common errors
Declare variables explicitly, ideally with Option Explicit at the top of the module. This setting makes VBA require declarations and helps catch misspelled names instead of silently treating them as new variables. A String is usually appropriate for a name or a prepared prompt. If a value is numeric or a date, decide how it should look before joining it to text. CStr performs a conversion to a string, while Format$ lets you specify a display format.
One frequent problem is a blank result. The variable may be empty, the referenced cell may be blank, or a different worksheet may be active. Check the source value and qualify worksheet references. Another is a compile error caused by a misspelled variable, missing quotation mark, or missing ampersand. Use Debug > Compile VBAProject in the Visual Basic Editor to find compile-time problems; runtime problems can still depend on the workbook’s data.
- Text appears without a space: include the space inside the literal, such as
"Hello, ", before joining the variable. - A number has an awkward display: use
Format$with a suitable mask rather than expecting a particular default representation. - The wrong cell value appears: qualify the range with the intended worksheet and confirm the address.
- The prompt contains an unexpected value: inspect the variable immediately before the MsgBox call, and check whether the source cell is blank or contains an error.
Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the Excel desktop application for these examples. Excel for Mac supports VBA, though macro settings, keyboard shortcuts, and some workbook features can differ from Windows. Enable macros only for workbooks you trust, and do not treat a confirmation MsgBox as a substitute for checking a macro’s effects.
When MsgBox is the right tool
MsgBox is well suited to brief status messages, simple confirmations, and small prompts where a user must choose among a few buttons. It pauses the macro while the dialog is open, so it can make a procedure wait for a decision. That is useful for a short confirmation, but disruptive if a macro needs to process many rows or show frequent messages. Avoid placing a MsgBox inside a loop that runs repeatedly unless repeated interruptions are intentional.
For more complex input, use an Excel worksheet, an InputBox, or a VBA UserForm. An InputBox is designed to collect a small text response, while a UserForm can provide labels, text boxes, lists, validation, and multiple controls. A worksheet is often better when users need to review or edit several values. These alternatives require more setup, but they give users more room and control than a small message dialog. Regardless of the interface, validate important input before using it to change data.
MsgBox does not make a workbook safer or provide a reliable audit trail by itself. A user can click a button without understanding a lengthy prompt, and a macro that changes cells cannot be reversed with Ctrl+Z. Keep confirmations specific, limit the action to what the prompt describes, and make a backup copy before trying unfamiliar code. Those habits matter more than adding extra buttons to the dialog.
Frequently Asked Questions
How do I display a variable in a VBA MsgBox?
Join it to the prompt using &, for example MsgBox "Name: " & userName. Put literal text in quotation marks and leave the variable name outside them.
Can a MsgBox show a number variable?
Yes. VBA can usually convert a number when it is joined to a string with &. For predictable display, use CStr to convert it or Format$ to control decimal places and other formatting.
How do I put a variable on a new line in a MsgBox?
Join the parts of the prompt with vbNewLine, such as "Name: " & userName & vbNewLine & "City: " & cityName. The underscore used in some code examples only continues a statement onto another code line.
How can I tell which MsgBox button the user clicked?
Assign the result to a variable, then compare it with a result constant such as vbYes or vbNo. The constants you check must match the buttons you displayed.
Why does my VBA MsgBox show a blank variable?
The variable may not have been assigned, its source cell may be empty, or the macro may be reading from the wrong active sheet. Check the assignment and qualify worksheet ranges explicitly where needed.
Related
- Data Validation Interview Questions and Strong Answers
- Data Validation Greyed Out in Excel? Causes and Fixes
- Excel Data Validation Guidelines for Reliable Spreadsheets
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Change Pivot Table Source: Code and TipsTo change a PivotTable’s source with VBA, create a PivotCache for the new worksheet range or Excel table,…
- Excel VBA Arrays: How They Work and When to Use ThemAn Excel VBA array is a variable that holds multiple related values, such as a list of names…
- VBA Find a Value in a Column: Methods and ExamplesTo find a value in an Excel column with VBA, use the Range.Find method on the column and…
- Refresh Worksheet VBA: Methods, Examples, and FixesTo recalculate formulas on one worksheet with VBA, call that worksheet’s Calculate method. To refresh imported data, use…
- VBA Resize UserForm: Set Size and Enable ResizingTo resize an Excel VBA UserForm, set its Width and Height properties in the form’s Properties window or…
- Excel Macro to Import CSV Files AutomaticallyTo import a CSV file with an Excel macro, use VBA to ask you to select a file…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.