Redim Preserve an array and fill with values from controls.

[Flexcel22]

Hi there,

I am still practicing and learning array bit by bit in vba.

Now I'm stuck with Redim Preserve.

I managed to Redim preserve a two dimensional array. For example arr( 1 To 10, 1 To 4) now I redim preserve it (1 To 10, 1 To 6).

My problem is I dont know now how to refill the new columns in an array with values. I tried with the little knowledge I had but ended up overwriting the preserve values

I would like an example using values from controls e.g comboboxes. But from range as well is highly appreciated.

Thanks in advance

 

[John_w]

Use a variable to hold the size of the 2nd dimension and then you know where to start looping from when adding values to the ReDim'ed array. Here's a simple example:

Public Sub Array_Test()

    Dim arrSize As Long
    Dim i As Long
    
    ReDim arr(1 To 10, 1 To 4) As Variant
    
    For i = 1 To UBound(arr, 2)
        arr(1, i) = i
        arr(2, i) = i
        arr(3, i) = i
    Next
    arrSize = UBound(arr, 2)
    For i = 1 To UBound(arr, 2)
        Debug.Print i, arr(1, i), arr(2, i), arr(3, i)
    Next
    Stop
    
    ReDim Preserve arr(1 To 10, 1 To 6)
    
    For i = arrSize + 1 To UBound(arr, 2)
        arr(1, i) = i
        arr(2, i) = i
        arr(3, i) = i
    Next
    arrSize = UBound(arr, 2)
    For i = 1 To UBound(arr, 2)
        Debug.Print i, arr(1, i), arr(2, i), arr(3, i)
    Next
    
End Sub

 

[Flexcel22]

Thanks John _w

I will give ur code a try and revert back to you with my feedback.

Cheers ??

 

[Flexcel22]

Hi John_w

As I'm still learning vba, i would be very much appreciate if you could help me out by commenting each line of code.

Hopefully myself and whoever encountering the same will had a chance to learn as well.

Thanks in advance.

 

[John_w]

See if this helps to explain the code.

Public Sub Array_Test2()

    Dim arrSize As Long 'variable to hold size of second array dimension
    Dim i As Long
    
    'Resize dynamic 2-dimensional array of Variants.  Variant is specified so that the array can hold different data types, though only Longs are stored in this demo.
    'First dimension is 1 to 10.  Second dimension is 1 to 4 and this will be increased later in the code.
    'Strictly speaking, the array should have been first declared using:
    'Dim arr() As Variant
    'but this isn't necessary
    
    ReDim arr(1 To 10, 1 To 4) As Variant
    
    'Fill array with values.  Note - only elements 1, 2 and 3 of the first dimension are specified in this demo, not the whole 10 elements.
    'UBound gives largest subscript of the array's specified array dimension.  In this case dimension 2, so it returns 4
    
    For i = 1 To UBound(arr, 2)
        arr(1, i) = i
        arr(2, i) = i
        arr(3, i) = i
    Next
    
    'Save size of array's second dimension.  arrSize should be 4
    
    arrSize = UBound(arr, 2)
    
    'Debug.Print outputs values to the VBA Immediate Window (press Ctrl+G to view this window).  Again, only elements 1, 2 and 3 of the first dimension are ouput in this demo.
    
    For i = 1 To UBound(arr, 2)
        Debug.Print i, arr(1, i), arr(2, i), arr(3, i)
    Next
    
    'Stop code execution to see the array values.  Press F5 key to resume execution
    Stop
    
    'Resize array's second dimension to 1 to 6 and preserve existing values.  With Preserve, we can resize only the last array dimension
    
    ReDim Preserve arr(1 To 10, 1 To 6)
    
    'After this, UBound(arr, 2) is 6
    'Fill array's new elements with values.  The For loop ranges from 5 to 6, so only those elements are filled, leaving elements 1 to 4 unaffected.  Again, only first 3 elements of the first dimension are filled in this demo
    
    For i = arrSize + 1 To UBound(arr, 2)
        arr(1, i) = i
        arr(2, i) = i
        arr(3, i) = i
    Next
    
    'Save size of array's second dimension.  arrSize should be 6
    
    arrSize = UBound(arr, 2)
    
    'Output array's values again
    
    For i = 1 To UBound(arr, 2)
        Debug.Print i, arr(1, i), arr(2, i), arr(3, i)
    Next
    
End Sub

These pages give detailed information about the ReDim and UBound keywords used in the code.

ReDim statement (VBA)

Office VBA reference topic

docs.microsoft.com

UBound function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Compare with:

LBound function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com