VBA - UDF handles Arrays differently

Question

This code is a small subset of what I am working on. I have narrowed the problem down to the following piece. I have this UDF SampleFunction, to which I need to pass an Array, {3;4} as the sole argument.

Function SampleFunction(InputVar As Variant) As Integer
    SampleFunction = InputVar(LBound(InputVar))
End Function

I call this UDF in two different ways. First, through VBA

Case 1:

Sub testSF()
    MsgBox SampleFunction(Array(3, 4))
End Sub

And secondly, through my excel worksheet as

Case 2:

={SampleFunction(ROW(3:4))} -> i.e. as an array function.

The Problem:

The UDF works for Case 1, i.e. the call through VBA, it gives a #VALUE error for Case 2, when I call it through the excel worksheet.

I stepped through the function using F8 for Case 2. Lbound(InputVar) evaluates to 1 (which is different from calling from the sub in Case 1, there it evaluates to 0), yet InputVar(Lbound(InputVar)) shows the "Subscript out of Range" error in Case 2.

All I want to know is how to call SampleFunction function from a worksheet, i.e. Case 2 so that it has the same behaviour as Case 1 shown above. As a bonus, it would be nice if someone could explain why Lbound(InputVar) evaluates differently in the above cases.

Some Other Details:

I am building a UDF to perform some regex manipulations. The argument InputVar above, will be an array {x;y;z;...} specifying the xth, yth, zth ... occurences. The data type of InputVar is kept to Variant because I want to be able to pass either numbers (as a one length array), arrays, or ranges (taken in and converted to array) to the function.

Thanks in advance!!

Answer 1

I believe you have two problems. First, I don't think your code will evaluate if you use SampleFunction in a non-array formula, i.e., if InputVar is a Range. You need to incorporate some way of dealing with different types of input that can be passed into the variant. Second, your code assumes the InputVar is a one-dimensional array. This will lead to an error for any multi-dimensional array. This is the source of the 'Subscript out of range.' error, because array functions pass all array arguments as two-dimensional arrays even if they are can be represented as one-dimensional.

I would suggest declaring a new dynamic array in your function and then setting it equal to InputVar. In the past I've made this work for array and non-array formulas with something like below. Also, note the change to how the first item from the array is retrieved.

Option Explicit
Function SampleFunc(InputVar As Variant) As Integer

Dim tmpArray() As Variant

On Error GoTo ErrHandler
tmpArray = InputVar

'Added extra argument to LBound since dynamic arrays have two dimensions by default.
SampleFunc = tmpArray(LBound(tmpArray, 1), LBound(tmpArray, 2))
Exit Function

ErrHandler:
'Handles the case where InputVar is a Range.
tmpArray = InputVar.Value
Resume Next

End Function

This is quick and dirty, esp. the error handling, but hopefully the basic idea is helpful.