VBA Error with Redim Preserve and Application.Transpose changing array base

Question

I have a 2 dimensional array. I wanted to make a redim preserve on it's first dimension but I know that redim preseve works only on the last dimension. I tried to use transpose function, but it seems that the transpose is changing my array from base 0 to base 1, is it normal ? if yes how to set it back to base 0 ? or how to solve my problem to preserve my array while changing it's first dimension ? I want to add 2 elements to my array

here is a part of my code where I get the problem :

    table3 = Application.Transpose(table3)
    ReDim Preserve table3(Ubound(table3,1), Ubound(table3,2) +2)
    table3 = Application.Transpose(table3)

I noticed that before the Transpose the array is base 0 and after the transpose it's base 1, and I think that's the main problem. I don't want to change the base from 0 to 1 because I use the same array elsewhere in my code and I don't want to change the whole code.

The following line of code will give me an error

“Subscript Out of Range”

ReDim Preserve table3(Ubound(table3,1), Ubound(table3,2) +2)

if I change it by the following line

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

it will work but my array will become a based 1 array which not what I want, I want to keep my indexes starts from 0 not from 1

BEFORE TRANSPOSE

AFTER TRANSPOSE

`ReDim Preserve table3(0 To UBound(table3, 1), UBound(table3, 2) + 1)` — Ricardo A, Mar 20 '19 at 19:07
I already tried it before i post and it fails. I tried also 0 to ubound(table3,1)-1 didn't work. — JustGreat, Mar 20 '19 at 19:36
You can write a small utility function to transpose an array without changing its lower bounds... You don't need to use `Application.Transpose` Or write a function to directly perform the resizing without any transpose. https://stackoverflow.com/questions/13183775/excel-vba-how-to-redim-a-2d-array — Tim Williams, Mar 20 '19 at 19:47
@user11217663 - Transpose does change the bounds (if the original lower bound is not 1) The output lower bound is always 1 — Tim Williams, Mar 20 '19 at 19:56
What does matter the way the Array is first populated? Can't see what it has to do with my problem. — JustGreat, Mar 20 '19 at 20:48
Regarding what you are saying you confirm that transpose change the base from 0 to 1 right? And the only why to solve my problem to create my own utility? By the way i never said i want the transpose, i said i used the transpose because the redim préserve does work on the last dimension only. — JustGreat, Mar 20 '19 at 20:53

Guest · Accepted Answer · 2019-08-22T14:11:47.760

Perhaps this would help you:

'********************************************************************************************************************
' To re-dimension the first dimension of a two-dimension array without getting Excel errors
' Also possible to re-dimension the second dimension
' Usage: myArray = reDimPreserve(myArray, UBound(myArray, 1) + x, UBound(myArray, 2) + y)
' Where x and y are the increments to get to the desired new dimensions
' Returns an empty array if there was an error
'********************************************************************************************************************
Public Function reDimPreserve(ByVal aArray As Variant, ByVal newFirstUBound As Long, ByVal newLastUBound As Long) As Variant
Dim tmpArr As Variant, nOldFirstUBound As Long, nOldLastUBound As Long, nFirst As Long, nLast As Long

If Not IsArray(aArray) Then
    reDimPreserve = Array(Empty)
ElseIf newFirstUBound < UBound(aArray, 1) Or newLastUBound < UBound(aArray, 2) Then
    reDimPreserve = Array(Empty)
Else
    ReDim tmpArr(newFirstUBound, newLastUBound)
    nOldFirstUBound = UBound(aArray, 1)
    nOldLastUBound = UBound(aArray, 2)
    For nFirst = LBound(aArray, 1) To newFirstUBound
        For nLast = LBound(aArray, 2) To newLastUBound
            If nOldFirstUBound >= nFirst And nOldLastUBound >= nLast Then
                tmpArr(nFirst, nLast) = aArray(nFirst, nLast)
            End If
        Next nLast
    Next nFirst
    reDimPreserve = tmpArr
    Erase tmpArr
End If

End Function

VBA Error with Redim Preserve and Application.Transpose changing array base

1 Answers1

Linked