How to monitor the values in a Dictionary in the Excel VBA watch window?

excel ms-access vba ms-office
tyrex picture tyrex · Mar 21, 2012 · Viewed 14.2k times · Source

I am using dictionaries in Excel VBA via dict As New Dictionary (and adding a reference to the scripting runtime). When I try to monitor those during debugging, I can only see the keys which lie in the dictionary, but not the respective value of each key.

Is there any way to see the value as well? It would make debugging much more easy for me.

EDIT: Based on your answers, there is no easy solution, but I can do the following.

Use a global variable Dim d_obj As Object and monitor it constantly and whenever I need to look up a value of a dictionary, I type into the immediate window Set d_obj(key) = ... and I will be able to see the value in the monitor-window.

What I may do in addition is write a function which takes in a dictionary and returns the values as a list and use this function similarly at the direct window. Thx to all!

Answer

Nick picture Nick · Mar 26, 2012

I usually type dict.items into the immediate window, select it and go Shift+F9 to insert it into the watch window.

Alternatively, here's a one-liner for the immediate window, to list all items:

for each i in dic.Items: debug.Print i: next

Related questions

Get length of array?

I'm trying to get the length of an array, yet I keep getting this error: Object required Am I doing something wrong? Dim columns As Variant columns = Array( _ "A", "ID", _ "D", "Name") Debug.Print columns.Length ' Error: Object required

Read More
Error: "Could Not Find Installable ISAM"

I've written some VBA code in an Excel workbook to retrieve data from an Access database in the same directory on a desktop. It works fine on my machine and several other machines running Windows XP, but when we tested …

Read More
Export tables to an excel spreadsheet in same directory

I have two tables in my access database that I want to be able to export to excel. I can do it by opening the table and then doing File->Export... and then choosing the format and typing in …

Read More