VBA Create new dictionary on loop

Question

What I am doing? Presume 5 sample tables. I loop through the items, and use the dictionary.exists method to add only unique items. This way I get 1 dictionary object with unique values, the problem is, I use the same dictionary "IDsDict" for this, and I have to delete the dict.items so I can obtain the next set of unique IDs.

I would like to a) either "store" the dictionary seperately from the loop so that dict.RemoveAll does not effect the copy of this dictionary b) or add a new dictionary dynamically for each new run through of the loop

Is either one possible?

For uniqueRow = LBound(IDsDataFiltered, 2) To UBound(IDsDataFiltered, 2)
            If Not IDsDict.Exists(IDsDataFiltered(1, uniqueRow)) Then
                counter = counter + 1
                globalcounter = globalcounter + 1
                'Add to Dictionaries
                IDsDict.Add IDsDataFiltered(1, uniqueRow), globalcounter
            End If
Next uniqueRow

collUniqueDicts.Add IDsDict, timeStampsIDs
IDsDict.RemoveAll

EDIT: ultimately, I want to find out the sets of unique IDs, that are in different tables, and return other columns from those tables. Application: assume each table has ID, Timestamp, and Location. This way I can track the locations of IDs over time.

Not entirely sure what you are trying but can you create 5 initials dicts and put them in an array and loop that doing your inner loop adding to each dict? — QHarr
– QHarr, Commented Nov 27, 2018 at 16:11
I would probably move that section of code into a Function which returns a dictionary object. Change the variables from earlier in the code into function parameters, and then you can call it whenever you need the new dictionary. — Mistella
– Mistella, Commented Nov 27, 2018 at 16:12
@Mistella thank you I have thought about this method. My estimation is that a dictionary returned as a function value will have the same "lifetime" problems, once I rerun the function to get the next dictionary, it will override the old dictionary variable (i.e. the function value). Is this correct? — Hunzen
– Hunzen, Commented Nov 28, 2018 at 7:58
@Hunzen I don't believe so; if you had the function create a new dictionary each time it was called, it should have the same effect as @Pragmateek's answer; whether you assign the returned dictionary to a variable before putting it in collUniqueDicts or just insert it directly. The issue with your original code, is not that the dictionary was being overriden, so much as that you were constantly using the same dictionary object. — Mistella
– Mistella, Commented Nov 28, 2018 at 14:07
To elaborate: there was one dictionary object: populate - assign to collUniquDicts - removeall - repopulate - assign again - removeall, etc. @Prmateek's answer disassociates the IDsDict variable from the existing dictionary and assigns it a new one. With a function, any referenced objects - including the dictionary - are "lost" to the function after the function ends. Thus, the next function call, would result in a dictionary completely separate from any previous ones. (Exception - global vars) — Mistella
– Mistella, Commented Nov 28, 2018 at 14:15

Mathieu Guindon · Accepted Answer · 2018-11-27 16:45:49Z

3

You're storing the same object pointer 5 times - .RemoveAll clears the items you added in the previous run, so you end up with 5 copies of the same dictionary, with only the items that were added last (assuming RemoveAll doesn't run for the last iteration?).

If you want 5 objects, you need 5 different pointers.

I'd pull the dictionary-populating code into its own dictionary-returning function.

Private Dictionarize(...) As Dictionary
    Dim result As Dictionary
    Set result = New Dictionary
    '...populate one dictionary
    Set Dictionarize = result
End Function

Give the function the parameters it needs to work with, and then invoke it 5 times, as needed:

collUniqueDicts.Add Dictionarize(...)

edited Nov 27, 2018 at 16:45

answered Nov 27, 2018 at 16:35

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

Comments

Pragmateek · Accepted Answer · 2018-11-27 16:26:35Z

1

Instead of IDsDict.RemoveAll create a new Dictionary after each sample processing:

Set IDsDict = New Dictionary

answered Nov 27, 2018 at 16:26

Pragmateek

13.6k9 gold badges80 silver badges112 bronze badges

2 Comments

Hunzen Over a year ago

Quick tested this, and it did it. Using this I can keep the dictionary values that I stored in collUniqueDicts and I can still populate IDsDict appropriately in the next iteration without old values in the dict.

Pragmateek Over a year ago

@Hunzen Exactly, because you're working with a fresh Dictionary.

Collectives™ on Stack Overflow

VBA Create new dictionary on loop

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related