To print a VBA dictionary for debugging, loop through its keys and items and send each pair to the Immediate window with Debug.Print. To keep or physically print the results, write the pairs to worksheet cells first; Debug.Print displays text in the VBA editor and does not send anything to a printer.
What “printing” a dictionary means in VBA
A Scripting.Dictionary stores unique keys, with an item (value) associated with each key. It does not have a built-in command that renders the whole object as a readable table. To inspect its contents, your code must visit each key and decide where to send the corresponding item.
There are three common destinations:
- The Immediate window: a quick, temporary view while debugging a macro.
- A worksheet: a reusable two-column list that you can format, filter, save, or print.
- A printer or PDF: usually print a worksheet containing the dictionary, rather than trying to print the object directly.
The examples below assume Excel for Microsoft 365 on Windows and a dictionary created with the Windows Scripting.Dictionary object. The same looping idea applies when the dictionary is already populated by your own macro.
Print dictionary contents in the Immediate window
In Excel, open the Visual Basic Editor from Developer > Visual Basic. In the editor, choose View > Immediate Window. On Windows, Ctrl+G is a shortcut to show that window. Run the macro, and each Debug.Print line will appear there. The window is for development and inspection; it is not a worksheet and its contents are not saved as part of the workbook.
A compact loop prints one key-item pair per line:
Dim dict As Object
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 98
For Each key In dict.Keys
Debug.Print key, dict.Item(key)
Next key
In the Immediate window, the output will show the keys North and South alongside 125 and 98. The comma in a Debug.Print statement separates output into tab-like print zones, so the spacing can vary with the lengths of the values. For more predictable separation, join the values with a delimiter:
Dim dict As Object
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "North", 125
dict.Add "South", 98
For Each key In dict.Keys
Debug.Print CStr(key) & vbTab & CStr(dict.Item(key))
Next key
Here vbTab inserts a tab between the key and item. You can replace it with a visible separator such as " : " if tabs make the output hard to read. The key variable is declared as a Variant because dictionary keys can have different data types.
A complete example with a reusable print procedure
If another macro has already built the dictionary, put the display loop in a separate procedure and pass the object to it. This keeps the inspection step easy to run without changing how the dictionary is created:
Sub ShowDictionaryContents()
Dim dict As Object
Dim key As Variant
' Create a sample dictionary for this demonstration.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "Apples", 12
dict.Add "Pears", 7
dict.Add "Plums", 4
' Print a heading and one key-item pair per line.
Debug.Print "Key" & vbTab & "Item"
For Each key In dict.Keys
Debug.Print CStr(key) & vbTab & CStr(dict.Item(key))
Next key
End Sub
Before running any macro that changes a workbook, save a copy. VBA macros cannot be undone with Ctrl+Z. This sample only creates an in-memory dictionary and writes to the Immediate window, but the precaution matters when adapting code that also edits cells.
Write the dictionary to a worksheet
For output you want to retain, write keys in column A and items in column B. The procedure below uses a worksheet named Output; create that sheet first or change the name to one that exists in your workbook. It writes headers in row 1 and starts dictionary entries in row 2. It deliberately does not clear any old content, so choose an empty output area or clear it yourself after confirming that existing data can be removed.
Sub WriteDictionaryToSheet()
Dim dict As Object
Dim ws As Worksheet
Dim key As Variant
Dim rowNum As Long
' Create sample data; replace this with your populated dictionary.
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "Apples", 12
dict.Add "Pears", 7
dict.Add "Plums", 4
' Use an existing worksheet named Output.
Set ws = ThisWorkbook.Worksheets("Output")
ws.Cells(1, 1).Value = "Key"
ws.Cells(1, 2).Value = "Item"
rowNum = 2
For Each key In dict.Keys
ws.Cells(rowNum, 1).Value = key
ws.Cells(rowNum, 2).Value = dict.Item(key)
rowNum = rowNum + 1
Next key
ws.Columns("A:B").AutoFit
End Sub
After it runs, you can apply ordinary worksheet formatting, sort the rows, or use Excel’s print settings. If you run the procedure repeatedly, it will replace the two header cells and write new results, but it will not remove leftover rows from a previous, longer output. Clear only the previous output range when that behavior is appropriate.
Send the results to a printer or PDF
Once the dictionary is displayed on a worksheet, select that sheet and use Excel’s print controls. On Windows, open File > Print or press Ctrl+P. On Mac, use Command+P. Check the print preview, selected printer, page orientation, scaling, and print area before confirming. A long list may need landscape orientation or scaling to fit its columns.
You can also call the worksheet’s PrintOut method from VBA, but doing so can send pages to the active printer without the same opportunity to inspect the output first. For most cases, review the sheet in Excel and print from the preview. To create a PDF, choose an available PDF option in the print dialog or use Excel’s export options; the exact available printer and PDF choices depend on the operating system and installed software.
Common problems and their causes
- “ActiveX component can’t create object”:
CreateObject("Scripting.Dictionary")depends on the Windows scripting runtime. Check that the code is running in Windows Excel and that the object name is spelled correctly. The object is not a universal VBA feature on every platform. - The Immediate window is blank: confirm that the macro ran, that the loop has at least one entry, and that the Immediate window is open in the Visual Basic Editor. A dictionary with a
Countof zero has no pairs to display. - A key is missing or an add operation fails: dictionary keys must be unique according to the dictionary’s comparison rules. Check
dict.Exists(key)before adding a key that may already be present. If text keys should be compared without regard to capitalization, setCompareModebefore adding entries. - Values do not display cleanly: simple numbers and text are straightforward, but an item may itself be an object, an array, or an Excel error value. Converting every item with
CStrcan fail for some of these types. Inspect the item’s type and decide how to represent it before printing. - The output order seems unexpected: do not use a dictionary as a sorted list. If a particular order matters, collect the keys and sort them separately before writing or printing them.
Dictionary details that affect printed output
A dictionary’s Keys method returns the keys, while Items returns the corresponding values. Looping through dict.Keys and looking up dict.Item(key) is a clear way to keep each value paired with its key. For a single known key, you can use Debug.Print dict.Item("North"), but that only displays one value and will fail if the key is absent. Test first with dict.Exists("North") when the key may not be present.
Text comparison is another detail worth setting deliberately. The dictionary’s CompareMode controls how string keys are compared. For example, a text comparison can treat keys such as "North" and "north" as equivalent, while a binary comparison distinguishes their capitalization. Set the mode after creating the dictionary and before adding its first key; changing it after entries exist can raise an error. This setting affects which entries can coexist, not how the print loop formats them.
Windows, Mac, and alternatives
The examples use CreateObject("Scripting.Dictionary"), which relies on Windows COM automation. Excel for Mac does not provide the same Windows scripting-runtime support, so this creation method is not a portable assumption for Mac workbooks. Mac users should verify that their specific Excel environment supports the object rather than assuming the code will run because the VBA syntax is familiar. The VBA editor and worksheet-writing concepts may be familiar across platforms, but object availability differs.
If the dictionary object is unavailable, alternatives include a VBA Collection, a two-column worksheet range, or arrays and a custom lookup routine. A worksheet table is often the simplest choice when the main goal is to inspect, sort, filter, or print the data. A collection can hold keyed entries, but it does not provide all the same features as a dictionary, such as the same direct Exists check. Pick the structure based on how the macro uses the data, not just on how it will be displayed.
Frequently Asked Questions
How do I print all keys and values in a VBA dictionary?
Loop through dict.Keys and use Debug.Print key, dict.Item(key) for each key. Open the Immediate window in the Visual Basic Editor to see the output.
Does Debug.Print print to paper?
No. It writes to the Immediate window for debugging. To print on paper or save a readable report, write the key-item pairs to a worksheet and use Excel’s print or PDF options.
Can I print only one dictionary value?
Yes. Use Debug.Print dict.Item("North") for the value associated with that key. If the key might not exist, check dict.Exists("North") first.
Why does Scripting.Dictionary fail on Excel for Mac?
The common creation method uses the Windows scripting runtime, which is not a reliable cross-platform VBA dependency. Use a worksheet range, collection, or another Mac-compatible data structure if the object is unavailable.
Does a dictionary print its entries in sorted order?
No. The normal loop is not a request to sort. If the printed list must be alphabetical or numeric, sort the keys or the worksheet output explicitly before displaying or printing it.
Related
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Error? Common Causes and FixesAn Excel macro error usually means VBA is blocked, the code cannot find an expected workbook, worksheet, or…
- VBA Code to Delete a Row in ExcelTo delete a worksheet row with VBA, use Rows(5).Delete for row 5. In a macro, qualify the row…
- VBA Range Index: How Cells, Item, and Offsets WorkIn VBA, a range index identifies a cell by its row and column position, starting at 1. For…
- VBA Editor in Excel: Where to Find It and How to OpenIn Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor (VBE), where you…
- VBA Find the Last Used Cell When Filters Are OnTo find the last used cell in Excel VBA while a filter is active, search the worksheet with…
- VBA Function Calls: Syntax, Examples, and Common ErrorsA VBA function call runs a function and, in the usual case, uses the value it returns. In…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.