Writing efficient VBA UDFs (Part 4) – Variants, References, Arrays, Calculated Expressions, Scalars

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.

About these ads
This entry was posted in UDF, VBA. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s