Extracting array from VBA dictionary

Question

I cannot find out how to store and access an array in a VBA dictionary.

I have a key with multiple items, say "Apple" with value "3" and quantity "5".

A single item would not be a problem, ie: dict("Apples") = 3

But how do I store and retrieve multiple values? Lets say I want to add "Apples, 3, 5" and then move to cell C4 and automatically paste Apples there, 3 in C5 and 5 in C6.

So far I've tried:

Dim last_row As Long
last_row = Cells(Rows.Count, 1).End(xlUp).Row
Dim last_col As Long
last_col = ActiveSheet.UsedRange.Columns.Count

Dim strVal As String
Dim Item(0 To 99) As String
Dim header As Range
Dim rng As Range

For Each rngCell In Range(Cells(1 + i, 1), Cells(last_row, 1))
    i = i + 1
    strVal = rngCell.Text
    If Not dict.Exists(strVal) Then
            n = 0
            For Each headerCell In Range(Cells(i, 2), Cells(i, last_col))
                n = n + 1
                Item(n) = headerCell.Text
                'MsgBox headerCell.Text
            Next headerCell
            dict(strVal) = Item
    Else
        MsgBox "already exists"
    End If
Next rngCell

Dim Items(0 To 99) As String
sFruit = InputBox("Check value of key")
Items = dict(sFruit)
MsgBox "The value of " & sFruit & " is " & Items(2)

It's the last part that isn't working. Whether I declare Items as Variant or String or Object or even if I put Item = Items(2) above the message box and refer to that. I can't extract any kind of information out of an array retrieved from the dictionary. It seems that Items = dict(sFruit) is not the way to retrieve an array.

Exact duplicate of your previous question: Referencing array in VBA dictionary. Edit your question to improve it then it can be re-opened, it was closed for a reason. — Wolfie
– Wolfie, Commented Mar 20, 2020 at 14:54
Hi Wolfie, it was edited but not reopened. Therefor I posted a new question. — QuakeFX
– QuakeFX, Commented Mar 20, 2020 at 15:11
You have to wait for it to be re-opened pending a review, depending whether or not your edit is sufficient — Wolfie
– Wolfie, Commented Mar 20, 2020 at 15:13
You only have a single array Item but you're adding it to every dictionary key's value? It would help a lot to explain what your data is and what the code is supposed to acheive. — Tim Williams
– Tim Williams, Commented Mar 20, 2020 at 16:01

Warcupine · Accepted Answer · 2020-03-20 14:58:36Z

0

If you dim items as variant, and use array() it should work.

This is just an example and not your code.

Sub testdict()
    Dim dict As Object

    Set dict = CreateObject("Scripting.Dictionary")

    dict.Add "apple", Array(1, 3)
    dict.Add "orange", Array(2, 5)

    Dim k As Variant
    Dim i As Long
    Dim vals As Variant
    For Each k In dict
        vals = dict(k) 'set a variant to an array
        Debug.Print vals(1) 'print the second element
        For i = LBound(dict(k)) To UBound(dict(k)) 'iterate through all array elements
            Debug.Print k, dict(k)(i) 'print everything in the dict
        Next i
    Next k

End Sub

answered Mar 20, 2020 at 14:58

Warcupine

4,7203 gold badges18 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

Extracting array from VBA dictionary

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related