JavaEar 专注于收集分享传播有价值的技术资料

Issue with Redim Preserve (2D array)

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?

2个回答

    最佳答案
  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)
    
  2. 参考答案2
  3. 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