VBA - UDF treats arrays differently

Question

This code is a small subset of what I'm working on. I narrowed down the problem to the next part. I have this UDF SampleFunction, to which I need to pass Array, {3; 4} as the only argument.

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

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

Case 1:

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

And secondly, through my excel worksheet

Case 2:

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

Problem:

UDF works for case 1, i.e. calls through VBA, it gives an error #VALUEfor case 2, when I call it through an Excel worksheet.

I went through the function using F8 for case 2. Lbound(InputVar)evaluates to 1 (which is different from calling from sub in case 1, there it evaluates to 0), but it InputVar(Lbound(InputVar))shows “Subheading from Range” in case 2.

, , SampleFunction , .. 2, , 1, . , - , Lbound(InputVar) - .

:

UDF . InputVar , {x; y; z;...}, xth, yth, zth.... InputVar Variant, ( ), , ( ) .

!

Answer 1

, . -, , , SampleFunction , .. InputVar - . - , . -, , InputVar . . 'Subscript out of range.', , .

, InputVar. - . .

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

, . , , , .