Excel VBA Scripting Dictionary Output Issue

[mekueny]

In VBA I have created a scripting dictionary called finDict that contains ~72k keys and items. I'm trying to write the list of keys to a worksheet.

The code below correctly prints all of the keys in finDict, but it is extremely slow:

For Z = 1 To finDict.Count
ActiveWorkbook.Sheets("Test").Range("A" & Z) = finDict.Keys(Z - 1)
Next Z

I tried replacing that code with the code below, but for some reason this code replaces most of the printed keys with "#N/A":

ActiveWorkbook.Sheets("Test").Cells(1, 1).Resize(finDict.Count) = Application.Transpose(finDict.Keys)

Can anyone spot what the issue is??

 

[Leith Ross]

Hello mekueny,

Transpose has a cutoff off 8192 distinct range areas. It is much faster to load the values into a 2-D array then output the array to the range. Here is an example...

Sub Macro2()


    Dim Dict    As Object
    Dim k       As Long
    Dim Key     As Variant
    Dim Keys    As Variant
    
        Set finDict = CreateObject("Scripting.Dictionary")
        
        ' // Code to load the dictionary goes here
        
        ReDim Keys(1 To Dict.Count, 1 To 1)
        
        For Each Key In Dict.Keys
            k = k + 1
            Keys(k, 1) = Key
        Next Key
        
        ActiveWorkbook.Sheets("Test").Range("A1").Resize(finDict.Count, 1).Value = Keys
        
End Sub

 

[Akuini]

Transpose is limited to 65,536 elements, see this discussion:
https://www.mrexcel.com/forum/excel-questions/555073-limitation-range-size-transpose-function.html

A work around:

[FONT=lucida console][COLOR=Royalblue]Dim[/COLOR] x, va
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]

[COLOR=Royalblue]ReDim[/COLOR] va([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] finDict.count, [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]1[/COLOR])
    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] x [COLOR=Royalblue]In[/COLOR] finDict.Keys
        i = i + [COLOR=crimson]1[/COLOR]
        va(i, [COLOR=crimson]1[/COLOR]) = x
    [COLOR=Royalblue]Next[/COLOR]
ActiveWorkbook.Sheets([COLOR=brown]"Test"[/COLOR]).Cells([COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]).Resize(finDict.count) = va[/FONT]

Edited: Ah, Leith Ross already got the answer.

 

[mekueny]

Worked like a charm, thanks!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.