From VBA to C Part 5 – Some Pratfalls – the mistakes I keep making

I have been coding in VBA since Excel 5 (1993) so I keep trying to do VBA things in C, specially when I spend a few weeks doing VBA stuff and then switch back to C.
Here are (just a few) of the mistakes I have made/keep making.

This is what happened the first time I tried to write a function:

VS2010 has generated 2 red squiggles and 2 error messages before I ask it to compile the code:

  • ‘theinputData’ is undefined: thats because in the case-sensitive world of C I did not spell it correctly.
  • Missing semi-colon  ; Notice that it did not actually highlight the line that was the problem, instead when it got to ‘return‘ it recognised that a ; was missing

So lets fix those 2 errors:

CXlOper* HelloRange_Impl(CXlOper& xloResult, const CXlOper* theInputData)
// End of generated code
// TODO - set the value of xloResult, or return another value
// using CXlOper::RetXXX() or throw a CXlRuntimeException.
return xloResult.Ret();

This looks fine: VS2010 does not show any red squiggles: but when I hit debug then this is the result –

The function always ignores whatever the input is and just returns TRUE. When I use debug to inspect the contents of theInputData I can see the “hello world” string, but when I inspect xloResult there is no sign of the string.

If I use the Build –>Configuration Manager in VS2010 to switch from Debug to Release and Rebuild then I get a warning message in the error list, which points to the xloResult=theInputData; line:

For my very first function even a performance freak like me does not worry about performance warnings.
But the message is trying to tell me something about TRUE or FALSE, which sounds like it might have something to do with the problem.

This one had me going back to the books for a couple of hours.  It turns out that there are 2 problems here:

  • Rule 1: Excel thinks “All numbers are Double-precision floating point”, so if you give it an integer it insists you mean TRUE (non-zero number) or FALSE (zero).
  • The other problem is that theInputData is a pointer (address of a memory location that contains a value), rather than the value itself. A Pointer is a non-zero integer, so Excel follows Rule 1 and converts it to TRUE.

How do we know its a pointer? Look at the way its passed to the HelloRange function and you see const CXlOper* theInputData .
The *  means its a pointer!  (The const means that you are not allowed to change the data its pointing to, and CXlOper defines the type of the data).

So I need to turn the pointer into the data it is pointing at. This is called de-referencing and you do using another *


xloResult= * theInputData;

So what do you do if you really do want to return an integer as the answer?

long theAnswer=42;
xloResult=(double) theAnswer;
return xloResult.Ret();

You just convert it to a double!
Using (type) is the C way of type-casting, rather than using conversion functions like CDbl().

One other difference from VBA is that rather than assigning the result to the function you have to use return.

So instead of the VBA:


you use the C:

return xloResult.Ret();

And now for my favourite for loop  mistake:

for ( j=0; j=nRows; j++) {

This looks like it should work: the equivalent of VBA:

For j=0 to nRows
next j

But actually its an infinite loop!
The j=nRows is not the end condition, its the while condition. And its not really a conditional expression, its an assignment which says set the value of j to the value of nRows. So j gets reset to nRows on every iteration of the loop and the condition for continuing the loop is that j is equal to nRows!
This is the correct C syntax:

for ( j=0; j<=nRows; j++) {

and an even more frequent error is:

if (j=nRows) {
// do something

j=nRows is always true, because its assigning the value of nRows to j rather than testing if its true. The correct C code is

if (j==nRows) {
// do something

So remember: in C = is not the same as ==
In the next post I plan to comparet how C++ XLLs handle the equivalent of Range and Variant arguments compared to VBA, and when you would use Range.

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

7 Responses to From VBA to C Part 5 – Some Pratfalls – the mistakes I keep making

  1. Keith A. Lewis says:

    Hi Charles. I am following along and trying out your examples using Here is a straight translation of your AvTol example:

    // avtol.cpp – average tolerance
    // Uncomment the following line to use features for Excel2007 and above.
    //#define EXCEL12
    #include “xll/xll.h”

    using namespace xll;

    typedef traits::xrw xrw;
    typedef traits::xcol xcol;

    static AddInX xai_AvTolXLL2(
    FunctionX(XLL_DOUBLEX, _T(“?AvTolXLL2_Impl”), _T(“AverageTolE”))
    .Arg(XLL_LPOPERX, _T(“Range”), _T(“is a range of numbers. “))
    .Arg(XLL_DOUBLEX, _T(“Tol”), _T(“is the tolerance. “))
    double AvTolXLL2_Impl(const LPOPERX theNumbers, double theTolerance)
    #pragma XLLEXPORT
    double result = 0;
    const OPERX& Numbers(*theNumbers);
    double dAvTol = 0;
    xrw Counter = 0;

    for (xrw i = 0; i theTolerance) {
    dAvTol += Numbers(i,0);
    if (Counter) {
    result = dAvTol/Counter;

    return result;

    Some things to note:
    – The same code can produce add-ins for Excel 2003 and earlier and for Excel2007 and later. Just #define EXCEL12 and recompile.
    – If you pass in the argument from Excel as an LPOPER then Excel will coerce all the arguments to values for you. (If you use LPXLOPER they come in as ranges.)
    – In the xll library, OPER’s are variant C++ data types. Good thing you are just learning C++, otherwise you might think this is impossible.🙂
    – Use the underlying C++ data type in the argument list when possible. If someone tries to pass in theTolerance as a string, Excel does the error checking for you.

    This example can benefit from the FP data type:
    There is no VBA analog of this, but you really need it if you want to do high-performance computing in Excel. Here is how you could implement your example to take advantage of this data type:

    typedef traits::xfp xfp;
    typedef traits::xword xword;

    static AddInX xai_avtol(
    _T(“?xll_avtol”), XLL_DOUBLEX XLL_FPX XLL_DOUBLEX,
    _T(“AVTOL”), _T(“Range, Tolerance”)
    double WINAPI
    xll_avtol(const xfp* pn, double tol)
    #pragma XLLEXPORT
    double avtol(0), n(0);

    for (xword i = 0; i array[i]) > tol) {
    avtol += pn->array[i];

    return n > 0 ? avtol/n : 0;

    • fastexcel says:


      So you have to compile separate XLLs for 2003 versus 2007, and presumably another XLL for 64-bit?

      The AVTOL function is supposed to handle mixed data types as input by ignoring non-numeric, because I wanted to benchmark the general case rather than the floating point case. So you can’t use a floating point argument type for this function, even though it is faster. (My target is not the Quant world, but things like faster Lookups and array formulas, which need to handle any kind of data rather than just numeric).

      • The builds without EXCEL12 defined will run on all versions of Excel back to 97 and in 64-bit Excel. It is also possible to write one set of code that will create an add-in that works in Excel 2003 and automatically use the extended features if loaded in Excel 2007 or later. Can XLL Plus do this too?
        You may want to have a glance at It works on mixed data types and allows you to operate on ranges in memory.
        A handy add-in for dealing with array functions can be found on It is called adjust.xll and adds a right click cell menu entry to paste the array output into the spreadsheet.

  2. Benzadeus says:

    Charles, I’m enjoying very much this “VBA to C” series. I hope you keep the good work.

  3. Jon says:

    Have you tried out ElectricEditing? I use VB.NET so I don’t need it.

    via Scott Hanselman

    CodeRush Electric Editing plugin

    Description of what the plug in does.

    • fastexcel says:

      @Jon, I am using VS2010 – not sure why I would need the plugin? (I use Ctrl AKF lots)

      • Jon says:

        Hope I’m not wasting your time here. From what I read electric editing is supposed to help with the semicolons and brackets, etc. So you don’t end up missing them like you mentioned in the OP. At least that is what I understood from the description of what electric editing does. I believe it works with the code rush core (which is free) which works in VS Professional.

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 )

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