Redim variable number of dimensions in VBA

Question

I have a case in an Excel macro (VBA) where I'd like to dimension an array where the number of dimensions and the bounds of each dimension are determined at runtime. I'm letting the user specify a series of combinatorial options by creating a column for each option type and filling in the possibilities below. The number of columns and the number of options is determined at run time by inspecting the sheet.

Some code needs to run through each combination (one selection from each column) and I'd like to store the results in a multidimensional array.

The number of dimensions will probably be between about 2 to 6 so I can always fall back to a bunch of if else blocks if I have to but it feels like there should be a better way.

I was thinking it would be possible to do if I could construct the Redim statement at runtime as a string and execute the string, but this doesn't seem possible.

Is there any way to dynamically Redim with a varying number of dimensions?

Answer 1

I'm pretty sure there is no way of doing this in a single ReDim statement. Select Case may be marginally neater than "a bunch of If...Else blocks", but you're still writing out a lot of separate ReDims.

Working with arrays in VBA where you don't know in advance how many dimensions they will have is a bit of a PITA - as well as ReDim not being very flexible, there is also no neat way of testing an array to see how many dimensions it has (you have to loop through attempts to access higher dimensions and trap errors, or hack around in the underlying memory structure - see this question). So you will need to keep track of the number of dimensions, and write long Case statements every time you need to access the array as well, since the syntax will be different.

I would suggest creating the array with the largest number of dimensions you think you'll need, then setting the number of elements in any unused dimensions to 1 - that way you always have the same syntax every time you access the array, and if you need to you can check for this using UBound(). This is the approach taken by the Excel developers themselves for the Range.Value property - which always returns a 2-dimensional array even for a 1-dimensional Range.