How to watch a "Scripting.Dictionary" of more than 256 elements in excel vba when adding a-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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