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!

Longish

Do you mean you want to see the 'value' as well as the key? – Caputto

>>>"I can only see the keys which lie in the dictionary, but not the respective value of each key." <<< How are you debugging to see the values? This works in immediate window. ?dict.Item(1) – Luminary

#738912 – Entanglement

As @SiddharthRout says, using the index of the Dictionary is generally the way to go. However, as the value in a Dictionary can be anything (objects, UDTs, arrays, etc.) there is no full-proof way to get the values of a dictionary like what I think you want. – Caputto

I don't think you can, the best way is what @SiddharthRout proposes: type dict(yourKey) in the watch window. Note that there is a bug in some versions of VBA: if you type dict(aKeyThatDoesNotExist) in the watch window, that key might get created with a null value which causes weird and difficult to reproduce bugs... – Milklivered

An alternative would be to create a custom class that has the same methods as a Dictionary and contains a dictionary and an array that updates every time the Dictionary is changed... You would only need to change the declaration from Dim dict as Dictionary to Dim dict as MyDictionary. The rest would be unchanged. But that's a lot of work just for debugging... – Milklivered

Good question. One advantage of using Collections is that you can more easily view values inside via the watch window. I've wondered about writing a DLL function to return a container (array or collection) that can be called from the watch window to see values in a dictionary, maybe a utility for this already exists? – Arsenault

@creamyegg: yes, i want to see the 'key' and the related 'value', which of course sometimes will be a complicated object in which case the ?dict.Item(1) method will not work. Regarding defining a separate class, I have thought about that, but in my case dismissed it for not worth it. @lori_m: I would certainly use such a utility if it existed. – Longish

@all: thank you for your answers!! You confirmed my fear that there is no easy solution to this. The reason for this may be that the dictionary-object is not an excel-native object but is taken from the com-buffet-table... – Longish

generally if I am operating on a list of complex objects inside a dictionary I will detach it from dictionary before making changes to it... assigning it to an object as you have outlined above. This is a bit more unwieldy but if you strong type the object you can get the benefit of leaning on the IDE for method/property type aheads. For me this ends up saving time since I don't have to go check the class for method spelling etc. – Loiret

@Pynner: Haven't thought of that before, but it sounds like a good idea. – Longish

Answer 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

Eurythmics

Answer 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

Leasehold