May I return a 0-base array from ws.usedrange.value?

Question

I like how efficient this function is. Unfortunately I wish the function returned a zero-based array! Any suggestions? I've attempted Option Base 0 already (although that is the default).

Function getWSarr(pWs As Worksheet) As Variant
    getWSarr = pWs.UsedRange.Value
End Function

Answer 1

I'd say simply looping the values into a zero-based array is safest & simplest.

You could however fool around with some memory-copy stuff from WinAPI:

Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Public Function MultiDimOneToZeroArray(ByVal s As Variant) As Variant
    'Do your own check first that s is a one-based array etc
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim arr() As Variant
    ReDim arr(0 To UBound(s) - 1, 0 To UBound(s, 2) - 1)

    CopyMemory ByVal VarPtr(arr(0, 0)), ByVal VarPtr(s(1, 1)), UBound(s) * UBound(s, 2) * 16

    MultiDimOneToZeroArray = arr
End Function

Call it like so:

Sub test()
    Dim s() As Variant
    s = Sheet1.Range("A1:E20").Value2

    Dim arr As Variant
    arr = MultiDimOneToZeroArray(s)

End Sub

Obviously you can wrap your func so:

Function getZeroBasedWSarr(pWs As Worksheet) As Variant
    getZeroBasedWSarr = MultiDimOneToZeroArray(pWs.UsedRange.Value)
End Function

Answer 2

It cannot be done. Retrieving values into an variant array from the worksheet's cells always returns a 1-based 2-D array regardless of whether you are dealing with a single column or single row or multiple columns and/or rows.

Option Base 0 (which is the default in any case) cannot change this behavior.

Caveat: Application.Transpose applied once or twice can return a 1-D zero-based array of a single column or single row.

Option 1: Simply convert the array on the fly

dim arr1 as variant, arr2 as variant, i as long

'for multiple row values in a single column
arr1 = range("a1:a9").value
redim arr2(lbound(arr1, 1) - 1)

for i = lbound(arr1, 1) to ubound(arr1, 1)
    arr2(i-1) = arr1(i, 1)
next i

for i=lbound(arr2) to ubound(arr2)
    debug.print i
    debug.print arr2(i)
next i

'for multiple column values in a single row
arr1 = range("a1:i1").value
redim arr2(lbound(arr1, 2) - 1)

for i = lbound(arr1, 2) to ubound(arr1, 2)
    arr2(i-1) = arr1(i, 2)
next i

for i=lbound(arr2) to ubound(arr2)
    debug.print i
    debug.print arr2(i)
next i

Option 2: Transpose the values as they are received

dim arr as variant

arr = application.transpose(range("a1:a9").value)

for i=lbound(arr) to ubound(arr)
    debug.print i
    debug.print arr(i)
next i

arr = application.transpose(application.transpose(range("a1:i1).value))

for i=lbound(arr) to ubound(arr)
    debug.print i
    debug.print arr(i)
next i

Note that in Option 2 you only transpose once when converting a single column's rows into a 1-D array. However, you need to transpose twice to convert a single row's columns into a 1-D array.

Transpose has a functional limit of either a signed or unsigned integer's overflow limit (I cannot remember which at the moment).