From VBA to C Part 3 – A “Hello Range” function

Lets start with something simple: an XLL function that just returns whatever you feed it as a parameter.

Create an XLL project with VS 2010 and XLL Plus

Installing Planatech XLL Plus integrates some XLL templates and Wizards into Visual Studio 2010. Create a new VS 2010 project and choose the Visual C++ XLL +7 Addin:

Take all the default options and a skeleton project will be created for you.

Use the XLL Plus Function wizard to create a function

On the Visual Studio menu bar choose Tools & click New XLL+ Function.

Click OK and you get this:

We want to give the function one argument – click the type dropdown and choose Value (Value allows the function argument to accept any kind of input), give the argument a name (theInputData) and a description “The data to pass to the Hello Range function”.

You can also give the Function itself a description: “This function returns unchanged whatever you provide as an argument: this could be a range, and array of constants or an expression. All Excel data types are allowed.

You could also choose a category from the Category Dropdown for the Excel Function Wizard – lets choose Information.

The XLL+ Function Wizard should now look like this:

click OK and more C++ code will be generated:

Now we just need to make the function return the Input Data:

// TODO - set the value of xloResult, or return another value
//          using CXlOper::RetXXX() or throw a CXlRuntimeException.
return xloResult.Ret();

And we are ready to test. Click the Start Debugging button (or hit F5) … Excel starts up.
Enter “Hello World” in a cell somewhere (B2),
select another cell (B4),
click the function wizard button,
choose the Information category
and click HelloRange and OK.
Select cell B2 as the input data:

You can see that XLL Plus has given the Excel Function Wizard the description of the function and its argument(s).
If we were not in debug mode a skeleton Help .chm file that you can add to would also have been generated and hooked up to the Function Wizard, and clicking Help on this function would give you:

Now click OK and the function formula will be entered into the cell (I chose B4) and will return Hello World.
Type a number or any other kind of data or formula into B2 and the HelloRange will return it.

In fact HelloRange will also work as an array formula: select cells D5:F6 and enter (using Control-Shift-Enter) =HelloRange({1,2,3;4,5,6}).

To debug the function go back to Visual Studio, select a line of code in the function and press F9. Then go back to Excel and re-enter one of the HelloRange formulas:
Visual studio will halt execution on the F9 line of code, and you can use the Locals, Call Stack and Immediate windows for debugging.

If you hover over theInputData you can see more details of what it contains: most of this is to do with the xlOper data structure that you don’t need to know about right now, but you can see the value of the string:

Comparing XLL and VBA functions.

HelloRange is a trivial function. You could very easily write the same thing in VBA and make it an XLA addin:

Function HelloRangeVB(theInputData As Variant) As Variant
HelloRangeVB = theInputData
End Function

  So what are the advantages and disadvantages of an XLL UDF versus a VBA UDF?

  • The VBA UDF can be built into a workbook or stored in an XLA addin: XLL functions are always in an XLL addin external to the workbook.
  • The XLL UDF is a lot faster than the VBA UDF, and does not have the VBE refresh bug problem.
  • Its easier to add function wizard descriptions and categories using XLL Plus than it is with VBA, and you automatically  get a skeleton .CHM help file hooked up.
  • VBA UDFs in an XLA have the XLA path hard-wired into formulas calling the UDF: XLL functions don’t have this problem.
  • XLL UDFs can be multi-threaded for Excel 2007 and later.
  • VBA UDFs are basically unprotectable, but XLL UDFs are hard to crack.
  • XLL UDFs cannot access as much of the Excel object model via the XLL API as VBA UDFs can.
  • Debugging is somewhat easier with VBA compared to C XLLs.
  • XLL UDFs can return more than 64K rows, VBA UDFs up to XL2010 can’t.

The major disadvantage of XLL UDFs is that they are written in C/C++ (although you can use Excel DNA or Addin Express to create a .NET XLL). And C++ has a frightening reputation for being full of complicated hard-to-understand stuff like Pointers.

It is true that C++ is a much more powerful language than VBA, and the learning curve is undoubtedly steeper.

But I hope to show you in the next few posts that for many UDFs you only need to use a smallish subset of C syntax and that its really not that hard.

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

3 Responses to From VBA to C Part 3 – A “Hello Range” function

  1. Keith A. Lewis says:

    If been following this series with interest Charles. There is no way I can compete with Planatech, but I am always trying to improve the usability of my library for non C++ experts. Below is how you would write your example using it. It uses the ‘named argument idiom’ so as you are typing the code to create an AddIn you will have intellisense displaying the possibilities. That is my low-budget wizard.🙂

    To create integrated documentation just run the macro XLL.DOC.

    #include “xll/xll.h”

    using namespace xll;

    static AddIn xai_hello(
    Function(XLL_LPOPER, “?xll_hello”, “XLL.HELLO”)
    .Arg(XLL_LPOPER, “Range”, “is a range. “)
    // .ThreadSafe() // commented out since this function is not thread-safe
    .FunctionHelp(“Return Range unchanged. “)
    .Documentation(“Here is the documentation!”)
    xll_hello(LPOPER po)
    #pragma XLLEXPORT
    static OPER o;

    o = *po;

    return &o;

  2. Great series of post Charles. Looking forward for the next ones. Thanks for sharing your knowledge and experience.

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