Insert multiple dictionaries in an array by using a loop

Question

I have a problem by filling an array in excel vba by using a loop. Actually I would like to fill the dictionary myitem the following way: produkt1 and produkt2 may be both dictionaries.

myitem("productionOrderItems")=Array(produkt1, produkt2).

Since I don't now how many times I have a produkt I am using a loop to create the dictionary produkt. Everytime the dictionary produkt is created and filled I would like to assign it to an Array just like I described above. The following code shows the loop to create the dictionary. But in that case obviously only the last dictionary (i=counter) is created. counter is the amount of products I would like to assign to the Array.

myitem As New Dictionary

For i = 1 To counter
    Dim produkt As New Dictionary
    zeile = 2 + i
    produkt("id") = Tabelle6.Cells(zeile, 1).Value
    Tabelle6.Cells(zeile, 1).Value = ""
    produkt("actualWithdrawalQuantity") = Tabelle6.Cells(zeile, 2).Value
    Tabelle6.Cells(zeile, 2).Value = ""
    produkt("articleId") = Tabelle6.Cells(zeile, 3).Value
    Tabelle6.Cells(zeile, 3).Value = ""
    produkt("articleNumber") = Tabelle6.Cells(zeile, 4).Value
    Tabelle6.Cells(zeile, 4).Value = ""
    produkt("createdDate") = Tabelle6.Cells(zeile, 5).Value
    Tabelle6.Cells(zeile, 5).Value = ""
    produkt("positionNumber") = Tabelle6.Cells(zeile, 6).Value
    Tabelle6.Cells(zeile, 6).Value = ""
    produkt("quantity") = Tabelle6.Cells(zeile, 7).Value
    Tabelle6.Cells(zeile, 7).Value = ""
    produkt("targetWithdrawalDate") = Tabelle6.Cells(zeile, 8).Value
    Tabelle6.Cells(zeile, 8).Value = ""
    produkt("targetWithdrawalQuantity") = Tabelle6.Cells(zeile, 9).Value
    Tabelle6.Cells(zeile, 9).Value = ""
    myitem("productionOrderItems") = Array(produkt)
Next

Maybe someone has an idea how to solve the problem. Thanks in advance!

score 0 · Accepted Answer · answered Mar 15 '18 at 12:45

0

The issue is that

myitem("productionOrderItems") = Array(produkt)

always overwrites myitem("productionOrderItems") with a new array and doesn't preserve the old one. So in the end there is only the last "new" array in it.

What you need to do is to extend the existing array by one entry each time:

Dim tmpArr As Variant
tmpArr = myitem("productionOrderItems") 'read existing array into temp array
If IsEmpty(tmpArr) Then
    tmpArr = Array(produkt) 'first time we need to generate a array
Else 'for all other times we need to append to the existing array
    ReDim Preserve tmpArr(UBound(tmpArr) + 1) 'resize existing array by one
    Set tmpArr(UBound(tmpArr)) = produkt 'add the product to the newly added array entry
End If
myitem("productionOrderItems") = tmpArr 'write temp array back to dictionary

answered Mar 15 '18 at 12:45

Pᴇʜ

56,719
10
49
73

I am using the array to set up a PUT request. When finishing the request the error "productionorderItems: duplicate id" occurs. I am wondering if the Array does contain duplicated ids. But for that I have to show a MsgBox that shows the produkt array. Can you tell me how? Or do you have an idea why the error with the duplicated id occurs? – Sophia Mar 15 '18 at 13:47
you need a loop to output all items of an array or dictionary: [How to monitor the values in a Dictionary in the Excel VBA watch window?](https://stackoverflow.com/questions/9803708/how-to-monitor-the-values-in-a-dictionary-in-the-excel-vba-watch-window). If my answer is the solution to your original question please consider marking it as solution. – Pᴇʜ Mar 15 '18 at 13:50

score 0 · Answer 2 · answered Mar 15 '18 at 16:15

I figured out what the problem was: The loop worked but the "produkt" dictionary haven't been overwritten so that two time the same "produkt" have been inserted. I circumvented that issue by setting produkt to nothing in the beginning of the loop. Thanks for your help!

Insert multiple dictionaries in an array by using a loop

2 Answers2