Handling array declaration when the number of arrays required is unknown

[switters_aka]

Hello,

I am trying to write a piece of code that populates arrays inside a loop. The problem I can't think of solution for is that every time the code runs the number of arrays will be different. This presents a problem for array declaration so I thought it might be possible to declare the array within the loop using SubArrayi where i is the loop variable. E.g.

For i = 1 to UBound(MainArray)
 Dim SubArray & i as Variant
'populate SubArrayi
Next i

Dim SubArray & i is not acceptable VBA syntax though and it might not even be possible to declare the array in such a manner so if someone could please help me handle array declaration where the number of arrays is initially unknown, I would really appreciate it.

Thanks to anyone who can offer any help.

 

[RoryA]

Your question isn't really clear to me but it sounds as though you need Redim, probably with Preserve.

 

[pgc01]

Hi

Please post more info, like a working example, that we can use to do some testing.

There are solutions, like declaring the arrays dynamically adding a dimension, or using a jagged array, or an object like a dictionary or a collecton, but only with more information can we know it it makes sense.

 

[switters_aka]

Sorry, let me try and make it clearer. I am creating arrays from a database and depending on the filter criteria I use the number of arrays required will be different each time. On some occasions I will require 30 arrays and sometimes only 5. So I need some day of dynamically declaring and naming the arrays.

I hope that helps make it clearer.

 

[pgc01]

Sorry, let me try and make it clearer. I am creating arrays from a database and depending on the filter criteria I use the number of arrays required will be different each time. On some occasions I will require 30 arrays and sometimes only 5. So I need some day of dynamically declaring and naming the arrays.

I hope that helps make it clearer.

Are all the arrays unidimensional and do they have the same number of elements?

For ex.,

- the first time you read from the database you must create 30 arrays of 10 elements each

- the second time you must create 5 arrays of 20 elements each

etc., each time you must create a variable number of unidimensional arrays but all the arrays have the same number of elements.

If that's true you can create the arrays with ReDim, like Rory said.

Please confirm.

 

[switters_aka]

thanks for the response. No, I am afraid that won't work because each array will have a variable number of elements.

Thanks anyway. I will try and figure out another approach.

 

[chuckchuckit]

Why not just set the array size to the largest possible size you will be needing it for. Then before each next use of the array, just zero out the whole array even if you did not use it all. I use loops for that.

Sounds like the array will not use much memory.

I use arrays for storing different query data, flags, etc and the sizes change all the time. So I just declare bigger than the the max size I'll use (to be sure I don't overmax), and clear the whole array even if it doesn't need it, before each time I reload the array with the next set of data.

I even tested the speed of the loops I use to clear them and since arrays are only using memory they are extremely fast.

There are some tricky things about arrays though, like you can't directly copy one array to the next unless you do one element at a time (if I remember correct on that).

But I even use arrays sometimes in place of cell data for manipulating data because they are faster than worksheet cells to bounce around data. Then write final answer to the cells themselves.

Hope that might be of some use.

 

[switters_aka]

thanks for the input ChuckChukit. I think my initial explanation about what I was trying to do was pretty rubbish so apologies to all for that. I think I am on my way to finding another solution but thanks all for your help and suggestions.

 

[chuckchuckit]

No problem. Thanks for the answer back.

 

[pgc01]

No, I am afraid that won't work because each array will have a variable number of elements.

In that case you can, for ex., can use a jagged array (an array in which each element is itself an array).

This is an example for a jagged array with three arrays, each array with a variable number of elements. I chose to have arrays with lower bound 1.

Insert a new module, paste and execute this code:

Option Explicit
Option Base 1
 
' create a jagged array with 3 arrays
' the first array with 3 elements, the second array with 4 elements and the third array with 2 elements
Sub Test()
Dim vJaggedArr As Variant
Dim vTmpArr As Variant
 
' create the jagged array
ReDim vJaggedArr(1 To 3)
vJaggedArr(1) = Array("a", "b", "c")
vJaggedArr(2) = Array(11, 22, 33, 44)
vJaggedArr(3) = Array(True, False)
 
' display some values, to test if it's ok
MsgBox vJaggedArr(1)(3) & ", " & vJaggedArr(2)(4) & ", " & vJaggedArr(3)(1)
 
End Sub