VBA - Extracting Array Data from a Dictionary

Question

I have populated a dictionary with multiple arrays linked to each unique key. E.g:

Dim dict As New Scripting.Dictionary
Dim x(5) As Variant
Dim prNumbers() as String
Dim prArrCount as Integer

prArrCount = 0

For i = 2 To lastRow
    'Populate relevant values (prNr, description etc. by reading them in.

    'Save the values in an array
    x(0) = prNr
    x(1) = description
    x(2) = priority
    x(3) = deliveryDate
    x(4) = delivery
    x(5) = endUser

    'Add to the dictionary if the key does not yet exist in it
    If Not dict.Exists(prNr) Then
        dict.Add prNr, x
        prNumbers(prArrCount) = prNr
        prArrCount = prArrCount + 1
    Else
        If priority < dict(prNr)(2) Then
            dict(prNr) = x
        End If
    End If

Next i

Now, I want to print the contents of the entire dictionary. I try to load the contents of the dictionary into an array, and then print the array as follows.

For i = 3 To (prArrCount + 3)
    x = dict(prNumbers(i - 3))

    Range("A" & i).Value = i - 2
    Range("B" & i).Value = x(0)
    Range("C" & i).Value = x(1)
    Range("D" & i).Value = x(2)
    Range("E" & i).Value = x(3)
    Range("F" & i).Value = x(4)
Next i

The issue is that it does not allow me to store the contents of the dictionary in an array as per line x = dict(prNumbers(i - 3)). Is there a way of doing this, or another way of printing the array?

If x is the array from your first bit of code then x is an array. You have to specify a location of the array to store the value in — Zac
– Zac, Commented Oct 4, 2016 at 15:12
Thanks for this! I've specified what location to save the various items in the dictionary to, but now I get a Type Mismatch? It comes from the dict(prNumbers(i - 3))(0) section. I'm assuming there is some problem with using an array as the key for a dictionary, but how can one fix this? — Cornel Verster
– Cornel Verster, Commented Oct 4, 2016 at 15:24

Tim Williams · Accepted Answer · 2016-10-04 16:13:45Z

1

You cannot assign the dictionary value to an array like that.

Something like this is fine:

Sub TT()

    Dim dict As New Scripting.Dictionary
    Dim x(3) As Variant, y() As Variant

    x(0) = "A"
    x(1) = "B"
    x(2) = "C"
    x(3) = "D"

    dict.Add "blah", x

    y = dict("blah")

    Debug.Print Join(y, ",")

End Sub

answered Oct 4, 2016 at 16:13

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

VBA - Extracting Array Data from a Dictionary

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related