1

I have a scripting dictionary of around 450 elements. I need for debugging to watch in the spy window elements at index > 256, but Excel VBA limits the view to index 256. What I want to debug is the lack of some keys and to track what happens when they are supposed to be created.

Here is the declaration: Set col = CreateObject("Scripting.Dictionary")

I would expect to be able to write something like I can in Python: col[256:512]

As a workaround, I can print all the keys in a debug terminal like explained here: For Each c In col: Debug.Print c: Next

Bus there a way to do what I want in the watch/spy window?

lalebarde
  • 1,684
  • 1
  • 21
  • 36
  • 1
    You could do `keys = col.keys()` and check keys variant array in the Locals Window – milo5m Oct 17 '22 at 14:45
  • You probably fill your dictionary in a loop. If you know when the keyes in question should be created you could add a `debug.assert` statement to stop the execution. – Storax Oct 17 '22 at 14:52
  • 1
    Also you could add if you are using `col(key) = ...` in a loop, watch expressions `col(key)` and `key` and watch for values there – milo5m Oct 17 '22 at 14:54
  • 2
    ...just be aware that using a Watch alone on a dictionary key can be enough to create that key. See https://stackoverflow.com/a/41858402/478884 for example. – Tim Williams Oct 17 '22 at 15:40
  • 1
    Also, you could add `col.Items` as an expression as well (shows all the values) in Watches Window – milo5m Oct 17 '22 at 15:50
  • @milo5m I could find from your suggestions that watching for `col.keys` fulfil my needs. You can put that as the right answer, thanks. – lalebarde Oct 17 '22 at 16:28

2 Answers2

1

Add watch -> col.Items or col.keys as expressions.

milo5m
  • 619
  • 1
  • 3
  • 8
0

Print Some Dicitonary Keys and Items

Late Binding

  • When using late binding, you cannot use the 'index feature' (dict.Keys(n), dict.Items(n)). So you can only loop ascending and you could use the following method.
Sub PrintDictLate( _
        ByVal dict As Object, _
        ByVal FirstIndex As Long, _
        ByVal SecondIndex As Long)
    
    Dim StartIndex As Long
    Dim EndIndex As Long
    Dim dStep As Long
    
    ' Account for first greater than second.
    If FirstIndex <= SecondIndex Then
        StartIndex = FirstIndex
        EndIndex = SecondIndex
    Else
        StartIndex = SecondIndex
        EndIndex = FirstIndex
    End If
    
        ' Account for out of bounds.
    If StartIndex < 0 Then StartIndex = 0
    If EndIndex > dict.Count - 1 Then EndIndex = dict.Count - 1
    
    Dim Key As Variant
    Dim n As Long
    
    For Each Key In dict.Keys
        Select Case n
        Case StartIndex To EndIndex
            Debug.Print n, Key, dict(Key) ' if simple datatype
        End Select
        n = n + 1
    Next Key
    
End Sub

Early Binding

' Needs a reference to the Microsoft Scripting Runtime library.
Sub PrintDictEarly( _
        ByVal dict As Scripting.Dictionary, _
        ByVal StartIndex As Long, _
        ByVal EndIndex As Long)
    ' Note that the dictionary indexes are zero-based!
    ' Note that you can loop backwards!
    
    Dim dStep As Long

    If StartIndex <= EndIndex Then ' ascending
        dStep = 1
       ' Account for out of bounds.
        If StartIndex < 0 Then StartIndex = 0
        If EndIndex > dict.Count - 1 Then EndIndex = dict.Count - 1
    Else ' descending
        dStep = -1
        ' Account for out of bounds.
        If EndIndex < 0 Then EndIndex = 0
        If StartIndex > dict.Count - 1 Then StartIndex = dict.Count - 1
    End If

    Dim Key As Variant
    Dim n As Long

    For n = StartIndex To EndIndex Step dStep
        Debug.Print n, dict.Keys(n), dict.Items(n) ' if simple datatype
    Next n

End Sub

Test

' Needs a reference to the Microsoft Scripting Runtime library.
Sub LateVsEarly()
    
    Dim dict As Scripting.Dictionary: Set dict = New Scripting.Dictionary
    
    Dim i As Long
    For i = 1 To 20
        dict.Add i, i ^ 2
    Next i
    
    ' This works always.
    Debug.Print "Late Binding:"
    Debug.Print "Index", "Key", "Item"
    PrintDictLate dict, 5, 10
    
    ' This works only if a reference has been created.
    Debug.Print "Early Binding:"
    Debug.Print "Index", "Key", "Item"
    On Error Resume Next ' prevent error if no reference created
        PrintDictEarly dict, 10, 5
    On Error GoTo 0

End Sub

Results

Late Binding:
Index         Key           Item
 5             6             36 
 6             7             49 
 7             8             64 
 8             9             81 
 9             10            100 
 10            11            121 
Early Binding:
Index         Key           Item
 10            11            121 
 9             10            100 
 8             9             81 
 7             8             64 
 6             7             49 
 5             6             36
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • 1
    You can access elements by index when using late binding, however, you have to use `dict.Keys()(n)` or `dict.Items()(n)`. Note that this method of accessing an element is very slow and should usually be avoided, especially for large dictionaries. – GWD Oct 18 '22 at 08:11
  • Interesting. Looping 5k records and writing each key and item to a variable took 1s while doing it properly took 0,004s. With 50k records, it took 195s, while doing it properly took 0.6s. I thought it might be the same as writing `arr = dict.Keys: var = arr(n)...` in the loop (obviously terrible) but that took even 318s. So, I guess it may be acceptable for a few thousand records but I won't be adding it to my answer since you can easily write the keys or the items, which are arrays, to a variable and use the variable in the loop. Thanks for the input. – VBasic2008 Oct 18 '22 at 10:31