Advanced Excel Development, Training and Support

User Defined Functions in Excel

This section looks at the different approaches to adding new worksheet formulas (User Defined Functions) to Workbooks and the Excel environment in general.

User Defined Function Options

The simplest option is to create a VBA function that takes variants as parameters and returns the required value. This is a workbook oriented approach - the formula will only work in the workbook with the VBA code or one that has set a reference to it. They are also slooow. Actually as Charles Williams at www.decisionmodels.com points out, VBA is only slow if the calculation is triggered by Excel, if VBA triggers the recalculation the performance is very respectable.

If you want to play around with this stuff here are some minimal functions. They simply return zero as quick as possible, this gives an indication of the cost of calling an XLL versus a VBA function. If you need a simple timer routine here is one. Have fun.

The most challenging type of UDF to write is xll based functions written to Excels C API, generally in C or C++ (you can also use Delphi, or any other language that can follow the C calling convention). These are fast - similar performance to native Excel functions, and coding mistakes will probably crash Excel. They operate at the application level - if you have the xll open the formulas work, if not you get a #NAME! error. A good example of xlls is the analysis toolpack that ships with Excel. Everyone who wants to use your formulas must have a copy of the xll open in their Excel environment. Xll technology seems to be getting a significant refresh for Excel 2007, which suggests a renewed commitment to this technology from MS.

In reality these 2 options will cover 99% of circumstances. But for completeness there is another option if the target machines are running Excel XP (2002) or newer:
Automation add-ins were introduced with Excel 2002, these can be written in any COM compliant language and are a bit like COM add-ins, except they are registered slightly differently. Main language choices are VB6 or .net. VB6 performance is similar to VBA, .net much worse. They are application level, to use the function the add-in must be registered on the machine and running. The behaviour is set when the component is written, they can be managed from Tools>>Add-ins>>Automation... With a reduced target audience, poor performance and a non-trivial installation process, its difficult to recommend them.

There are also some third party tools to help in this area - when we have tried them we will report back.

Worksheet Function Performance

Here is a performance table for a moderately complex array formula that was converted to various types of Visual Studio (2003) project:

Here is the worksheet function that was converted (it returns the last non zero value from a list of 100 values):

{=INDEX(B1:B104,MAX(ROW(B4:B104)*(B4:B104<>0)))}

If you would like the full set of projects to play with then let us know. The C++ one was written with the help of XLL+ from Planatech so you will need a demo copy of that. In fact you can download a Zip with an instruction doc, a test workbook, and the VB6 VBA and C# source code here (1.5Mb). You will need Visual Studio 6 for the VB version and 2003 ror the C# one (it may work in other versions - let us know).

All the programmed solutions benefited from using a specific algorithm suited to the problem. The performance difference would be quite different for any other problem. The likely outcome being Excel and C++ much closer and VBA/VB6 further behind. C# unfortunately, would probably never make the grade.

The VBA one was compiled, and the IDE closed before running. The VB6 version had all speed optimisations set. I did not bother with a VB.net version as there seems no reason to believe it would be materially faster than C#.

The C# code is significantly different to the other code as it needs an Excel reference to be able to understand the range that is passed in. In VB6 and VBA that is treated as a variant, and looped through in the normal way. However, simply adding 2 doubles (ie no Excel objects) in the above manner takes 30 seconds in C#, so its not just the Excel reference, if at all (Excel comparison time? 0 seconds rounded to 0 decimal places!).

The only fair conclusion from this analysis is that it is very expensive to pass through the Primary Interop assemblies for Office 2003. If a worksheet function is only used a few times then the benefits of .net may outweigh the speed penalty, but for the type of use I see, .net is not currently appropriate. This issue has been raised with Microsoft and they are investigating it.

For now, and probably for a good while into the future, the only viable technology to implement most worksheet functions seems to be C or C++. And conveniently enough XLL's are also simple to deploy, requiring no registry access, no VB runtimes, and no .net framework. They also work with all versions of Excel from 97 onwards.

And guess what? Codematic can help you write your custom worksheet functions in C++.

We can work from scratch, a paper spec, a complex worksheet function, a VB/VBA function, even old XLM, whatever, we'll convert it to a C++ XLL. We can also offer try before you buy on XLLs - we will create the XLL if you like the stability and performance you can buy it and receive source code, support etc.

If you require any more information please get in touch.