Excel VBA: Unable to reassign value to array

Question

I have code that loops through a dictionary. the value for each key in the dictionary is a 2-item array (dictionary looks like name: [string, integer]). when I reference the dictionary later on, I can see and print the string and integer in the array belonging to the dictionary entry, but I can't change the integer through a normal assignment like dictionary(name)(2) = 5; after doing so in the code, I print the array value to a debug file, and the array value is the original value, not its changed value. I have no idea why this doesn't work, and how I can get it to work. everything I've read on arrays says you just assign array(0) = something.

here is part of the code below:

defining the original dictionary:

dim Dict as Object
Set Dict = CreateObject("Scripting.Dictionary")

for i = 1 to 10
    strnum = "str"&i
    Dict.Add strnum, array("str"&i+1,0) 
next
'printing each item in dict returns "Str1: [str2,0]", etc. as it should

working with the dictionary:

For each Cell in Range("a1:a11")
    If Cell.Value <> "" And Dict.exists(Cell.Value) Then
        name = Cell.Value
        range = Dict(name)(0)
        If Dict(name)(1) = 1 Then
        'we have already located the name here
        Else
            Dict(name)(1) = 1
            s = "Setting the found flag to " & Dict(name)(1)
            debug.print s
            'Dict(name)(1) returns 0 when it should return 1
        end if
    end if
next cell

Range a1:a11 is Str1,Str2,Str3,Str4,Str5...Str11.

What can I do to fix this?

The code snippet has numerous errors. (for example -- no Set before CreateObject and using Add in the loop like that will cause a key already associated with value error). Please post code that actually works and reliably reproduces the problem. — John Coleman
– John Coleman, Commented Apr 18, 2016 at 16:17
Updated the code. It should work (or not work) as specified now. — Metalgearmaycry
– Metalgearmaycry, Commented Apr 18, 2016 at 16:23
I'm just trying to generalize variable names to protect the innocent lol. let me cook up something else :). Edit: and done. — Metalgearmaycry
– Metalgearmaycry, Commented Apr 18, 2016 at 16:32

John Coleman · Accepted Answer · 2016-04-18 17:03:04Z

0

You are creating some weird aliasing with

for i = 1 to 10
    Str = "str"&i
    arr(1) = "str"&i+1
    arr(2) = 0
    Dict.Add Str, arr
next

since you only created a single array when you dimensioned arr.

You could create a dictionary of dictionaries to do what you wanted:

Sub test()

Dim Dict As Object, Str, i, arr
Set Dict = CreateObject("Scripting.Dictionary")


For i = 1 To 10
    Set arr = CreateObject("Scripting.Dictionary")
    arr.Add 1, "str" & i + 1
    arr.Add 2, 0
    Str = "str" & i
    Dict.Add Str, arr
Next

For i = 1 To 10
    Debug.Print (Dict("str" & i)(1))
Next i

Dict("str1")(1) = "Bob"
Debug.Print Dict("str1")(1) 'prints Bob

End Sub

edited Apr 18, 2016 at 17:03

answered Apr 18, 2016 at 16:43

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

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

5 Comments

Metalgearmaycry Over a year ago

Even making that change (I didn't know about the Array() function), the problem persists.

John Coleman Over a year ago

I've used dictionaries of dictionaries and dictionaries of collections, but never realized that it is somewhat tricky to make dictionaries of arrays. I would just go with a dictionary of dictionaries.

Metalgearmaycry Over a year ago

stackoverflow.com/questions/2404212/… is an answer to this same topic.

Metalgearmaycry Over a year ago

Dictionary of dictionaries is what i think I will go with. thanks for your help John

John Coleman Over a year ago

You could also look into dictionaries of arraylists, created with CreateObject("System.Collections.ArrayList ")

Collectives™ on Stack Overflow

Excel VBA: Unable to reassign value to array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related