Using a For Loop to Create New Arrays in VBA

Question

long time listener, first time caller
I need to create arrays where the dimensions of each array and the number of arrays is determined by user input. This means that each time the code is executed, there will be a varying amount of arrays created and each array could have varying dimensions. Is there a way to create a for loop that will create these new arrays for me in VBA?

For example, lets say the user indicates the need to create 3 arrays. They then indicate Array 1 should be 5 x 6, array 2 should be 6 x 6, and array 3 should be 6 x 3. I am trying to find a way to create a for loop that will perform the following without me needing to actually create each array:

Dim W1() As Double 
    ReDim W1(5,6)
Dim W2() As Double 
    ReDim W1(6,6)
Dim W3() As Double 
    ReDim W1(6,3)

Something like the code below is what i had in mind.


'UserInput1 is the number of arrays needed

'xDim(i) and yDim(i) are two arrays whose length is equal to UserInput1

'each value in the xDim and yDim arrays represents the X or Y dimension of the
'new array to be created

'i want to create new arrays W1, W2, ...Wn, but using the code W & i() does
'not work

For i = 1 to UserInput1
    Dim W & i() As Double
        ReDim W & i(xDim(i), yDim(i))
Next i

The code above of course results in an error

"Compile error: expected: end of statement"

Is there a solution to my problem in VBA or is there some other work around I'm not thinking of?

Any help is greatly appreciated! This is my first question asked so please let me know if I need to share any other information.

Not a big deal, but best probably better to use `Long` instead of `Integer`. See discussion here: https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/51689021#51689021 — pgSystemTester, Jul 12 '19 at 02:58

score 1 · Accepted Answer · edited Oct 13 '19 at 19:49

You could also build an array within an array. I am not sure exactly what you have as available variables, but this shows how such a process would work. A UDF simplifies the process.

Sub SubName()
    ReDim bigArray(1 To 3) As Variant


    bigArray(1) = eachARR(0, 4)
    bigArray(2) = eachARR(5, 99)
    bigArray(3) = eachARR(20, 88)


    Debug.Print UBound(bigArray) 'returns 3
    Debug.Print UBound(bigArray(1), 1) 'returns 0
    Debug.Print UBound(bigArray(2), 1) 'returns 5
    Debug.Print UBound(bigArray(2), 2) 'returns 99
    Debug.Print UBound(bigArray(3), 2) 'returns 88

End Sub


Private Function eachARR(xInput As Long, yInput As Long) As Variant
     ReDim bRay(0 To xInput, 0 To yInput) As Variant

     eachARR = bRay


End Function

Updated. I think the below code is a more dynamic example of what you're trying to do. I assume the ydim and xdim are functions?

Sub BetterExample()
    ReDim bigArray(1 To UserINput) As Variant

    Dim r As Long
    For r = 1 To UserINput
        bigArray(r) = eachARR(xdim(r), ydim(r))
    Next r


End Sub


Private Function eachARR(xInput As Long, yInput As Long) As Variant
    ReDim bRay(0 To xInput, 0 To yInput) As Variant

    eachARR = bRay

End Function


Private Function xdim(x As Long) As Long
    'not sure what this is so I just made it itself
    xdim = x + 1

End Function

Private Function ydim(y As Long) As Long
    ydim = y + 2

End Function

Modified it a bit, but it works wonderfully now! Thank you so much for taking the time to respond! greatly appreciated — RiesTrout, Jul 21 '19 at 00:14

score 0 · Answer 2 · answered Jul 12 '19 at 02:15

One way to accomplish this would be to use a 3-dimensional array. Using this method your totalArray Dim (plus ReDim) statement occurs only once regardless of the number of arrays desired. The first array dimension represents the array#. The second and third dimensions represent the x & y dimensions in each array, respectively. In order to access the array values, you will need to use three nested for-loops, one for each dimension in totalArray. Below is a sub that populates each item in each array and debug.prints them. Hope this helps.

Public Sub makeManyArrays()
Dim numOfArrays As Integer
    numOfArrays = 3

Dim xDim() As Double
    ReDim xDim(1 To numOfArrays)
    xDim(1) = 5
    xDim(2) = 6
    xDim(3) = 6
Dim yDim() As Double
    ReDim yDim(1 To numOfArrays)
    yDim(1) = 6
    yDim(2) = 6
    yDim(3) = 3

Dim totalArray() As Double      'only 1 dim statement needed
    ReDim totalArray(1 To numOfArrays, 1 To WorksheetFunction.Max(xDim), 1 To WorksheetFunction.Max(yDim))  'There will be empty values in your array

Dim arrayNum As Integer 'Loop variable
Dim x As Integer                    'Loop variable
Dim y As Integer                    'Loop variable

For arrayNum = 1 To numOfArrays     'Loop through each array
    'You didn't specify what you wanted to do, but I put the x*y value in each cell of each array just for fun.
    For x = 1 To xDim(arrayNum)
        For y = 1 To yDim(arrayNum)
            totalArray(arrayNum, x, y) = x * y
            Debug.Print "Array " & arrayNum & " value at " & x & ", " & y & " is: " & totalArray(arrayNum, x, y)
        Next y
    Next x
Next arrayNum End Sub

Shillington · Answer 3 · 2022-02-01T17:19:03.880

My original answer is below. I've found it impossible to maintain values for this array going forward. Instead I recommend that you create a helper function which generates an array as the output.

You can create a new array in each loop using the following structure:

Sub DynamicArrays()
    dim col as new Collection
    dim a() as Variant
    dim n as Integer

    for n = 1 to 10
        a = Array()
        redim a(1 to n)
        col.Add a
    next
End Sub

If you hit a breakpoint on the End Sub line and run, you'll see in the immediate window that the collection contains 10 separate arrays of different lengths.

Using a For Loop to Create New Arrays in VBA

3 Answers3