VBA cannot display an array directly in a MsgBox: first convert its elements to a text string. For a one-dimensional array, use Join; for a two-dimensional array, such as values read from a worksheet range, loop through its rows and columns and build a formatted string.
Why an array needs to become text
A VBA array stores multiple values under one variable name. A MsgBox, by contrast, displays a prompt string. Passing an array variable directly to MsgBox does not automatically produce a useful list of its contents; depending on how the array is declared and used, VBA may report a type mismatch or fail to display what you expect. The reliable approach is to decide how the values should look, assemble that representation as text, and pass the resulting string to the dialog.
This is useful for quick checks while writing or troubleshooting a macro. It can also help a macro user confirm a short list of items. A message box is not a good substitute for a worksheet, report, or form when there are many values: the dialog is intended for a brief prompt, and its text can be difficult to scan when it is long.
Display a one-dimensional array with Join
For a one-dimensional array, Join is usually the simplest choice. It combines the array elements into one string, placing a separator between each pair. A line break makes the result easy to read in a MsgBox:
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z.
Sub ShowColorArray()
Dim colors As Variant
Dim message As String
' Array creates a zero-based, one-dimensional array.
colors = Array("Red", "Green", "Blue")
message = Join(colors, vbCrLf)
MsgBox message, vbInformation, "Colors"
End Sub
The dialog shows Red, Green, and Blue on separate lines. Replace vbCrLf with ", " if you prefer a comma-and-space separator. Other useful separators include vbTab for tab spacing and vbNewLine for a platform-appropriate line break.
Join is designed for a one-dimensional array. It is particularly convenient for arrays made with the Array function or for a one-dimensional string array. If the source values are not strings, VBA may convert them as it builds the result, but explicit conversion and handling are preferable when the values can include errors, Nulls, or other special types.
Display a two-dimensional array from a range
When you assign a multi-cell Excel range to a Variant, Excel returns a two-dimensional array: one dimension represents worksheet rows and the other represents columns. Join does not flatten that structure for you. Loop over both dimensions and add separators between columns and rows instead.
The following macro reads A1:C4 on Sheet1 and places each row on a separate line, with tabs between columns. It uses the array’s actual lower and upper bounds rather than assuming that every array starts at zero or one.
Save a copy of the workbook before running this macro; its changes cannot be reversed with Ctrl+Z.
Sub ShowRangeArray()
Dim data As Variant
Dim message As String
Dim r As Long
Dim c As Long
Dim firstRow As Long
Dim lastRow As Long
Dim firstColumn As Long
Dim lastColumn As Long
' A multi-cell range is returned as a two-dimensional array.
data = ThisWorkbook.Worksheets("Sheet1").Range("A1:C4").Value
firstRow = LBound(data, 1)
lastRow = UBound(data, 1)
firstColumn = LBound(data, 2)
lastColumn = UBound(data, 2)
For r = firstRow To lastRow
For c = firstColumn To lastColumn
If c > firstColumn Then message = message & vbTab
message = message & ValueForMessage(data(r, c))
Next c
If r < lastRow Then message = message & vbCrLf
Next r
MsgBox message, vbInformation, "Range values"
End Sub
Private Function ValueForMessage(ByVal value As Variant) As String
' Avoid conversion errors for worksheet errors, Null, and Empty values.
If IsError(value) Then
ValueForMessage = "[Error]"
ElseIf IsNull(value) Then
ValueForMessage = "[Null]"
ElseIf IsEmpty(value) Then
ValueForMessage = ""
Else
ValueForMessage = CStr(value)
End If
End Function
The code uses the cell values returned by Range.Value. If you need the displayed text exactly as it appears in the cells, including number formats such as currency or dates, that is a different requirement: Value returns underlying values, not each cell’s formatted display string. You can read cells individually using their Text property, but that property reflects displayed text and can be affected by column width. Choose based on whether the message should show stored values or worksheet presentation.
Choose separators and readable formatting
Separators determine whether a list is understandable. A comma is compact, a line break is easier to scan for a short vertical list, and a tab can make a small table easier to distinguish. For a two-dimensional array, use one separator between columns and another between rows. For example, tabs between fields and vbCrLf between records preserve the row-and-column structure.
- Comma and space: suitable for a short one-dimensional list, such as
Red, Green, Blue. - Line break: useful when each item should stand out on its own line.
- Tab: handy for separating a few columns, though a MsgBox is not a full table viewer.
- Labels: add context such as
message = "Selected items:" & vbCrLf & Join(items, vbCrLf)when the dialog needs to explain what the list represents.
A separator is inserted between elements, not necessarily after the final element. That avoids an unwanted trailing comma or blank line. If values themselves may contain commas or line breaks, choose a separator that will not make the output ambiguous, or add labels and delimiters that make the structure clear.
Handle empty arrays and unusual cell values
Some arrays cannot be joined or indexed until they have been initialized. A dynamic array declared with a statement such as Dim items() As String has no usable bounds until it is allocated. Calling LBound, UBound, or Join on an uninitialized dynamic array can raise a runtime error. If an array may be empty, establish how your code represents that state and check it before trying to format it. VBA does not provide one universal, simple test that works for every possible array declaration and state, so the check should match how the array is created.
Worksheet arrays can also contain blank cells, formula errors such as #N/A, or values that cannot be converted to ordinary text. The helper function in the example turns errors into the visible label [Error], Null into [Null], and Empty into an empty string. Change those labels to suit the audience. For example, a diagnostic message might show the error value explicitly, while a user-facing confirmation may omit empty cells.
Formatting can change the apparent content, too. A date is stored as a numeric value in Excel, while currency and percentages use number formats to control their display. Converting a raw range value to a string may therefore not look like the cell. Decide whether you want the stored value, the cell’s displayed text, or a custom format, and make that choice explicit in the macro.
Keep MsgBox output short and useful
A MsgBox is best for a short status, warning, or small list. It is not designed to browse a large array. Long text can be awkward to read, may exceed practical dialog limits, and can be truncated or displayed inconveniently. The exact behavior depends on the content and environment, so do not rely on a message box to preserve a large data dump.
Before displaying a formatted array, consider limiting the output to a manageable number of items. You can show a count and a few examples, then direct the user to a worksheet for the complete results. For instance, a macro can write all values to a temporary worksheet and show a MsgBox that says how many were found and where they were written. This gives the user a useful summary without turning the dialog into a scroll-free report.
Also consider what happens if the array contains personal or confidential information. A message box is visible on screen and may be captured in a screenshot or seen by someone nearby. For sensitive data, show only the minimum information needed, or avoid displaying it in a dialog.
Common mistakes and how to fix them
- Passing the array directly: convert it to a string first. Use
Joinfor one dimension or a loop for multiple dimensions. - Using Join on a range array: a multi-cell range is two-dimensional. Loop through both dimensions and add row and column separators.
- Assuming a fixed lower bound: use
LBoundandUBoundfor the relevant dimension. Arrays can have different bounds depending on how they were created. - Reading a single cell as though it were a 2-D array: a one-cell range can return a scalar Variant rather than a two-dimensional array. Code that handles ranges of variable size should check for this case or ensure the source range contains multiple cells.
- Ignoring worksheet errors: converting an Error value with
CStrcan fail. Test withIsErrorbefore conversion. - Expecting cell formatting automatically:
Range.Valuesupplies values, not the formatted appearance. Choose a display-oriented method if the exact visible text matters.
When to use a worksheet or UserForm instead
Use a MsgBox when the output is brief and the user needs to acknowledge it or choose an action. Use a worksheet when the array contains many rows or columns, needs sorting, filtering, copying, printing, or comparison with other data. A worksheet is also easier to inspect because users can navigate through a large result instead of reading it in a small dialog.
A VBA UserForm is another option when you need a custom interface, such as a list box, a scrollable display, or buttons that perform specific actions. A form takes more setup than a MsgBox, but it offers better control over layout and interaction. For a quick debugging check, the Immediate window in the Visual Basic Editor may be more convenient than repeatedly opening dialogs; it is not, however, a polished interface for ordinary workbook users.
Frequently Asked Questions
Can I use Join on any VBA array?
Use Join with a one-dimensional array. It does not automatically combine the rows and columns of a two-dimensional array. For a two-dimensional array, loop through each dimension and build the output string.
How do I show each array item on a new line?
For a one-dimensional array, use Join(items, vbCrLf). For a two-dimensional array, add vbCrLf after each row and a different separator, such as vbTab, between columns.
Why does my range array give a type mismatch?
A common cause is passing the array directly to MsgBox or using Join on a two-dimensional range array. Convert the array to a string first. Also check whether the range contains error values or whether the code assumes an array when the range has only one cell.
Can a MsgBox show a whole worksheet range?
It can show a short range after you convert its values to text, but it is usually a poor way to present a large range. Put extensive results on a worksheet or in a form, and use the MsgBox for a concise summary.
How do I show the same formatting as the cells?
A range’s Value gives the underlying value, not necessarily the displayed formatting. Read each cell’s Text property when you need its displayed text, keeping in mind that the result can depend on column width. For predictable output, apply your own formatting while building the string.
Related
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Delete a Worksheet Without a Prompt SafelyTo delete an Excel worksheet without the confirmation prompt, set Application.DisplayAlerts to False immediately before calling the worksheet’s…
- VBA Coding Dictionary: How the Dictionary Object WorksA VBA Dictionary is an object that stores pairs of keys and values, letting an Excel macro look…
- VBA Array Set: Assigning and Initializing Arrays in ExcelIn VBA, you do not use Set to assign an array: use ordinary assignment for array values, and…
- Excel VBA Refresh All: How to Update Workbook DataTo run Excel’s Refresh All command from VBA, use ThisWorkbook.RefreshAll in a macro. It asks Excel to refresh…
- Access VBA Match Function: What It Does and How to Use ItMicrosoft Access does not provide Excel’s worksheet MATCH function as a native VBA function. To find a value…
- VBA Macro to Copy and Paste a Row Below the Last RowTo copy the last data row into the row immediately below it, find the last occupied cell in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.