How do I create an array of variables in excel VBA

Question

0.00/5 (No votes)

See more:

I would like to create an array containing variable names in order to be able to assign values in a loop.

What I have tried:

I tried something like this code but, of course it didn't work:

varArray = Array(a,b,c,d,e)
valueArray = array(10,20,30,40,50)
for i = 0 to 4
varArray(i) = valueArray(i)
Next i

And I would like to be able to access the variable by their names like msgbox(c) should give me 30. Does anyone have a solution for this problem?
( Please spare me with any advice to access the values by using a regular array type like msgbox(varArray(3)) )
Thanks.

Posted 4-Oct-19 12:44pm

Member 14613037

Updated 4-Oct-19 21:56pm

Add a Solution

Comments

Dave Kreskowiak 4-Oct-19 22:39pm

There is no way to do that in VBA, nor in most programming languages.

You also don't need it. You just need the one array that has your values.

Member 14613037 4-Oct-19 23:37pm

There is no way to do that in VBA, nor in most programming languages: Yes you can do it in other languages like Python and R.
You also don't need it. You just need the one array that has your values: yes I need to do it the way I am asking. If other languages made it possible, it's because there is a need for it.

Dave Kreskowiak 5-Oct-19 5:40am

I don't give a sh*t 'cause I would never use that "feature". I can see something like that promoting the use of very short variable names which would just make the code harder to read, debug, and maintain.

Just because a language supports such a feature does not mean there is a "need for it". It's just a shortcut for defining variables.

2 solutions

Solution 1

Quote:
I would like to create an array containing variable names in order to be able to assign values in a loop.

As far as I know, this feature does not exist in vba.

This does not create an array with references to variables

VB

varArray = Array(a,b,c,d,e)

It create an array with the contains of those variables.

I know languages that can do it, but they embed a 'language macro compiler' which compile and execute and arbitrary string. SQL can do it.

You should explain the reason why you need this, may be there is another way.

Posted 4-Oct-19 13:22pm

Patrice T

Comments

Member 14613037 4-Oct-19 23:45pm

The reason is that I need to address these variables by their names in the program, not by a unique name+serial#. For sure there is another way to do it, I just have to write 100 lines of code to assign the values individually to each variable.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2019-10-04T21:57:00

Solution 2

You could use a VBA Dictionary[^], with variable names as keys.

Posted 4-Oct-19 21:57pm

phil.o

Comments

Member 14613037 5-Oct-19 9:29am

If I understand your suggestion, the code should look like this:

set X = CreateObject("Scripting.Dictionary")
varArray = Array("a","b","c","d","e")
valueArray = array(10,20,30,40,50)
for i = 0 to 4
X.add varArray(i), valueArray(i)
Next i

However, this will store a series of couples ("a",10), ("b",20)... in object X but I cannot access individual values by typing X.a to get value 10, if you see what I mean.
Instead, I should search for a given key to get the value. It's not better than a 2 dimensional array. Do you have any suggestion?

phil.o 5-Oct-19 9:44am

This is exactly what it could allow you to do. After all, this is what you have: variable names (strings) on one side, and their respective values on the other side.
You can access the value of a specific variable (i.e., key) this way:

myValueA = X.Item("a")
myValueB = X.Item("b")
'' and so on ...

Member 14613037 5-Oct-19 12:00pm

Thanks a lot, it works!

phil.o 5-Oct-19 12:01pm

You're welcome!