Reputation: 559
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
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
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