Failed to iterate 2D array converted from dict object-CodePudding

I was trying to filter some data with dictionary like using a map or object, and when thing is done, the item should be output as 2D array for easy importing back into spreadsheet.

But the following code returns error from failed reading arr(1,1) which it say something like index is out of range, why?

Sub DictToArr()
    Dim dict As New Scripting.Dictionary, arr As Variant
    dict.Add "item 1", Array("value 1", "value 2")
    dict.Add "item 2", Array("value 3", "value 4")
       
    arr = dict.Items
    Debug.Print arr(1, 1)
End Sub

Is there a way to directly assign this array format arr(i)(j) into a spreadsheet?, or directly convert dict.items into this array format arr(i, j) ?

CodePudding user response：

You can loop thru each dictionary item and apply row by row like this.

I'm sure there are other ways. You could convert it from an array of 1D arrays to a 2D array, but why add an intermediary step?

Sub DictToWorksheet()
    Dim dict As New Scripting.Dictionary
    dict.Add "item 1", Array("value 1", "value 2", "extra test")
    dict.Add "item 2", Array("value 3", "value 4")
       
    Dim Row As Long : Row = 1
    Dim Key As Variant
    For Each Key In dict.Keys
      ActiveSheet.Cells(Row, 1).Resize(1, UBound(dict(Key))   1) = dict(Key)
      Row = Row   1
    Next
End Sub

And to be clear, this is not a 2D array as stated in your title. This is a keyed collection of 1D arrays. (Maybe that's why the output wasn't what you expected.)

I added an additional "extra test" to illustrate that the upper bound for each item can be different, and 2D arrays will not work like that.

Speed depends on the size of the Dictionary. If you are talking about a couple of dozen items/arrays, then row by row will probably be just fine speed-wise. However, if there are thousands of items, then it would be better to convert it to a 2D array to transfer to the spreadsheet all at once.

For the basics on creating a 2D array:

Note the hard coded values for the array. You will want to adjust those accordingly.

I think you are mostly interested in the loop part of the code anyway.

Function DictTo2DArray() As String()
    Dim dict As New Scripting.Dictionary
    dict.Add "item 1", Array("value 1", "value 2") ' number of items here determines size of array
    dict.Add "item 2", Array("value 3", "value 4")
    dict.Add "item 3", Array("value 5", "value 6")
        
    Dim Result2D() As String
    ReDim Result2D(1 To dict.Count, 1 To UBound(dict("item 1"))   1) ' match the name of the first key
    
    Dim Row As Long : Row = 0
    Dim Col As Long
    Dim Key As Variant
    For Each Key In dict.Keys
      For Col = 0 To UBound(dict(Key))
        Result2D(Row   1, Col   1) = dict(Key)(Col) ' look familiar? Debug.Print arr(1)(1)
      Next
      Row = Row   1
    Next
    
    DictTo2DArray = Result2D

End Function

You can probably make this better with a more dynamically allocated array, but with only sample data it's hard to know what way is going to work best for you. This is just meant to show you how the loop works.