VBA Array with Non-Consecutive Indices

[lyanik]

I know in Python you can assign a value to an array index that doesn't exist yet. That's what I want to mimic in VBA if possible.

For example, I have a list of accounts and items they map to:

40100 => 1
42000 => 2
43000 => 8
44456 => 27

What i want is an array that I can get values from like this: arr(40100) = 1, arr(43000) = 8.

In my current workaround, I have to dimension the array to be lbound() = 40100 and ubound() = 44456 and only (4) items have values. That works, but it seems like a waste of memory and is more difficult to scroll through in the locals window.

For reference, in Python currently, the code is:

str1 = str(acct[0][0:5])
account_map[str1] = acct_holder[:]

 

[Marcelo Branco]

I think you should use a Dictionary object.
Take a look at
https://excelmacromastery.com/vba-dictionary/

M.

 

[lyanik]

I think you should use a Dictionary object.
Take a look at
https://excelmacromastery.com/vba-dictionary/

M.

Yes, this is exactly what I need.

I knew something like this must exist, but I didn't know the reference to turn on and the exact name of the object. Thank you so much.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

Yes, the Dictionary object is very useful - using it you can make flexible and very fast codes. Worth every minute of your time learning when and how to use it.

By the way, you can also add objects (worksheets, ranges and even another dictionary) as items of a dictionary and use the methods of such objects - just a simple example of using a Dictionary of dictionaries.
http://www.techbookreport.com/tutorials/vba_dictionary2.html

But this is another story!

M.