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

Question

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!

Solution 1

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

Solution 2

I use a recursive function which can be used to display all simple type variables and the contents of all nested dictionaries in the watch window. This produces output in the form:

Fred:rabbit; Tiddles:cat; Fluffy:cat; Food:[1:lettuce; 2:biscuits; ]; 

where keys and values are separated by ":", items are separated by "; " and nested dictionaries are shown in square brackets.

Public Function DictionaryContents(ByVal dcDictionary, Optional ByVal boolShowKeyIndex As Boolean = False)

  Dim Keys
  Keys = dcDictionary.Keys

  Dim i As Long
  Dim stIndex As String

  Dim stOutput As String
  stOutput = vbNullString

  For i = 0 To dcDictionary.Count - 1

    If boolShowKeyIndex Then
      stIndex = "(" & i & ")"
    End If

    stOutput = stOutput & Keys(i) & stIndex & ":"

    If IsObject(dcDictionary(Keys(i))) Then
      stOutput = stOutput & "[" & DictionaryContents(dcDictionary(Keys(i)), boolShowKeyIndex) & "]"
    Else
      stOutput = stOutput & dcDictionary(Keys(i))
    End If

    stOutput = stOutput & "; "

  Next i

  DictionaryContents = stOutput

End Function