In VBA, How To Use a String to Refer to an Array Name

[mitachoo]

I am perplexed trying to do something that sounds simple, but I'mgetting nowhere!

In VBA, I have the name of an Array that is constructed via concatenation of several strings. The formula is:
Name = Hits_Index_Name & A & ")"

And the concatenation comes out like this:
Hits_Index_A(0,1)

The above is the name of an Array from which I'm attempting to pull data.

How do I do this?

 

[Dermot]

I don't think you can, assuming you are asking whether you can create some arrays and then choose the one you want to use, based on the array name being the same as a given string. You can't compare array names with anything.

But what you can do, is when you define those arrays, make a list of their names, and when you want to choose an array, find it in the list. I can thin of two ways to arrange your data to make this work. One is to have a single array containing all your arrays (assuming they are the same size), so if you had 10 arrays, each 50 rows and 5 columns, you could define a single array Array(10,50,5), and then store a list of the 10 names that apply to each of the 10 arrays of 50x5. Then when you create a text array name, you can look it up in the list, and if, say, it is 3, then you use Array(3,....).

If the arrays could be different sizes, then I would set them up as separate classes, and define a class array containing them

 

[mitachoo]

I don't think you can, assuming you are asking whether you can create some arrays and then choose the one you want to use, based on the array name being the same as a given string. You can't compare array names with anything.

But what you can do, is when you define those arrays, make a list of their names, and when you want to choose an array, find it in the list. I can thin of two ways to arrange your data to make this work. One is to have a single array containing all your arrays (assuming they are the same size), so if you had 10 arrays, each 50 rows and 5 columns, you could define a single array Array(10,50,5), and then store a list of the 10 names that apply to each of the 10 arrays of 50x5. Then when you create a text array name, you can look it up in the list, and if, say, it is 3, then you use Array(3,....).

If the arrays could be different sizes, then I would set them up as separate classes, and define a class array containing them

Thanks Dermot! As a workaround I had done something similar to what you suggested, and it sounds like this workaround is the only way to do it.

Thanks again!