How to add dictionary values to an Array in VBA instead of reference

Question

I am sending data from Excel to the dashing.io dashboard via JSON. The data which I am sending consist of several touples of data like

[{"Group":"Interface","progress";"10"},{"Group":"HMI","progress";"20"}]

Right now I am using several dictionary objects progress1, progress2 to hold the data like:

DIM progress1 as new Dictionary
DIM progress2 as new Dictionary
progress1.add "Group", "Interface"
progress1.add "Progress", 10
progress2.add "Group", "HMI"
progress2.add "Progress", 20

And finally for creating the JSON object, the two progress dictionaries are added to one object:

Dim body as new Dictionary
body.add Array(progress1, progress2)

As there are more than just two progress items, I wanted to create a for-loop to add data to a dictionary and then add that dictionary item to an array like

for i=1 to 10
  progress.add "Group", Range(i,1)
  progress.add "progress", Range (i,2)
next i
overall_progress.add (progress)

It seems that I am adding the object as reference, instead of the values of the object. If the loop runs 10 times, I end up having ten times the same entry.

Any idea how to add the values of a dictionary to an array, instead of the reference?

Tim Williams · Accepted Answer · 2018-08-29 06:36:55Z

1

Creating objects in a loop is easier to manage if you use a "factory" function.

E.g:

Sub Tester()

    Dim body As New Collection, i As Long

    For i = 1 To 10
        body.Add Progress(Cells(i, 1), Cells(i, 2))
    Next i

End Sub

Function Progress(grp, prog) As Object
    Dim rv As New scripting.dictionary
    rv.Add "Group", grp
    rv.Add "Progress", prog
    Set Progress = rv
End Function

answered Aug 29, 2018 at 6:36

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bernhard Over a year ago

Wow, great! I had to use "Dim rv as New Dictionary" but apart from that, it now works great. Thanks a lot!

Collectives™ on Stack Overflow

How to add dictionary values to an Array in VBA instead of reference

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related