How can set the Transpose function base index to 0 in Excel VBA?

Question

In my VBA code I use the Transpose function of WorksheetFunction Class. What I noticed that even if the Option Base of the Application is the default 0 value, the WorksheetFunction.Transpose() returns the array indexed from 1.

This is not causing any troubles if it is used on an Excel sheet, or in VBA with ranges, but a little bit surprising when the function is applied on an array of any type. Now I reindex the values in a new array to be compatible for other arrays in the program.

Is there a parameter or a setting which sets the array index base value to 0, or to the Option Base value of the Application resulted from the WorksheetFunction.Transpose?

This is the test:

Sub arrtra()
    Dim arr(5, 2)
    Dim res As Variant
    For i = 0 To 5
        For j = 0 To 2
            arr(i, j) = Rnd()
        Next j
    Next i
    res = arr
    tra = tes(res)
    Debug.Print "Ubound="; UBound(arr, 1), "Ubound="; UBound(tra, 2), arr(0, 0), tra(1, 1)

End Sub

Function tes(vmi As Variant) As Variant

    tes = WorksheetFunction.Transpose(vmi)

End Function

This is the printed result of the test:

Ubound= 5     Ubound= 6      0,7055475     0,705547511577606 

Answer 1

• a) As BigBen mentioned, you won't change the 1-based result via WorksheetFunction.Transpose or evaluation
• b) You can change, however the logical procedure considering that 1) a Listbox object is zerobased by default, but accepts other boundaries as well 2) its .Column property is able to return any content input (input via .List property) to a zero-based transposed output.

Eventually you might profit from the fact that this all can be done in memory - see TransposeZerobased(arr) function :-)

Function TransposeZerobased(arr)
'Purp:  transpose a 2D array into a zerobased one
'Note:  expects a 2D array input into a ListBox referred to in memory
transposed data
    With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}") ' listbox only in memory
        .List = arr                     ' assign array to list property
        TransposeZerobased = .Column    ' transpose via Column property
    End With

End Function

Sub FillRndArr(arr)
    Dim i As Long
    For i = LBound(arr, 1) To UBound(arr, 1)
        Dim j As Long
        For j = LBound(arr, 2) To UBound(arr, 2)
            arr(i, j) = Rnd()
        Next j
    Next i
End Sub

Example Call

Sub ExampleCall()
    Dim arr(0 To 5, 0 To 2)
    FillRndArr arr                      ' fill 2D array with randomized content
    Dim tra
    tra = TransposeZerobased(arr)       ' << execute transposition
End Sub

Additional hint

As an alternative you might want to benefit from Win API via CopyMemory (c.f. @MacroMarc's 2018 post return a 0-base array from ws.UsedRange.Value) keeping in mind a possible update for an elder API system declaration before VBA7/Office 2010, e.g. via conditional compilation:

#If Mac Then
    Private Declare PtrSafe Function CopyMemory_byVar Lib "libc.dylib" Alias "memmove" (ByRef dest As Any, ByRef src As Any, ByVal size As Long) As LongPtr
#Else
    #If VBA7 Then
        Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
               (ByRef destination As Any, ByRef SOURCE As Any, ByVal Length As LongPtr)
    #Else
        Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
                                      (ByRef destination As Any, ByRef SOURCE As Any, ByVal Length As Long)
    #End If
#End If

Note that the length argument for the recent versions (2010+) would need a conversion to a LongPtr within the posted function, as well.

C.f. Some hints to API & compiled compilation