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:


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


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


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


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
jType = 2
End If
jRowL = 1
jRowU = 1
jColL = 1
jColU = 1
jType = 4
End If
Variant_Type = jType
Exit Function
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).


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.

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

  1. Hello Charles, I discovered your series of articles on writing efficient UDF yesterday and it has been a huge eye opener for me. Especially the article above allowed me for the first time to have a good understanding of how “array & range -capable” formulas work. I have since been rather absorbed in trying to make a few of my UDF’s more performant and more generic (i.e. that they can accepts multi-cell ranges, scalars and can be used in array formulas). Really amazing stuff. Thank you so much!

    There is however something that bugs me about the “Variant_Type” example above. For arrays you determine the lower bound. Why is this necessary? In what cases will the lower bound not be 1?

    Thank you again for sharing your knowledge with the world!

    • fastexcel says:

      If you use Option Base 1 then the default lower bound is 1, but if you don’t its zero. And you can Dim an array to start at any lower bound. So its considered good defensive programming practice to determine the lower bound, although it is often not actually required.

  2. Phillip Stofberg says:

    Got it. I’ve added Option Base 1 to my modules and was able to simplify my code working on the assumption that my arrays’ index start at 1. Thank you for the quick reply.

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