CODEWITHSUNDEEP
excelvbadictionary
morebus
morebus

Reputation: 33

Alternative to Loop with very large dictionary in VBA

I have a dictionary with around half a million key/item pairs that i need to dump to a excel sheet. I can't use Application.Transpose(Dict.Keys) and Application.Transpose(Dict.Items) because there seems to be a limit of 65536 (2^16).

As an alternative I've considered looping over the dict and write the key and item to the cells. This takes several hours and seems very inefficient.

Any suggestions how I can do better?

Upvotes: 1

Views: 375

Answers (1)

Tim Williams
Tim Williams

Reputation: 166181

As Scott says:

Sub Tester()
    Dim i As Long, arr, t, k
    Dim dict As Object
    
    Set dict = CreateObject("scripting.dictionary")
    t = Timer
    For i = 1 To 500000
        dict.Add CStr("Key" & Format(i, "000000")), True
    Next i
    Debug.Print "Loaded dictionary", Timer - t
    
    ReDim arr(1 To dict.Count, 1 To 2)
    i = 0
    For Each k In dict
        i = i + 1
        arr(i, 1) = k
        arr(i, 2) = dict(k)
    Next k
    
    Debug.Print "Loaded array", Timer - t
    ActiveSheet.Range("A1").Resize(dict.Count, 2).Value = arr
    Debug.Print "Put array on sheet", Timer - t
End Sub

Output (times in seconds):

Loaded dictionary      13.9 
Loaded array           27.3 
Put array on sheet     28.8

Upvotes: 4

Related Questions