CODEWITHSUNDEEP
excelvba
Janthelme
Janthelme

Reputation: 999

Passing 2D-array to VBA/UDF function from sheet

I am on Excel 2010.

When a VBA user-defined function, say make2DArray, outputs a variant representing a 2D-array, this 2D-array can be then used as an input to another VBA function, say someFunction. While calling the functions from within VBA code. It works fine.

However when doing the same, passing make2DArray output as argument to someFunction, from within the sheet (via a formula in a cell), then it seems to work only for 2D-array with **2 or more rows**. Generating 2D-array with 1 row would fail. It seems that the 1-row 2D-array is then transformed automatically into an equivalent 1D-array.

A small example :

Option Explicit

'returns last (top-most, right-most) element
Function someFunction(v As Variant) As Integer
    On Error Resume Next
        Debug.Print "Dim-1 size", UBound(v, 1) - LBound(v, 1) + 1
        Debug.Print "Dim-2 size", UBound(v, 2) - LBound(v, 2) + 1
    On Error GoTo 0

    someFunction = v(UBound(v, 1), UBound(v, 2))
End Function

Function make2DArray(h As Integer, w As Integer) As Variant
    Dim i As Integer, j  As Integer
    Dim v2d As Variant

    ReDim v2d(1 To h, 1 To w)
    For i = 1 To h
        For j = 1 To w
            v2d(i, j) = i * j
        Next j
    Next i

    make2DArray = v2d
End Function

Sub test()
    'also works when called from sheet
    Debug.Print someFunction(make2DArray(2, 3)) 'returns 6
    'doesn't work when called from sheet
    Debug.Print someFunction(make2DArray(1, 3)) 'returns 3
End Sub

The test function will work fine from within VBA. Similarly =someFunction(make2DArray(2, 3)) or any =someFunction(make2DArray(i, j)) cell formula would work well for i>1, however =someFunction(make2DArray(1, 3)) or any =someFunction(make2DArray(1, j)) will only produce a #VALUE! result in the sheet.

My question : Is this behavior documented somewhere? Is there a way to avoid the "casting" from 2D-array to 1D-array for 1-row 2D-arrays?

Upvotes: 2

Views: 782

Answers (2)

Charles Williams
Charles Williams

Reputation: 23520

UDFs should really copy with parameters being either a range, or a vector, or an array, or a scalar constant, or an array/vector output from some other function.
SomeFunction({1,2,3}) gets a 1D vector array
SomeFunction({1;2;3}) gets a 2D array
SomeFunction(Range as variant) gets a variant containing a range object whose Value2 property always returns either a scalar or a 2D array.

Upvotes: 1

user4039065
user4039065

Reputation:

From my comments:

I've never seen that before. It is like it is using transpose to convert a 2-D (one row) array into a 1-D array. I consider this a bug in that the VBA overhead is looking at a 2-D variant array with one 'row' (e.g. 1 as ubound first rank) and converting it to a 1-D array with the original ubound second rank as the ubound of the sole 1-D rank. For universality, the only suggestion I can come up with would be error control; I'll post a suggestion below.

Here is some error control to overcome the rogue 2-D to 1-D array conversion.

'returns bottom-right element of 2D-array
Function someFunction(v As Variant) 'As Integer
    On Error Resume Next
        Debug.Print "Dim-1 size", UBound(v, 1) - LBound(v, 1) + 1
        Debug.Print "Dim-2 size", UBound(v, 2) - LBound(v, 2) + 1
    On Error GoTo 0

    'Debug.Print IsArray(v)
    'Debug.Print UBound(v, 1) & ":" & UBound(v, 2)

    On Error GoTo err_1D_array
    someFunction = v(UBound(v, 1), UBound(v, 2))
    Exit Function

err_1D_array:
    someFunction = v(UBound(v))

End Function

Upvotes: 1

Related Questions