Referencing array in 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 get extract any kind of information out of an array retrieved from the dictionary.

Instead of dict("Apples") = 3 use dict.Add "Apples", Array(3, 5) to store an array. To get this into a cell you need to write a code with the desired logic. For a good resource on dictionaries look at Excel VBA Dictionary – A Complete Guide. Note that SO is not a free code writing service, so give it a try yourself and if you get stuck or errors come back with your code or a minimal reproducible example. — Pᴇʜ
– Pᴇʜ, Commented Mar 20, 2020 at 10:55
I updated the question. I've been learning with Paul Kelly's guide. Usually an array does not give me trouble, except for when it's stored in a dictionary. It seems that Items = dict(sFruit) is not the way to retrieve an array. — QuakeFX
– QuakeFX, Commented Mar 20, 2020 at 13:12

Pᴇʜ · Accepted Answer · 2020-03-23 09:36:30Z

2

Here is an example of how to write arrays into dictionaries and how to access them later.

Option Explicit

Public Sub DictArrayExample()
     Dim dict As Object
     Set dict = CreateObject("Scripting.Dictionary")

     'create an array
     Dim Arr() As Variant
     Arr = Array("Item1", "Item2", "Item3") 'Array Index 0 to 2

     'write the array into dictionary
     dict.Add "apples", Arr

     'or directly into dictionary without variable
     dict.Add "bananas", Array("banana1", "banana2")


     'directly access array inside dictionary
     Debug.Print dict("apples")(1) 'index 1 = Item2

     'get the array from the dictionary into another variable
     Dim RetrieveArr() As Variant
     RetrieveArr = dict("apples")

     'access array in that variable
     Debug.Print Join(RetrieveArr, ", ")
     Debug.Print RetrieveArr(0) 'index 0 = Item 1
End Sub

answered Mar 23, 2020 at 9:36

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

1 Comment

Notus_Panda Over a year ago

For people using this method, don't forget you can't adjust the array elements later on without a small work-around. See this question for the work-around.

Oddclock · Accepted Answer · 2023-11-20 23:04:52Z

You can make a 2-D dictionary with keys for the rows and the columns to access a 2-D array. It might even support arrays of more than 2 dimensions.

This demo stores the rows in the dictionary as 1-D arrays with the row name as the key. The dictionary also stores the column number under the column name. This allows retrieval of the row array from the dictionary by the row name, and immediately indexing it with the column index pulled from the dictionary by the column name:

Array Element = Dictionary("Row Key") (Dictionary("Column Key"))

Example 4x4 array with row and column titles:

title	item1	item2	iterm3
apples	a1	a2	a3
bananas	b1	b2	b3
citrus	c1	c2	c3

'_2022_12_23
Function fArray2Dictionary()
    Dim dicArray As Object  'this is the dictionary
    Dim arrRows() As String 'the array split into an array of rows
    Dim arrRow() As String  'one row split into an array of columns
    Dim intIndex As Integer 'for iterating through the columns in a row
        
    'here's the example array
    Const strArray = "title,item1,item2,item3;apples,a1,a2,a3;bananas,b1,b2,b3;citrus,c1,c2,c3"
    
        Set dicArray = CreateObject("Scripting.Dictionary") 'create a dictionary
    
        arrRows = Split(strArray, ";")  'split array into rows
        For intIndex = LBound(arrRows) To UBound(arrRows)   'for each row
            arrRow = Split(arrRows(intIndex), ",")  'split row into array of columns
            dicArray(arrRow(LBound(arrRow))) = arrRow   'put array in dictionary with row name as key
        Next
        arrRow = Split(arrRows(LBound(arrRows)), ",")   'get the first row with column names as array
        For intIndex = LBound(arrRow) + 1 To UBound(arrRow) 'for each column name
            dicArray(arrRow(intIndex)) = intIndex   'store field index dictionary with column name as key
        Next
        
    'now access the dictionary by row and column title to get a particular array value
        Debug.Print "apples:item1= " & dicArray("apples")(dicArray("item1"))
        Debug.Print "apples:item2= " & dicArray("apples")(dicArray("item2"))
        Debug.Print "apples:item3= " & dicArray("apples")(dicArray("item3"))
        Debug.Print "bananas:item1= " & dicArray("bananas")(dicArray("item1"))
        Debug.Print "bananas:item2= " & dicArray("bananas")(dicArray("item2"))
        Debug.Print "bananas:item3= " & dicArray("bananas")(dicArray("item3"))
        Debug.Print "citrus:item1= " & dicArray("citrus")(dicArray("item1"))
        Debug.Print "citrus:item2= " & dicArray("citrus")(dicArray("item2"))
        Debug.Print "citrus:item3= " & dicArray("citrus")(dicArray("item3"))
    
    End Function

Debug.Print "apples:item1= " & dicArray("apples")(dicArray("item1")) got result "apples" not a1. Something wrong here not as expected
You're right, I tried it and it wouldn't even compile. The line "dicArray(arrRow(intIndex)) = intIndex" was missing the "dicArray", but I put it back on above. It works for me now.

Collectives™ on Stack Overflow

Referencing array in VBA dictionary

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related