Vba Array subscript out of range

[sysuserwindows]

In the code below, it appears an error message "subscript out of range" in the line "ReDim Preserve intA(0 To 3, 0 To 4)"

Sub Populate2D()
    'declare the 2D array
    Dim intA() As Variant
    'declare variables
    Dim rw As Integer
    Dim col As Integer
  
    'initialize the array with 3 rows and 4 columns
    ReDim intA(0 To 2, 0 To 3)
  
    'populate the array
    intA(0, 0) = 45
    intA(0, 1) = 50
    intA(0, 2) = 55
    intA(0, 3) = 60
    intA(1, 0) = 65
    intA(1, 1) = 70
    intA(1, 2) = 75
    intA(1, 3) = 80
    intA(2, 0) = 85
    intA(2, 1) = 90
    intA(2, 2) = 95
    intA(2, 3) = 100
  
    'redimension the array to 4 rows and 5 columns
    ReDim Preserve intA(0 To 3, 0 To 4)
  
    'populate the additional column with values for all four rows
    intA(3, 4) = 45
    intA(3, 4) = 50
    intA(3, 4) = 55
    intA(3, 4) = 60
  
    'loop through the array and populate Excel
    For rw = 0 To 3
        For col = 0 To 4
            Worksheets("Sheet2").Cells(rw + 1, col + 1).Value = intA(rw, col)
        Next col
    Next rw
End Sub

 

[johnnyL]

You can't change the row count, first dimension, of an array like that. You can only change the column count, second dimension, like that.

 

[Peter_SSs]

.. more specifically, when you ReDim Preserve it is only the last dimension that you can change. So if an array has 2 dimensions like yours then you can only change the second dimension but if the array had 5 dimensions then only the 5th dimension could be changed.

 

[sysuserwindows]

You can't change the row count, first dimension, of an array like that. You can only change the column count, second dimension, like that.

Thank you johnnyL for your reply, how to add new row in this array?

 

[johnnyL]

Thank you johnnyL for your reply, how to add new row in this array?

Short answer is, you can't.

A work around to the limitation is to copy data from the array to another array dimensioned to the size that you want. The array that you copy the data to may or may not (your choice) be the same name.

 

[sysuserwindows]

How to initialize the array with 3 columns and dynamic rows

 

[johnnyL]

Here is some info that should get you going.

The following is a function that I have to do what you are wanting to do. Once you add the function to your module of code, It will do what you are trying to do practically instantaneously.

Function code to add to the module:

Public Function ReDimPreserve(ArrayNameToResize, NewRowUbound, NewColumnUbound)
'
' Code inspired by Control Freak
'
' Preserve Original data & LBounds & Redim both dimensions for a 2D array
'
' example usage of the function:
' ResizedArrayName = ReDimPreserve(ArrayNameToResize,NewRowSize,NewColumnSize)
' ie.
' InputArray = ReDimPreserve(InputArray,10,20)
'
' This function will keep the LBounds (Lower Bounds) of the original array.
'
    Dim NewColumn                   As Long, NewRow                      As Long
    Dim OldColumnLbound             As Long, OldRowLbound                As Long
    Dim OldColumnUbound             As Long, OldRowUbound                As Long
    Dim NewResizedArray()           As Variant
'
    ReDimPreserve = False
'
    If IsArray(ArrayNameToResize) Then                                                                      ' If the variable is an array then ...
           OldRowLbound = LBound(ArrayNameToResize, 1)                                                      '   Save the original row Lbound to OldRowLbound
        OldColumnLbound = LBound(ArrayNameToResize, 2)                                                      '   Save the original column Lbound to OldColumnLbound
'
        ReDim NewResizedArray(OldRowLbound To NewRowUbound, OldColumnLbound To NewColumnUbound)             '   Create a New 2D Array with same Lbounds as the original array
'
        OldRowUbound = UBound(ArrayNameToResize, 1)                                                         '   Save row Ubound of original array
        OldColumnUbound = UBound(ArrayNameToResize, 2)                                                      '   Save column Ubound of original array
'
        For NewRow = OldRowLbound To NewRowUbound                                                           '   Loop through rows of original array
            For NewColumn = OldColumnLbound To NewColumnUbound                                              '       Loop through columns of original array
                If OldRowUbound >= NewRow And OldColumnUbound >= NewColumn Then                             '           If more data to copy then ...
                    NewResizedArray(NewRow, NewColumn) = ArrayNameToResize(NewRow, NewColumn)               '               Append rows/columns to NewResizedArray
                End If
            Next                                                                                            '       Loop back
        Next                                                                                                '   Loop back
'
        Erase ArrayNameToResize                                                                             '   Free up the memory the Original array was taking
'
        If IsArray(NewResizedArray) Then ReDimPreserve = NewResizedArray
    End If
End Function

After you do that, you only have to change your line of code:
From:

    'redimension the array to 4 rows and 5 columns
    ReDim Preserve intA(0 To 3, 0 To 4)

to:

    'redimension the array to 4 rows and 5 columns
    intA = ReDimPreserve(intA,3,4)

Let us know if you still have questions. You can use variable values in that if you want to make it dynamic.

 

[Alex Blakenburg]

How to initialize the array with 3 columns and dynamic rows

In your original post you were redimensioning BOTH columns and rows, post 6 is just redimensioning rows.
If you are only redimension rows then you could just swap the rows and columns, then write it back to the spreadsheet using the worksheetfunction transpose (can only handle up to 65,536 rows though) no loop required.

Using your code the below is how it would look like swapping the rows and columns.

If you want to give us more details on what you are trying to do, we can probably give you some more options.

Sub Populate2D()
    'declare the 2D array
    Dim intA() As Variant
    'declare variables
    Dim rw As Integer
    Dim col As Integer
  
    'initialize the array with 4 columns and 3 rows - XXX Swapped rows and columns
    ReDim intA(0 To 3, 0 To 2)
  
    'populate the array - XXX Swapped Rows and columns
    intA(0, 0) = 45
    intA(1, 0) = 50
    intA(2, 0) = 55
    intA(3, 0) = 60
    intA(0, 1) = 65
    intA(1, 1) = 70
    intA(2, 1) = 75
    intA(3, 1) = 80
    intA(0, 2) = 85
    intA(1, 2) = 90
    intA(2, 2) = 95
    intA(3, 2) = 100
  
    'redimension the array the last dimension only
    ReDim Preserve intA(0 To 3, 0 To 3)
  
    'XXX Load additional row as a column
    intA(0, 3) = 45
    intA(1, 3) = 50
    intA(2, 3) = 55
    intA(3, 3) = 60

    ' Populate Excel
    Worksheets("Sheet2").Cells(1, 1).Resize(UBound(intA, 2) + 1, UBound(intA, 1) + 1).Value = Application.Transpose(intA)

End Sub