Naming Variables With Other Variables?

[craig.penny]

This might be a ridiculous want but I'll ask anyway. I'd like to be able to create a number of variables based on user input. Something like this:

'-------------------------
Dim Counter as long
Dim X as long

X = inputbox(prompt:="Enter Number of Blah Blah")

For Counter = 1 to X
Dim VarCounter as Double 'I want it to be Var1, Var2, ...
Next X

'-------------------------

The reason behind this is that I'm trying to find a work around to the limitation of only being able to change the last dimension of an array with ReDim. I feel like there is some way I could do this with a CallByName but if it's possible then I'm not getting the syntax right.

Thanks in advance for any guidance!

 

[craig.penny]

bump?

 

[Norie]

Craig

I won't say it's a ridiculous thing to ask - this sort of thing is available in other programming languages, but not in VBA.

There are a few workarounds like arrays, collections etc but nothing that will do what you specifically want.

The thing that kind of gets me is why people think being able to do this might be particularly useful.

For example I can't quite see how it would help you with redimming arrays.

Perhaps you should be looking at how your arrays are setup.

PS You do realise you can redim other dimensions of an array but there are some rules - take a look at the Help topic for that.

 

[SamTYler]

Get all your inputs, then

Dim myArray As Variant
 
Get all your inputs, then
 
Redim myArray(X, Y, Z)

When thinking of Arrays within Excel, look at them as arVariable(R, C), where R is Sheet Rows and C is Sheet Columns.

If you were to set a single Cell("A1") equal to a two dimensional Array, say arrSomething(3, 6), the array's values would be pasted in Range("A1:F3").

For really large data sets, you can look at an Array as arVariable(W, S, R, C), which would be WorkBooks(W).Sheets(S).Cells(R, C).

If you're using arrays deeper than that, you can teach us about 'em.

 

[craig.penny]

Thanks guys. This is a fine example of misunderstanding. I'm so used to using redim preserve that I thought only the last dimesion could change in any situation. As Norie pointed out

Dim Stuff() as Integer
ReDim Stuff(1 to 2, 1 to 5, 1 to...) as Integer

works just fine!

Thanks for answering and pointing me in the right direction!