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!

About these ads