6

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?

haim770
  • 48,394
  • 7
  • 105
  • 133
Q---ten
  • 2,168
  • 1
  • 13
  • 17
  • Do you need up to six dimensions or do you simply need a two dimensional array with a second rank that is 1 to 6? i.e. redim myArr(1 to 99, 1 to 6) or redim myArr(1 to 9, 1 to 9, 1 to 9, 1 to 9, 1 to 9, 1 to 9) –  Jul 08 '15 at 07:05
  • "construct the Redim statement at runtime as a string and execute the string" : This kind of delegation type execution is not possible in VBA. "varying number of dimensions" : So you want to change a single D array to multi D and then change back and forth or continue to change multi D? – bonCodigo Jul 08 '15 at 07:06

3 Answers3

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.

Community
  • 1
  • 1
aucuparia
  • 2,021
  • 20
  • 27
  • I think you understand the problem correctly. I've found that it's actually possible to do something with variant arrays of variant arrays, but it's even more hideous than using case statements. One of the problems (that I really should have realised initially) is that if it's hard to dimension an array with variable dimensions, it will be hard to index into it - even if you know what the indices are. I am going to use the approach suggested here with a fixed max number of dimensions as it will be the cleanest solution. – Q---ten Jul 09 '15 at 03:13
0

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

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • "which you may save it to an array (with the dimensions) you need" - I think this is the bit the OP is asking how to do. – aucuparia Jul 08 '15 at 07:17
  • @aucuparia When OP is iterating through the (transposed range) array, he has the opportunity to have a *counter* for both rows and cols. So there he gets the chance to find out what dimension to declare his desired array. Or he could simply go on creating new arrays and copying them to another till he gets it done. I am also quite skeptical about his needs for this dimensions: whether it's 2D with ranks or entirely multi D. *Pardon me for my laziness.* – bonCodigo Jul 08 '15 at 07:23
  • "You are not allowed to Redim an array, if you are defining an array with a Dim statement initially" That's not accurate - you can't Redim unless you initially Dim **without dimensions**. – Rory Jul 08 '15 at 07:30
  • 1
    @Rory `you can't Redim unless you initially Dim without dimensions` If I'm not mistaken, you can provided you do not specify the type. This `Redim x(1 To 3)` will work without `Dim x` up front. – L42 Jul 08 '15 at 07:43
  • @L42 Yes I should have been more explicit - I meant you can use a Dim statement first, but you can't include dimensions. You can indeed Redim a Variant without using Dim at all - though I think it's bad practice. – Rory Jul 08 '15 at 07:48
  • @bonCodigo - once you have the desired number of dimensions in a counter, there is no concise way to use that in a `ReDim` statement (which is I believe the point of the question). I.e. you can't do `ReDim(myArray, upperBound, numberOfDimensions)` - the number of dimensions is *implicit* in the number of parameters passed to `ReDim`. I agree that an array with a dynamic number of dimensions is probably not the best way to solve the problem! – aucuparia Jul 08 '15 at 09:03
  • Let me start with @Rory. `Dim vArray(1 to 6) As Variant)` can this be `Redim`? No it can't because it's already dimensioned. And that's what I meant in my sentence by giving an example in the bracket for that declaration prevents user from doing so. Probably I should have *explicitly* listed down all the combinations (which I updated above) which do not allow `Redim`. To be precise, not only Dim but also for all private, public arrays under the above criteria, the Redim will not work. – bonCodigo Jul 09 '15 at 02:39
  • @aucuparia I agree with most part of it you pointed out. Come to think of it, can you visualize what sort of a collection device the OP is after? One of his option's could be then, nested arrays but that doesn't change the fact outer most array will remain at its original dimensions (probably with a Redim preserve on last dimension). But the next question would be, **how would you retrieve data from a such a dynamic/ad-hoc structure**? *10,000* loops and Bound checks...perhaps. – bonCodigo Jul 09 '15 at 02:58
  • @bonCodigo, I can't really visualise it - there may well be a completely different approach which avoids the problem. I was just answering the specific question (which, although possibly not widely *useful* I thought was *interesting*)! – aucuparia Jul 14 '15 at 08:51
0

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

Community
  • 1
  • 1
psychicebola
  • 939
  • 1
  • 8
  • 16