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
- VSTO
- .NET Interop Automation Addin
- Excel DNA
- Addin Express
- Managed XLL
- …
- 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.
Excel and UDF Performance Stuff 
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).
I think XLDNA is great. For creating personal UDFs with .NET its hard to beat.
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.
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.
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.
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
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).
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.
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.
Keith,
No I haven’t tried it yet: got my hands full with XLL+/STL/Boost etc …
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.
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.
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++?
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
Charles, 3+ years on, are you happy with your decision to go for C++ or have the improvements to XL-DNA made you re-think this decision?
And, if you think C++ is the way to go for better Excel development, are there any good learning resources that you would recommend? I have had quick look and haven’t found much.
Yes I am happy to have gone the C++ route. But its not a slam-dunk decision – there are tradeoffs. In my case I am only using C++ XLLs for user-defined functions and keeping all the other stuff in VBA. In terms of resources I started with the XLL Plus platform which simplifies a lot of the development and contains many code examples in ther help.
Useful books are
Steve Dalton – Financial applications using Excel add-in development in C++
Stephen Prata C++ primer Plus
Thank you. That’s really helpful.
C++ is going through a renaissance. http://electronicdesign.com/dev-tools/interview-bjarne-stroustrup-discusses-c is a good place to start. Some very smart people have been hard at work making C++ easier to use.
If you need to get top performance from Excel for numerical routines you have to use the FP data type. http://xll.codeplex.com/wikipage?title=FP. It hands you a pointer to an array of doubles that can be used by number crunching routines without copying data back and forth.
XLL Plus is a fine product, but AFAIK they don’t allow you to access this.
Keith,
Thanks.
XLL Plus does allow use of FP and yes its very fast, but it does not help with FastExcel because my functions have to be able to handle mixed datatypes.
Hi All,
I have been programming VBA for some time. A custom back-solver, modeling tools and custom reporting solutions. I have tried to covert me VBA to other technologies to improve performance and make it a sell-able product. But always ran in to brick-walls.
Thank you very much to everyone for your insight.
Tried VSTO a dead loss. VB6 had problems with it could never get it stable. Figured at the time it could be my dodgy coding. lol But now think, it my be some of the things you described above were contributing.
Anyway my boss wants me to convert my VBA to an add-in that can be use on SharePoint Services. The add-in talks to backend databases and then returns the result to Excel and manipulates the user interface cell, row, column and sheet properties and creates charts.
It looks like from reading this the fastest and easiest option is VB.NET via Excel-DNA. The boss like my VBA. I boss is on my case on this. Execution performance is not as important as solution delivery deadline.
Learnt C++ in my degree, but never use commercially. I have some VB .Net and VB6 experience. I am not a programming guru. But I am very good at solving complex problems by braking them down into simple steps.
Can someone confirm this possible as I have wasted many hours (months) in the past trying to get tech to work. Only to discover it is a not go.
Is it possible to build an SharePoint Excel Service add in using VB.NET via Excel-DNA?
Anyone Feedback would be helpful.
Kind Regard
Jackson
I don’t know the answer but I suspect not because Excel Services does not support the XLL interface that XLDNA uses, so I suspect you would have to use a pure .NET solution instead. Try googling for Excel Services UDF. Or you could post your question in the XLDNA Groups.