In part 1 and part 2 of “Writing efficient UDFs” I used parameters defined as Range to get data from Excel.

`Function VINTERPOLATEB(Lookup_Value As Variant, Table_Array As Range, Col_Num As Long)`

This works OK if the function is called from a formula using a range:

`=VINTERPOLATEB($E5,$A$10:$C$10200,2)`

but results in #Value if you use a calculated expression or an array of constants:

`{=VINTERPOLATEB($E5,($A$10:$C$10200*1),2)}`

This formula has to be entered as an array formula using Control/Shift/Enter (don’t enter the { … } , Excel will add them).

`=VINTERPOLATEB(4.5,{1,3,3.5;4,4,4.5;5,4.5,5},2)`

This uses a 3 column 3 row array constant. You do have to enter the { … } surrounding the constants, but it does NOT have to be entered as an array formula. The , separates the columns and the ; separates the rows.

Excel detects that these parameters are not Ranges before even calling the function.

You can fix this by defining the parameter as a variant rather than a range: a variant parameter can hold virtually anything! But the UDF now has to handle all the different types of data that the Variant might contain.

One simple approach is to assign the parameter to a Variant: this will coerce everything to values:

Function TestFunc(theParameter As Variant) Dim vArr As Variant vArr = theParameter TestFunc = vArr End Function

`=TestFunc($A$10:$A$15*1)`

In the VBE putting a breakpoint (use F9) on the return line and showing the Locals window results in this: you can see that Varr contains Error 2015 which is #Value

Thats because I forgot to array-enter the formula, here is the Locals for the array-entered formula:Now you can see in the Locals window that the vArr variant contains a 2-dimensional array of variants with a sub-type of double.

Entering =testfunc({1,2,3;5,6,7}) also results in a 2 dimensional array:

but =testfunc({1,2,3}) results in a 1-dimensional array!:

whereas =testfunc({1;2;3}) gives a 2-dimensional array!;and =testfunc(45) gives a scalar, not an array;

If you give a range as the parameter =testfunc($A$10:$A$15) then you get this

Notice that theParameter variant contains an object of sub-type Range, which means you have to treat it as a Range Variable, whereas the vArr contains the values extracted from the Range.

### Determining Type and Dimensions for a Variant parameter

So in a general purpose UDF you want to use Variant parameters, and you often need to determine the type and upper and lower bounds of the variant.

For maximum efficiency you cannot just use vArr=theVariant, because:

- You cannot use .Value2 because it might not be a range.
- In many cases you want to manipulate the Range object before/instead of just coercing all its values.

So here is a function to determine what has been passed, and how large it is:

Function Variant_Type(theVariant As Variant) Dim jRowL As Long Dim jRowU As Long Dim jColL As Long Dim jColU As Long Dim jType As Long Dim varr As Variant ' ' theVariant could contain a scalar, an array, or a range ' find the upper and lower bounds and type ' type=1 range, 2 2-d variant array, 3 1-d variant array (single row of columns), 4 scalar ' On Error GoTo FuncFail jType = 0 jRowL = 0 jColL = 0 jRowU = -1 jColU = -1 If TypeName(theVariant) = "Range" Then jRowL = 1 jColL = 1 jRowU = theVariant.Rows.Count jColU = theVariant.Columns.Count jType = 1 ElseIf IsArray(theVariant) Then jRowL = LBound(theVariant, 1) jRowU = UBound(theVariant, 1) On Error Resume Next jColL = LBound(theVariant, 2) jColU = UBound(theVariant, 2) On Error GoTo FuncFail If jColU < 0 Then jType = 3 jColL = jRowL jColU = jRowU jRowL = 0 jRowU = -1 Else jType = 2 End If Else jRowL = 1 jRowU = 1 jColL = 1 jColU = 1 jType = 4 End If Variant_Type = jType Exit Function FuncFail: Variant_Type = CVErr(xlErrValue) jType = 0 jRowU = -1 jColU = -1 End Function

Note that the first test is whether the variant contains a Range. This is to avoid inadvertently coercing a Range to its values. Also there are several ways in VBA in determining the sub-type of a variant:

- If TypeOf theVariant Is Range Then
- If TypeName(theVariant) = “Range” Then

*Beware of trying to use VarType(theVariant) : this does an under-the-covers coerce of a Range and then throws the resulting values away! (Expensive for large ranges).*

### Conclusion:

In a general purpose UDF you have to use Variant type parameters rather than Range type.

You can handle this efficiently by determining what the variant contains before processing it.