CODEWITHSUNDEEP
arraysvbaexcelmultidimensional-array
Q---ten
Q---ten

Reputation: 2526

Redim variable number of dimensions in VBA

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?

Upvotes: 6

Views: 2962

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

"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."

To begin with, I would transpose desired Range object into a Variant.

Dim vArray as Variant
'--as per your defined Sheet, range
'this creates a two dimensional array
vArray = ActiveWorkbook.Sheets("Sheet1").Range("A1:Z300").Value2

Then you could iterate through this array to possible find the size and data you need, which you may save it to an array (with the dimensions) you need.

Little Background:

Redim: Reallocates storage space for an array variable.

You are not allowed to Redim an array, if you are defining an array with a Dim statement initially. (e.g. Dim vArray(1 to 6) As Variant).

UPDATE: to show explicitly what's allowed and what's not under Redim.

enter image description here

Each time you use Redim it resets your original Array object to the dimensions you are defining next.

There's a way to preserve your data using Redim Preserve but that only allows you to change the last dimension of a multidimensional array, where first dimension remains as the original.

Upvotes: 0

aucuparia
aucuparia

Reputation: 2051

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.

Upvotes: 1

psychicebola
psychicebola

Reputation: 949

As I understood your users can specify dimensions and their seize by filling in the excel-sheet. This means you have to get the last row containing a value and the last column.

Therefore, have a look at: Excel VBA- Finding the last column with data

Use Redim to change the array's size. If you want to keep some kind of entries use Redim Preserve

Upvotes: 0

Related Questions