CODEWITHSUNDEEP
excelvbadictionary
ReverseEngineer
ReverseEngineer

Reputation: 559

Storing dictionaries in an array

I have dictionaries which wanted to store them in an array. There is an error when adding the dictionary in an array.

The expected output should be something similar to this.

 [{"Name": "A1", "ID": "B1", "Device": "C1"}, {"Name": "A2", "ID": "B2", "Device": "C2"}, {"Name": "A3", "ID": "B3", "Device": "C3"}, ...]

Error Code:

Wrong number of arguments or invalid property assignment

Dim RowDict as Scripting.Dictionary
Dim RowList() as Variant

TotalRow = ThisWorkbook.Sheets(1).Cells(ThisWorkbook.Sheets(1).Rows.Count, 1).End(xlUp).Row
ReDim RowList(0)

for i = 0 to TotalRow
    Set RowDict = New Scripting.Dictionary
    RowDict.Add Key:="Name", Item:=SourceWS.Cells(i, 3)
    RowDict.Add Key:="ID", Item:=SourceWS.Cells(i, 4)
    RowDict.Add Key:="Device", Item:=SourceWS.Cells(i, 1)
    
    ReDim Preserve RowList(UBound(RowList) + 1)
    RowList(UBound(RowList)) = RowDict <-- Error here
Next i

Upvotes: 0

Views: 112

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

ReDim Preserve not recommended (in terms of Memory handling) when you can calculate/deduce the array dimension(s)... Then, dictionary is an object and you must Set each array element.

Private Sub testDictionariesArray()
  Dim RowDict As Scripting.Dictionary, TotalRow As Long, i As Long
  Dim RowList As Variant, SourceWS As Worksheet

  Set SourceWS = ThisWorkbook.Sheets(1)
  TotalRow = SourceWS .Cells(Rows.count, 1).End(xlUp).row
  
  ReDim RowList(TotalRow)

    For i = 1 To TotalRow
        Set RowDict = New Scripting.Dictionary
        RowDict.aDD key:="Name", Item:=SourceWS.Cells(i, 3)
        RowDict.aDD key:="ID", Item:=SourceWS.Cells(i, 4)
        RowDict.aDD key:="Device", Item:=SourceWS.Cells(i, 1)
        Set RowList(i) = RowDict
    Next i
    Debug.Print RowList(1).count, RowList(1)("Name")
End Sub

Upvotes: 2

braX
braX

Reputation: 11755

Use Option Explicit and declare all of your variables.

Row 0 is not a valid row, so change the for loop.

Option Explicit

Sub test()

Dim RowDict As Scripting.Dictionary
Dim RowList() As Variant
Dim TotalRow As Long
Dim SourceWS As Worksheet
Dim i As Long

Set SourceWS = ThisWorkbook.Sheets(1)

TotalRow = SourceWS.Cells(SourceWS.Rows.Count, 1).End(xlUp).Row
ReDim RowList(0)

For i = 1 To TotalRow
    Set RowDict = New Scripting.Dictionary
    RowDict.Add Key:="Name", Item:=SourceWS.Cells(i, 3)
    RowDict.Add Key:="ID", Item:=SourceWS.Cells(i, 4)
    RowDict.Add Key:="Device", Item:=SourceWS.Cells(i, 1)
    
    ReDim Preserve RowList(UBound(RowList) + 1)
    Set RowList(UBound(RowList)) = RowDict ' added SET because dictionaries are objects
Next

End Sub

or if you are trying to get the items:

For i = 1 To TotalRow
    Set RowDict = New Scripting.Dictionary
    RowDict.Add Key:="Name", Item:=SourceWS.Cells(i, 3)
    RowDict.Add Key:="ID", Item:=SourceWS.Cells(i, 4)
    RowDict.Add Key:="Device", Item:=SourceWS.Cells(i, 1)
    
    ReDim Preserve RowList(UBound(RowList) + 1)
    Debug.Print RowDict.Items(0) ' shows the item being added in the debug window
    RowList(UBound(RowList)) = RowDict.Items(0)
Next i

Upvotes: 0

Related Questions