excel - 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

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).