Issue with Redim Preserve (2D array)

Question

Yet another topic with multidimensional array and Redim Preserve, I know. I read a lot of them but still can not understand why my code is not working.

I kwow that you can only extend the last dimension and it is what I want: add a new column to my 2D array.

In order to isolate the issue, I test 2 code:

Sub test_Redim_Preserve()

Dim arr() As Variant

ReDim arr(10, 10)
ReDim Preserve arr(UBound(arr,1), UBound(arr,2) + 1)

End Sub

This work fine

Sub test_Redim_Preserve2()

Dim arr() As Variant

ReDim arr(10, 10)
arr = Range("A1:J10")
ReDim Preserve arr(UBound(arr,1), UBound(arr,2) + 1)

End Sub

This give me an error. I just gave a range to populate my array and then I can´t Redim it. I don´t understand what is missing for it to accept the Redim.

Could someone explain me?

Answer 1

The default lower bound, in the absence of an Option Base statement to the contrary, is 0 but you assign a range to an array, it always has a lower bound of 1, so your code is actually trying to resize the first dimension of the array too by altering its lower bound. Use:

ReDim Preserve arr(1 To UBound(arr, 1), 1 To UBound(arr, 2) + 1)

Answer 2

You can use Option Base 1 at the top of your code, and then your original code will work fine.

Full code:

Option Base 1

Sub test_Redim_Preserve2()

Dim arr() As Variant

ReDim arr(10, 10)
arr = Range("A1:J10")
ReDim Preserve arr(UBound(arr, 1), UBound(arr, 2) + 1)

End Sub