Is it possible to add a dictionary to an array in Excel VBA?

Question

I'm trying to figure out if this is a possibility in terms of the capability of excel. Consider the following code in what I'm trying to do:

Dim some_text, split_text() As String
Dim some_array_dict() As String 'This is the array to store the dictionaries
Dim some_dict As Dictionary 'The dictionaries that I'll be storing in the array above

ReDim some_array_dict(y) As String 'y is previously defined as an integer

For i = 0 To y - 1
    Set some_dict = New Dictionary

    some_text = some_array(2, i)
    split_text = Split(some_text, " ")
    
    For j = 0 To UBound(split_text)
        some_dict.Add split_text(j), 1
    Next j
    
    some_array_dict(i) = some_dict 'Issue
    
    Debug.Print some_array_dict(i) 'For debugging purposes
Next i

The following is the line of code that gives me the error:

some_array_dict(i) = some_dict

Can anyone help with this?

I think Tim's solution is the only one that actually works. Be careful because Dictionary is a reference type so setting each array component to the same object will result in the same object being referred by all array components. I don't think that this is what you want.. — Ioannis
– Ioannis, Commented Jun 20, 2014 at 0:31
More specifically to Tim's answer, I had to Dim as a Dictionary. That actually ended up fixing the issue I was facing. Thanks for help! — lcardona89
– lcardona89, Commented Jun 20, 2014 at 14:38

Tim Williams · Accepted Answer · 2014-06-19 20:30:39Z

4

Array is declared as wrong type, and you need to use Set when assigning objects

Sub Tester()

    Dim arr_dict() As Object, x As Long

    ReDim arr_dict(1 To 3)
    For x = 1 To 3

        Set arr_dict(x) = CreateObject("scripting.dictionary")

        With arr_dict(x)
            .Add "hello", 1
            .Add "there", 2
        End With

    Next x

End Sub

answered Jun 19, 2014 at 20:30

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Comments

jeffrey_t_b · Accepted Answer · 2014-06-19 20:30:36Z

2

It looks like you are trying to assign a Dictionary to a string array.

Try:

ReDim some_array_dict(y) As Dictionary

answered Jun 19, 2014 at 20:30

jeffrey_t_b

1,76912 silver badges18 bronze badges

Comments

paulroho · Accepted Answer · 2014-06-19 20:32:21Z

2

Since Dictionary is an object, you have to use Set for the assignment:

Set some_array_dict(i) = some_dict ' No issue

answered Jun 19, 2014 at 20:32

paulroho

1,2761 gold badge11 silver badges28 bronze badges

Collectives™ on Stack Overflow

Is it possible to add a dictionary to an array in Excel VBA?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related