CODEWITHSUNDEEP
arraysexcelvbadictionary
Cornel Verster
Cornel Verster

Reputation: 1781

VBA - Extracting Array Data from a Dictionary

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?

Upvotes: 0

Views: 1215

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

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

Upvotes: 1

Related Questions