Excel UDF Technology Choices – Snakes & ladders with VBA, VB6, .NET, C++, COM, XLL, Interop …

I develop a commercial Excel addin product, FastExcel, one component of which contains a number of UDFs. I have been planning and developing the next version of FastExcel for some time, and it probably won’t surprise you to hear that it will contain even more UDFs designed to help you make Excel calculate faster.

Currently the product supports Excel 97 through Excel 2010 32-bit and uses VBA with a lot of Windows API Calls. But the not-so-simple question was:

What technology to use for the UDFs?

Having got bored waiting for MicroSoft to fix the UDF VBE refresh bug I wanted to use a technology that solved that problem. It would also be nice if I did not have to rewrite the existing 8000 lines of VBA code. And faster performance would of course be good.

I had just about decided to use VB6 Automation addins, with a VBA wrapper for Excel 97 and Excel 2000, and had an initial Beta version which demonstrated that the technology worked well.

Then came the news of Excel 2010 64-bit, and guess what – VB6 does not produce 64-bit DLLs (and by the way Excel 2010 still does not fix the UDF VBE refresh bug).
So VB6 was a snake and not a ladder.
It was time for a rethink and a more thorough evaluation of the alternatives, including some performance benchmarking.

Some preliminary research narrowed my choices to:

  • Continue to use VBA
  • Use VB6 and ignore the 64-bit market
  • Take the .NET plunge and rewrite using one of
  • Go for broke with C++ using XLL Plus or equivalent

VSTO was rapidly ruled out when I discovered that it did not support Automation Addin UDFs or XLLs and was not good for version independence.

UDF Technology Benchmarks

The UDFs in FastExcel are mostly general purpose UDFs and so have to handle all the Excel data types with reasonably large sets of data. I decided to benchmark a do-nothing UDF to see what the overhead per UDF call was, and a UDF whose performance was dominated by data transfer. I used the AverageTol UDF shown in Writing Efficient VBA UDFs Part 1 for the data transfer UDF.

The performance results looked like this:

Timings in Milliseconds
AvTol 10K datacells
x 10 calls
  10K Calls
   Do Nothing
VBA Worst
7704
7030
SUMPRODUCT
277
4
VB.Net Automation Addin
170
101
Addin Express VB.Net Auto
170
101
VBA Best
109
26
Addin Express VB.Net XLL
100
112
XLDNA Vb.Net XLL
81
77
VB6 Automation Addin
63
25
XLL+ C XLL
37
13

VBA appears twice in the table:

  • The first VBA times are the least efficient version of the AverageTol UDF and using the Do-Nothing UDF without bypassing the VBE Refresh bug.
  • The second VBA times are the most efficient (non-array) version of the AverageTolUDF and using the Do-Nothing UDF but bypassing the VBE refresh bug.

Addin Express also appears twice because it supports both XLL and COM-Interop technologies.

Technologies:

There are basically three underlying technologies that can be used by UDFs:

  • COM (VBA and VB6)
  • XLL (C++, Excel DNA and Addin Express)
  • COM-Interop (.Net Automation, Excel DNa and Addin Express)

The COM and COM-Interop interface exposes all the Excel object model, whereas the XLL interface is more limited but much faster.
COM-Interop is generally slow because of the additional Interop overhead.

So .NET products such as Excel DNa and Addin Express offer both the XLL and COM interfaces, and thats why I benchmarked both technologies.
From a performance point of view the XLL interface is clearly unbeatable, particularly with C++.

Tools Technology Support

Performance is important, but there are many other factors that could influence your choice of technology. In fact all of these solutions can be a good choice in different circumstances.
I was particularly interested in 64-bit support, Multi-threading, code/intellectual property security and Function wizard support:

64Bit      Multithread
   Security
Func Wizard
VBA Worst
     Y                      N
  None
Limited
SUMPRODUCT
     Y                      Y
  Excellent
Excellent
VB.Net Auto
     Y                      Y
  Good (Obfusc)
Limited
ADX VB.Net
     Y                      Y
  Good (Obfusc)
Good
VBA Best
     Y                      N
  Poor
Good (Hack)
ADX VB.XLL
     Y                      Y
 Good (Obfusc)
Good
XLDNA Vb.Net
     Y                      Y
  Good (Obfusc)
Good
VB6 Auto
    N                      N
  Excellent
Good (Hack)
XLL+ C
    Y                      Y
  Excellent
Good

I reckon it will be some time before 64-bit Excel is in widespread use, but I have already had several requests to support it from high-end users.
Multi-Threaded UDFs are moving into the mainstream as all new PCs have multiple cores and Excel 2007 and Excel 2010 penetration ramps up.
The security column is not about ant-virus security but protection of intellectual property and licensing. Obfusc refers to the need to obfuscate .Net code because it is easy to reverse compile it.
The function wizard support is about how easy it is to provide argument descriptions and help for your UDFs from within the Function wizard.
There is a good description here of the technique used for VBA and VB6 to provide enhanced Function Wizard support for UDFs (regarded by some as a bit of a hack), but even in Excel 2010 there is no way of implementing the argument intellisense that Excel’s native functions provide.

The Decision

It became clear that the easiest solution, using VB6 automation addins, was not the right choice because of lack of 64-bit and multi-threading support.
So I would have to rewrite, either to .NET or to C++.

Its easier to rewrite to .NET than to C++, and both Addin Express and Excel DNA provide excellent support. Excel DNA seems to have slightly better performance but Addin Express has better installability with .Net version independence.

XLL Plus shields you from a lot of the pain of working with the XLL SDK and I found it very easy to develop simple UDFs. But it was clear that some of the things I wanted to do would be challenging in C++, and the learning curve would be even steeper than .NET.

Looking longer term its hard to see which of these technologies (if any) provides a better long-term strategy. You would think that if MicroSoft were serious about .NET programmability in Office they would provide something better than VSTO, but so far there is no sign of that. They continue to support VBA, even if a bit weakly, and the XLL interface has continued to lead the way in performance support.

So I finally decided to bite the bullet and go the C++ route with XLL Plus. Significant performance and technology advantages, and if you are going to have to rewrite anyway, why not go for it?

So far its working well, but there is a lot to learn as I wade into XLL Plus, VS2010, the STL and BOOST libraries etc etc.

So thats my decision: whats yours?

About these ads
This entry was posted in .NET, UDF, VBA, XLL. Bookmark the permalink.

14 Responses to Excel UDF Technology Choices – Snakes & ladders with VBA, VB6, .NET, C++, COM, XLL, Interop …

  1. Jon says:

    I went with XLDNA. Mainly because it is free and easy to work with. I used a method where I took my Excel 2003 library (had to put it through a microsoft coding tool that made it so I could make it a reference to VB.NET and ship it with the .NET program (if you are interested I can send you the link on how to do that). So far so good, but only having programmed in VBA before (except for a C++ sharp class in college), it is quite the learning curve to learn .NET compared to VBA. (I was an engineer, but I like to create things and engineering it’s hard to get to create things unless you are at the top of your class or can figure out how to start your own company).

  2. Roger Govier says:

    A brave decision Charles, but also, I feel the correct one.
    A lot of pain as you go through the learning curve, but, you will have a better product at the end, and will have added to your already full level of competency across a wider range of skills.

    I wish you well with the whole process.

  3. VSTO certainly seems like a dead-end: it has some utility for automation from outside but offers nothing (that I could see) worth having once you’re working inside Excel.

    The problem seems to be that Office apps are heavily intertwined with COM and MS still don’t seem to have strategy to move us away from that. I recall that something “interesting” was supposed to have surfaced at the last MVP summit, but I’m not aware that it’s reached the rest of us yet ;-) I still think they’ve missed the opportunity that the DLR gave them to begin the process of uplifting us from VBA in a way that makes sense.

    We still use C++ and the XLSDK (with help from XLW) at present but as we develop more in C# we’ll most likely be starting to make use of XLDNA.

    • fastexcel says:

      Well, VBA is going to be with us for a long time, but I would certainly like to see an improved .NET Office interface.
      Without that or something similar I don’t see a sensible way of developing stuff for both Client and Server Excel, or doing high-performance server Excel.

  4. Govert says:

    Hi Charles,

    Nice to see Excel-DNA being mentioned. I still think it’s pretty brave of you to go down the C++ route ;-)
    Certainly XLL+ looks very useful for that case.

    It does look like using the Excel COM interfaces from .NET will be a bit easier since I discovered the NetOffice library (http://netoffice.codeplex.com). It’s a set of version-independent interop assemblies for Office, that can also be used from Excel-DNA.

    Also, as I get to know the C# 4 improvements in calling COM (both late-bound with ‘dynamic’ and through the interop assemblies), it really does look like C# is now where VB.NET used to be for integrating with the COM interfaces.

    But my favourite remains VB.NET via Excel-DNA:
    * Very nearly copy-and-paste from VBA, so it’s the least jarring upgrade for most Excel gurus.
    * Full access to the .NET system and 3rd party libraries.
    * Nearly always fast enough (or use some cool maths libraries like Math.NET Numerics when you need the superfast MKL matrix libraries).
    * Easy xcopy single deployment (since at least .NET 2.0 is now nearly universal).

    You might know that the Excel Services (though not the Office 365 version?) supported making managed-code UDFs (http://msdn.microsoft.com/en-us/library/ms499792.aspx). Excel-DNA is designed with support to run exactly those same UDF assemblies on the client, though I’m not sure it’s ever been tested. It seems there is little interest in Excel Services so far.

    The HPC server support for Excel will offload managed or native .xll-based add-in to the HPC Cluster, so your C++ libraries should run there, and Excel-DNA add-ins do too.

    -Govert

  5. fastexcel says:

    Hi Govert,
    Not sure if its brave or foolhardy going C++: time will tell.
    Version-independent interop sounds good, that would negate one of the advantages of Addin Express.
    The problem I see with Excel Services is that it does not support the XLL interface, so the performance is always going to bad. The HPC stuff should be OK with the XLL interface.
    Also its not clear to me how much the UDF code would be different for Excel Services and XLDNA Client? (I hate duplicated code bases).

    • Govert says:

      The UDFs for Excel Services would be in a managed .NET assembly (no Excel-DNA on the Excel Services server side). Then that same .NET assembly, together with a .dna file and an .xll can be used from the Excel client, without even recompiling. Excel-DNA has some special support for those Excel Services attributes.

      It might not super useful, since the Excel services UDFs don’t support function wizard categories, help etc. I wouldn’t know about the performance of Excel Services at all. But I’m still to find someone who cares enough about Excel Services to test it…

      And I don’t think the UDF support is available on Office 365.

  6. Have you tried http://nxll.codeplex.com? One thing it has that the other libraries don’t is that it lets you generate documentation that is natively integrated into Excel help.

  7. karan90 says:

    Hey I am new to compiling macros could you please help me out. I wanted to know how do you distribute files created using xll+ or excel dna and does the recipient need to install anything? Thanks.

    • fastexcel says:

      XLL+ and the Visual Studio C++ compiler create an XLL file, which needs to be copied to the users PC and installed in Excel as an XLL addin.
      Excel DNA does not require Visual Studio, but you also need to copy files etc to the users PC.

  8. David says:

    Interesting. As far as I am concerned, I had to make this kind of choice a few years ago when I started having trouble with excessive calculation times when I was carrying out massive calculations on complex building structures. Something like several thousand formulas per sheet, nearly each a udf, which had to be recalculated about 100000 times as I was testing each structural element for a set of 1000s of loads to find the critical one.

    In vba this took about an hour or more. A colleague of mine got his hands on ExcelVBA, which reduced calculation times to about 5 minutes.
    Excel vba has great preformance and it is my default choice since I am already at home with VB and C#, but of course if you are starting from scratch with programming, then why not C++. Speed increase going from vba to C# + ExcelDNA was about twenty-fold. I guess performance must be even better with C++?

    • fastexcel says:

      Yes – ExcelDNA is great. In your case compiled VB6 might have been even faster. Most of my UDFs are data-intensive or object-model intensive or calling-overhead intensive rather than calculation-intensive so I have not done much benchmarking on language calculation speed. But I would expect C++ to be faster

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