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

Question

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?

To avoid confusion you may want to swap your w and h. A 2-D array passed back to the worksheet regards the first rank as the number of rows and the second rank as the number of columns. — user4039065
– user4039065, Commented Dec 8, 2017 at 1:39
Just tested your =someFunction(make2DArray(2, 4)) and it returns 8 which is the value at the ubound of both ranks. — user4039065
– user4039065, Commented Dec 8, 2017 at 1:42
Yes, as the output of the make2DArray(1,4) is passed back to Excel, Excel converts it to a 1D array, and then passes that 1D array to the someFunction function. It is always a bad idea to use the Excel-modified result of a UDF as a parameter to a UDF. Instead pass h and w to someFunction itself, and have someFunction invoke make2DArray. (I.e. call it as =someFunction(1, 4).) — YowE3K
– YowE3K, Commented Dec 8, 2017 at 2:32
tbh, 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. — user4039065
– user4039065, Commented Dec 8, 2017 at 2:39
fwiw THANK YOU! for asking an intelligent and well documented question about programming. It seems that nine out of ten inquiries made to the excel tag lately have been nothing more than software specifications with a lame 'I'm a noob and don't know how to do this' narrative. — user4039065
– user4039065, Commented Dec 8, 2017 at 3:07

user4039065 · Accepted Answer · 2017-12-08 02:44:28Z

1

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

answered Dec 8, 2017 at 2:44

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

user4039065 Over a year ago

I'm actually curious as to whether this bug may be more CPU efficient than a typical application.transpose function that reduces a 2-D array to a 1-D array when the first rank's ubound is 1. For that matter, it may also be more efficient when the second rank's ubound is one versus the standard application.transpose(application.transpose(v)).

Charles Williams Over a year ago

Transpose is quite an efficient function but at best it does involve creating a copy of the array and therefore this costs. At worst it involves slow round-trips between Excel and VBA - I have never found a case where transpose is faster than accessing the arrays directly (but such a case probably exists!)

Janthelme Over a year ago

I agree that the error control does work and help circumventing the array size issue. Before accepting this answer, I'll wait a bit longer in case someone knows if this behavior is documented somewhere (I'd like to understand if there is a reason for it, or if it is just a "bug")

Charles Williams · Accepted Answer · 2017-12-08 11:25:14Z

1

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.

answered Dec 8, 2017 at 11:25

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related