From VBA to C Part 6 – Argument Types – VBA Variants, Ranges and Doubles vs C++ Values, References and Doubles

In my previous series of posts on “Writing Efficient VBA UDFs – Parts 1 to 10” I spent some time discussing how the data type you use for function arguments can have a large effect on performance. Well the same thing is true for C++ UDFs, but there are differences …

Lets start by looking at how the XLL+ Function Wizard allows you to define argument types:

When you click the Type pulldown for an argument in the XLL+ Function Wizard you get a list of the available argument types:

  • Boolean (mapped to c++ bool)
  • Date (as long integer, same base date as Excel)
  • Double
  • Handle (handle to an object)
  • Int
  • NCHandle
  • Reference
  • Short Int
  • String
  • Unsigned Short Int
  • Value (Any value type or array of values)
  • XLArray (Array of numbers)

In this post I want to concentrate on Double, Reference, Value and XLArray.

The VBA AverageTol UDF

In the first post about writing efficient VBA UDFs I showed that a simple set of changes to the AverageTol UDF improved calculation time by a factor of 115.
The fastest UDF used an argument of type Range for the data, and then retrieved all the values from the Range reference into a variant array:

Public Function AverageTolE(theRange As Range, dTol As Double)

Then the individual values in the variant array were converted to Double.

Using Range as the datatype means that the function must be passed a Range reference when its called: if you pass anything other than a Range Excel returns #Value.

To make the function more general you could declare the theRange as Variant. This allows the argument to be a range, an array or an expression that resolves to an array (for instance a calculated column inside an array formula). Then you can test if theRange contains a range reference which will need to be converted (coerced) to values. If its not a Range then it will already contain the values from the array.

Public Function AverageTolE(theRange As Variant, dTol As Double)

If TypeOf theRange is Range then

If you call this function using a Range reference then theRange argument would be a variant containing a Range reference. The calculation speed for a Variant argument and a Range argument is virtually identical.

So what happens when you do the same sort of thing in a C++ XLL?

The C++ AverageTol UDF

You might think that the XLL Reference type argument (type R and U) is the same as a VBA Range, and the XLL Value type argument is the same as a variant, but this is not correct.

There is no real equivalent in a C++ XLL to the VBA Range argument type.

The XLL Reference type argument behaves more like a VBA Variant.

You can pass a range reference, or an array or a constant or anything resulting from a calculated expression. If the argument is a range reference then you can coerce it to values like you can coerce a VBA Range object to its values.

// get input dimensions
// define an Oper to hold the values
CXlOper xlo;
// if Range Ref coerce the whole range to values
if (theRange->IsRef()) {
// if the coerce fails throw #Value exception
if (iCRet!=xlretSuccess) throw CXlErrorException(xlerrValue);
// else dereference the pointer to theRange array into xlo
else xlo=*theRange;

A CXlOper is an XLL+  class that wraps an XLL Oper data structure with useful methods etc. The Oper data structure is complex but is similar to a VBA Variant except that it can contain some additional types of data. The Coerce method converts Opers from one type of data to another: in this case it coerces from a Reference to an array of values.

The Coerce operation can fail, most notably when trying to coerce an uncalculated cell (returns xlretUncalced). So the code checks for failure and if neccessary throws a #Value exception.
Handling uncalculated cells with Coerce is much simpler and more foolproof than the equivalent operation in VBA.

In a similar way to VBA calls to Coerce have a relatively high overhead, so that looping cell by cell using Coerce is much slower than Coercing an entire Range in one call. For 32000 cells cell-by-cell Coerce takes 25 times as long as a Coerce of all 32000 cells as a Range.

The XLL Value type argument (Type P and Q)

The Value type XLL argument is very similar to the Reference type, but with two key differences:

  • Range References are coerced to values before the function is called.
  • The function will not be called until all Value type arguments have been calculated.

The C++ code is simpler because you don’t have to handle the IsReference() case

// value array (P-type)
RW12 nRows=0;
COL12 nCols=0;
double dTemp=0.0,dAvTol=0.0;
RW12 Counter=0;
// loop thru the value array
for (RW12 i=0; i<nRows; i++) {

if (fabs(dTemp)>theTolerance) {
dAvTol += dTemp;
if (dAvTol != 0.0 && Counter != 0)
return dAvTol/Counter;
return 0.0;

Performance wise using Value and looping through the Oper is slightly slower than the Reference example (31 millisecs compared to 25 millisecs).

The XLL Vector of Doubles Argument Type

The XLL Plus Function Wizard allows you to define most scalar Arguments as a vector or a matrix, in this case a vector of doubles.

You can apply various constraints etc, and specify what kind of container you want to use to put the vector into. In this case I am using a Vector container (think one-dimensional dynamic array) from the Standard Template Library (more about containers and the STL in a later post).

This version of the AvTol function breaks the design goal of the function: if there is a non-numeric cell in the data you get an error message from the XLL Plus routines rather than the desired behaviour of ignoring non-numeric data. The error message tells you where the problem is:

#ERROR: Expected number for theNumbers[17]

The code is simple:

double dTemp=0.0,dAvTol=0.0;
ULONG Counter=0,nRows=0;
for (ULONG i=0;i<nRows; i++) {
if (fabs(dTemp)>theTolerance) {

dAvTol += dTemp;
if (dAvTol != 0.0 && Counter != 0)
return xloResult.Ret();

The main difference is theNumbers.size(); to get the number of rows and using [] brackets to access the items in the vector dTemp=theNumbers[i]; (standard C++ array indexing syntax).

This version has the same performance as the Reference example.

The XLL XLArray argument Type ( K or K%)

This argument types creates an XLL array of floating point doubles, which is wrapped in the XLL Plus CXlArray class.

Using this argument type also breaks the design rules for the AvTol function: it does not ignore non-numeric data and in fact just gives you a #Value error.
Also this argument type silently converts empty cells to zero, and does not allow any editing or constraint rules.
The code looks very similar to the previous examples:

double dTemp=0.0,dAvTol=0.0;
ULONG Counter=0,nRows=0;
for (ULONG i=0;i<nRows; i++) {
if (fabs(dTemp)>theTolerance) {

dAvTol += dTemp;
if (dAvTol != 0.0 && Counter != 0)
return xloResult.Ret();

The advantage of this argument type is that it is FAST: 9 millisecs compared to 25 for the Reference and Vector examples.


Al these argument types have their place.

Range Type

  • Does not exist in C++ XLLs

Reference Type

  • Good performance
  • You need to handle the Coerce of uncalculated cells
  • You can resize a Range Reference before Coercing (handle Full column references etc)
  • You can pass a Range Reference to an Excel function without transferring the data to C++.
  • Handles all data types including arrays, constants and mixed data
  • Closest to VBA Variant argument type

Value Type

  • Reasonable performance
  • No need to worry about uncalculated cells
  • All the data is passed to C++, including from full column references
  • Handles all data types including arrays, constants and mixed data
  • Similar but not identical to VBA Variant data type.

Vector and Matrix Types

  • Good performance
  • Ability to apply constraints
  • Some ability to handle full column references by truncating at the last non-empty cell
  • Gives easy access to STL Vector algorithms like SORT etc.
  • Designed for single data types
  • Good error handling for mixed data
  • No VBA equivalent

XLArray Type

  • Fast performance
  • Numerics only
  • Fills empty cells with zeros: not good for full column references or data that can contain zeros
  • If data is mixed does not tell you where the error is
  • No VBA equivalent


C++ XLLs with XLL Plus support a wide variety of data types and can offer faster performance and greater control than VBA UDFs

This entry was posted in UDF, VBA, XLL. Bookmark the permalink.

2 Responses to From VBA to C Part 6 – Argument Types – VBA Variants, Ranges and Doubles vs C++ Values, References and Doubles

  1. kalx says:

    I’m following your posts about XLL Plus with great interest Charles. They seem to have marvelous wizards to help you write xll add-ins, but not so great C++ code that gets generated behind the scenes.

    It is really tricky to get C++ to mimic VBA because C++ is a strongly typed language. I used some techniques I learned from from Andrei Alexandrescu to make OPER’s act like variants. Here is the documentation

    C++ has an astounding amout of expressive power compared to other languages, but it is damn hard to learn. The guy that invented it wrote a book for people that have never programmed before:

    • fastexcel says:

      @Keith – Which bits of generated C++ code are you referring to?

      (I was not aware that I had shown any generated code yet, and the code shown in the posts is deliberately written by me to be as VBA-UDF-like as possible using very restricted C syntax, since the target audience for the posts is people who know how to write VBA Excel UDFs but not C XLL UDFs.)

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s