arrays - Redim variable number of dimensions in VBA -

i have case in excel macro (vba) i'd dimension array number of dimensions , bounds of each dimension determined @ runtime. i'm letting user specify series of combinatorial options creating column each option type , filling in possibilities below. number of columns , number of options determined @ run time inspecting sheet.

some code needs run through each combination (one selection each column) , i'd store results in multidimensional array.

the number of dimensions between 2 6 can fall bunch of if else blocks if have feels there should better way.

i thinking possible if construct redim statement @ runtime string , execute string, doesn't seem possible.

is there way dynamically redim varying number of dimensions?

i'm pretty sure there no way of doing in single redim statement. select case may marginally neater "a bunch of if...else blocks", you're still writing out lot of separate redims.

working arrays in vba don't know in advance how many dimensions have bit of pita - redim not being flexible, there no neat way of testing array see how many dimensions has (you have loop through attempts access higher dimensions , trap errors, or hack around in underlying memory structure - see question). need keep track of number of dimensions, , write long case statements every time need access array well, since syntax different.

i suggest creating array largest number of dimensions think you'll need, setting number of elements in unused dimensions 1 - way have same syntax every time access array, , if need can check using ubound(). approach taken excel developers range.value property - returns 2-dimensional array 1-dimensional range.