Repeat a variable number of measurements in VBA

Question

I have a case in an Excel macro (VBA) where I would like to measure an array where the number of dimensions and the boundaries of each dimension are determined at runtime. I allow the user to set a series of combinatorial options by creating columns for each type of parameter and filling in the options below. The number of columns and the number of options are determined at run time by checking the sheet.

Some code needs to be executed through each combination (one selection from each column), and I would like to store the results in a multidimensional array.

The number of measurements is likely to be from about 2 to 6, so I can always go back to a bunch of if else blocks if necessary, but it seems like there should be a better way.

I thought it would be possible if I could build a Redim at runtime as a string and execute a string, but this is not possible.

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

Answer 1

I am sure there is no way to do this in a single ReDim expression. Select Case may be a little tidier than the "bunch of If...Else blocks", but you still write a lot of individual ReDim s.

Working with arrays in VBA, where you don’t know in advance how many measurements they will have, it’s a bit PITA, and also ReDim not very flexible, there is also no clear way to test the array to see how many sizes it has (you need to go through access attempts higher dimensions and trap errors or hack into the underlying memory structure - see this question ). This way, you will need to keep track of the number of dimensions and write long Case statements every time you need access to the array, as the syntax will be different.

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

Answer 2

"Some code has to go through each combination (one selection from each column), and I would like to save the results in a multidimensional array."

To begin with, I will transfer the desired Range object to 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 

You can then iterate through this array to find the size you need and the data you can store in the array (with the dimensions) you need.

---

Little background:

Redim: reallocates space to store an array variable.

You are not allowed to reinstall the array if you first define an array with a Dim statement. (e.g. Dim vArray(1 to 6) As Variant ).

UPDATE : explicitly indicate what is allowed and what is not in Redim.

Each time you use Redim , it resets the original Array to the parameters that you define later.

There is a way to save your data with Redim Preserve , but it only allows you to change the last dimension of a multidimensional array, where the first dimension remains as the original.

Answer 3

As I understand it, your users can specify the sizes and their capture by filling out the excel sheet. This means that you need to get the last row containing the value and the last column.

So take a look at: Excel VBA- Finding the last column with data

Use Redim to resize the array. If you want to keep some records, use Redim Preserve