From VBA to C Part 4 – A small subset of simple C syntax

And just how fast can you say “A small subset of simple C syntax”?

The benchmark  UDF

In this post I am going to build a simple benchmark UDF.
I have chosen the AverageTol UDF that was implemented as optimised VBA in “It ain’t what you do its the way that you do it”.

Here is the final VBA UDF which executed 114 times faster than the original VBA version:


Public Function AverageTolE(theRange As Range, dTol As Double)
'
Dim vArr As Variant
Dim v As Variant
Dim d As Double
Dim r As Double
Dim lCount As Long
On Error GoTo FuncFail
'
vArr = theRange.Value2
On Error GoTo skip
For Each v In vArr
d = CDbl(v)
If Abs(d) > dTol Then
r = r + d
lCount = lCount + 1
End If
skip:
Next v
AverageTolE = r / lCount
Exit Function
FuncFail:
AverageTolE = CVErr(xlErrNA)
End Function


To convert this to C you just need to know how to do a FOR loop and an IF statement and how to declare variables: (oh and you also need to know about curly brackets { … } and semicolons 😉

Semicolons ;

C does not believe that separate lines of code are separate statements like VBA. Instead each statement can span many lines but must be ended with a semicolon. (And yes, this is very easy to forget).

Curly Brackets { … }

Statements can be grouped together using curly brackets (Its OK, you get used to it)

Declaring variables

In C variables are declared as Type_of_Variable Name_of_Variable = initial value;
You can omit the =initial value bit but its dangerous because the variable will then start life with some entirely random value.

// Dim nRows as long
RW12 nRows=0;
//
// Dim nCols as long
COL12 nCols=0;
//
// Dim dTemp as double, dAvTol as double
double dTemp=0.0,dAvTol=0.0;
//
// Dim Counter as long
RW12 Counter=0;

I am showing the VBA statements as comments (// means comment, and its an exception to the end with ; rule!).
RW12 and COL12 are row and column indexes large enough to handle the Excel 2007 big grid.

FOR Loops

The syntax for FOR loops looks like this for (start ; while ; increment) {loop body}

for (RW12 i=0; i ≤ nRows-1; i++){
...
} // end for


The start is RW12 i=0  which defines a variable (and its start value) that will only exist inside the FOR loop: it will disappear when the for loop is exited. You can also use variables that were previously defined and are still in scope.

The while is i<= nRows-1 ; the loop will continue whilst this expression remains true.

The increment is i++ this means increment i by 1 on every iteration of the loop after the first.

The curly brackets enclose the group of statements that will be executed on each iteration of the loop.

IF Statements

The syntax for IF statements is if (logical statement) { do this if true } else { do this if false};

Logical statements often involve comparison & logical operators etc: in C++ these are

  • ==   (is equal to, using one = won’t work!!, this is probably my most frequent mistake)
  • <=   (less than or equal)
  • >=   (greater than or equal)
  • !=    (not equal to)
  • <     (less than)
  • >     (greater than)
  • &&  (AND – note its 2 &s not 1)
  • ||     (OR – note its 2 |s not 1)
  • !      (NOT)

Creating the AVTOLXLL2 Function

Using the XLL+ Function wizard to create the function looks like this:

and so, putting it all together, including the VBA code as // comments, the C code for the function is:

double AvTolXLL2_Impl(const CXlOper* theNumbers, double theTolerance)
{
// End of generated code
//}}XLP_SRC
// TODO - set the value of xloResult, or return another value
// using CXlOper::RetXXX() or throw a CXlRuntimeException.
//
RW12 nRows=0; // Dim nRows as long
COL12 nCols=0; // Dim nCols as long
double dTemp=0.0,dAvTol=0.0 // Dim dTemp as double, dAvTol as double;
RW12 Counter=0; // Dim Counter as long
//
// vArr=theNumbers.Value2
// theNumbers is a pointer to an array of oper values so don't need to convert range to values
//
// get num of rows & cols in input data
theNumbers->GetDims(nRows,nCols); // nRows=Ubound(vArr) nCols=Ubound(vArr,2)
// because theNumbers is a pointer to the oper data you use -> to access the oper methods
//
// loop thru the input data
for (RW12 i=0; i<=nRows ; i++) { // For i=0 To nRows-1 Step 1
// curly brackets for a block of statements
//
// get value, (will be zero if not converted to double)
dTemp=theNumbers->Cell(i,0).ToDouble();    // dTemp=cdbl(vArr(i+1,1)
// ->Cell() is a zero-based method of the oper object

//
//compare to the tolerance
if (fabs(dTemp)>theTolerance) { // If Abs(dTemp)>theTolerance Then
//
dAvTol += dTemp; // dAvTol=dAvTol+dTemp
//
Counter++; // Counter=Counter+1
}; // End If
}; // Next i
//
// return value if neither are zero:
// ! means NOT, && means AND, || means OR
if (dAvTol != 0.0 && Counter != 0) { // If dAvtol<>0.0 and Counter<>0 then
//
return dAvTol/Counter;} // AvTolXLL2=dAvtol/Counter
//
else return 0.0; // else AvTolXLL2=0.0
// Note the ; ends the IF
//
} // End Function

Some other C++ stuff you may have noticed:

  • C++ keywords are all lower-case.
  • Names of variables are case-sensitive (that takes a lot of getting used to, but fortunately VS2010 flags unknown names with red squiggles)
  • Methods are accessed using a . just like VBA, unless its a pointer to an object, in which case use ->

Performance

The AvTolXLL2 function executes about twice as fast as its optimised VBA equivalent on a single core PC.  On a 4-core system it will be up to 4 times faster still using multi-threading with Excel 2007 and later (assuming you have at least 4 formulas calling the function).

Write in C

By now you should be convinced that C++ XLLs are the way to go for UDFs, so I invite you to sing along with our anthem:

And if you are still a die-hard VBA coder your challenge is:

Change the words from “Write in C” to “Write VB” and upload your video to YouTube!

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

4 Responses to From VBA to C Part 4 – A small subset of simple C syntax

  1. Kirill Lapin (KL) says:

    Thank you very much for sharing, Charles. I have recentrly done the journey from VBA to .Net with Add-In Express, and was wondering how to start with XLL. I look forward to the next post.

  2. I have also mirrored your journey up to a point, except I have battled through and learnt how to write these functions the purists’ way, without using any tools.

    I have also just used C, mainly because I am still getting to grips with C. I am old fashioned, I like to see the code. I will probably move onto C++ when I am fully comfortable with C.

    You blogs are very pertinent, and the fact you are prepared to share your experiences is really wonderful.

    May I ask you something? If I create a working XLL with only the “AVTOLXLL2” function in it, would you be interested in benchmarking it against your version?

    It would be interesting to know what the overhead of XLL+ is, if any.

    • fastexcel says:

      Sure: a benchmark would be interesting.

      • Charles,

        Here are the two source components I have created. But instead of creating one function, I have created two:

        AvTolXLL3 – which uses an XLOPER array, to enter the number array. This, as far as I can see, mirrors the same functionality used in AvTolXLL2.

        AvTolXLL4 – which uses as an alternative, the Excel floating-point array structure, to enter the numbers.

        They are both executed the same way. For example:

        =AvTolXXL3(C5:EB,0.5)
        =AvTolXXL4(C5:EB,0.5)

        Do you need some instructions on how to load, compile & test them on Visual Studio?

        Average.c

        #include
        #include
        #include
        #include “xlcall.h”

        typedef struct
        {
        WORD rows;
        WORD columns;
        double array[1];
        }
        xl_array;

        #define FUNCTIONS 2 // THIS VALUE MUST REFLECT THE NUMBER OF FUNCTIONS DEFINED BELOW
        #define ARGUMENTS 11
        #define MAXLENGTH 255

        static char FUNCTIONTABLE[FUNCTIONS][ARGUMENTS][MAXLENGTH] =
        {
        {” AvTolXLL3″, // procedure REPEAT THIS AND THE FOLLOWING 10 LINES FOR EACH FUNCTION
        ” RPB”, // type_text
        ” AvTolXLL3″, // function_text
        ” Array”, // argument_text
        ” 1″, // macro_type
        ” MyFunctions”, // category THIS NAME MUST MATCH THE NAME USED IN THE IN xlAddInManagerInfo FUNCTION
        ” “, // short_cut key
        ” “, // help_topic
        ” Calculate Average of values in Range”, // function_help
        ” Range to Average”, // argument_help1
        ” Tolerance Value” // argument_help2
        },

        {” AvTolXLL4″, // procedure
        ” BKB”, // type_text
        ” AvTolXLL4″, // function_text
        ” Array”, // argument_type
        ” 1″, // macro_type
        ” MyFunctions”, // category THIS NAME MUST MATCH THE NAME USED IN THE IN xlAddInManagerInfo FUNCTION
        ” “, // short_cut key
        ” “, // help_topic
        ” Calculate Average of values in Range”, // function_help
        ” Range to Average”, // argument_help1
        ” Tolerance Value” // argument_help2
        },
        };

        static void HandleRegistration (BOOL bRegister) // THIS FUNCTION IS NOT USUALLY CHANGED
        {
        XLOPER xlXLLName, xlRegID, xlRegArgs[ARGUMENTS];
        int i, j;

        Excel4 (xlGetName, &xlXLLName, 0);

        for (i = 0; i < ARGUMENTS; ++i)
        xlRegArgs[i].xltype = xltypeStr;

        for (i = 0; i < FUNCTIONS; ++i)
        {
        for (j = 0; j < ARGUMENTS; ++j)

        xlRegArgs[j].val.str = FUNCTIONTABLE[i][j];

        if (TRUE == bRegister)
        {
        Excel4 (xlfRegister, 0, ARGUMENTS + 1,
        &xlXLLName,
        &xlRegArgs[0], &xlRegArgs[1], &xlRegArgs[2],
        &xlRegArgs[3], &xlRegArgs[4], &xlRegArgs[5],
        &xlRegArgs[6], &xlRegArgs[7], &xlRegArgs[8],
        &xlRegArgs[9], &xlRegArgs[10]);
        }
        else
        {
        xlRegArgs[4].val.str = "010";

        Excel4 (xlfRegister, 0, ARGUMENTS + 1,
        &xlXLLName,
        &xlRegArgs[0], &xlRegArgs[1], &xlRegArgs[2],
        &xlRegArgs[3], &xlRegArgs[4], &xlRegArgs[5],
        &xlRegArgs[6], &xlRegArgs[7], &xlRegArgs[8],
        &xlRegArgs[9], &xlRegArgs[10]);

        Excel4 (xlfRegisterId, &xlRegID, 2, &xlXLLName, &xlRegArgs[0]);

        Excel4 (xlfUnregister, 0, 1, &xlRegID);
        }
        }

        Excel4 (xlFree, 0, 1, &xlXLLName);
        }

        int APIENTRY DllMain(HINSTANCE hInstance, DWORD fdwReason, PVOID pvReserved) // THIS FUNCTION IS NOT USUALLY CHANGED
        {
        return 1;
        }

        __declspec(dllexport) LPXLOPER xlAddInManagerInfo(LPXLOPER xlAction) // USUALLY ONLY ONE NAME IS CHANGED IN THIS FUNCTION
        {
        static XLOPER xlReturn, xlLongName, xlTemp;

        xlTemp.xltype = xltypeInt;
        xlTemp.val.w = xltypeInt;
        Excel4 (xlCoerce, &xlReturn, 2, xlAction, &xlTemp);

        if (1 == xlReturn.val.w)
        {
        xlLongName.xltype = xltypeStr;
        xlLongName.val.str = "13MyFunctions"; // IMPORTANT: THIS NAME MUST MATCH THE CATEGORY NAME DEFINED ABOVE
        }
        else
        {
        xlLongName.xltype = xltypeErr;
        xlLongName.val.err = xlerrValue;
        }

        return &xlLongName;
        }

        __declspec(dllexport) int xlAutoOpen(void) // THIS FUNCTION IS NOT USUALLY CHANGED
        {
        static XLOPER xDLL;
        int i, j;

        for (i = 0; i < FUNCTIONS; ++i)

        for (j = 0; j xltype & xltypeMulti)
        {
        int size;
        XLOPER *p;

        size = px->val.array.rows * px->val.array.columns;
        p = px->val.array.lparray;

        for (; size– > 0; p++)
        if (p->xltype & (xlbitDLLFree | xlbitXLFree))
        xlAutoFree(p);

        if (px->xltype & xlbitDLLFree)
        free (px->val.array.lparray);
        }
        else if (px->xltype == (xltypeStr | xlbitDLLFree))
        {
        free (px->val.str);
        }
        else if (px->xltype == (xltypeRef | xlbitDLLFree))
        {
        free (px->val.mref.lpmref);
        }
        else if (px->xltype | xlbitXLFree)
        {
        Excel4 (xlFree, 0, 1, px);
        }
        }

        __declspec(dllexport) LPXLOPER AvTolXLL3 (LPXLOPER xMulti, double dTolerance)
        // REGISTRATION Type —R—- —P—- –B— ==> RPB
        {
        static XLOPER xReturn; // Define Return XLOPER
        LPXLOPER xPtr; // Define Pointer to input array of data (XLOPER)
        double dAvTol=0; // Define Total
        unsigned long int i; // Define Subscript
        unsigned long int icounter=0; // Define Counter
        unsigned long int iSize; // Define Number of input values

        xReturn.xltype = xltypeErr; // Prepare Return XLOPER with Error Code (Just in case)
        xReturn.val.err = xlerrValue; //

        if ( xMulti->xltype != xltypeMulti ) // Continue only if we have a Multi XLOPER
        return &xReturn;

        iSize = xMulti->val.array.columns * xMulti->val.array.rows; // Calculate Number of input values
        xPtr = xMulti->val.array.lparray; // Pickup pointer to Input array

        for ( i = 0; i dTolerance ) // Is value > Input tolerance
        {
        dAvTol = dAvTol + xPtr[i].val.num; // Accumulate Total
        icounter++; // Accumulate Number of values used
        }
        }
        }

        xReturn.xltype = xltypeNum; // Prepare Return XLOPER

        if ( dAvTol !=0.0 && icounter !=0 ) // Do we have anything to work with?
        xReturn.val.num = dAvTol/icounter; // Setup Average as return value
        else
        xReturn.val.num = 0.0; // Setup 0.0 as return value

        return &xReturn; // We are done, get out of here
        }

        __declspec(dllexport) double AvTolXLL4 (xl_array *double_array, double dTolerance)
        // REGISTRATION Type –B— —K—- –B— ==> BKB
        {
        double dAvTol=0; // Define Total
        unsigned long int i; // Define Subscript
        unsigned long int icounter=0; // Define Counter
        unsigned long int isize; // Define Number of input values

        if (!double_array ) // Continue only if we have some input
        return 0.0;

        isize = double_array->rows * double_array->columns; // Calculate Number of input values

        for ( i = 0; i array[i]) > dTolerance ) // Is value > Input tolerance
        {
        dAvTol = dAvTol + double_array->array[i]; // Accumulate Total
        icounter++; // Accumulate Number of values used
        }
        }

        if ( dAvTol !=0.0 && icounter !=0 ) // Do we have anything to work with?
        return dAvTol/icounter; // Setup Average as return value
        else
        return 0.0; // Setup 0.0 as return value
        }

        Average.def

        LIBRARY “MyFunctions”

        EXPORTS
        xlAutoOpen
        xlAutoClose
        xlAddInManagerInfo
        AvTolXLL3
        AvTolXLL4

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