As regular readers will know, I occasionally delve into compiling code in C++, or even Fortran, as dlls and link to them with a short piece of VBA to communicate between the spreadsheet and the compiled code. I have also sometimes had a look at compiling code as an xll file, but found it all too hard, and it hasn’t been obvious to me what the benefits are. What do others think? Am I missing something? What are the benefits of compiling an xll file, compared with a dll?
For future reference (should I decide to have another go at the xll route), two useful looking resources are:
Excel xll Add-in Development
The XLL Add-In Library
Make that three:
The major benifit is speed – not sure of the gain of natice XLL over dll+vba, but it will be quicker, becasue of the com calling which is slow.
Another important benefit is that XLL are not referenced by path, so if the xll moves, or is no a different location on another PC, the code still get called!
Have a look here:
Having said that all I can say is look at xl DNA, I really think you’ll like it…
I think it’s dependent on a number of factors. There’s a small overhead in using VBA as an intermediary: not sure how small, but it may be significant across very large numbers of calls. Plus there may be volatility issues – I haven’t studied this and probably should.
Having an add-in plus a DLL may get tiresome and could perhaps present other problems in controlled environments.
The straight XLL calling convention is probably the fastest available: once registered, I’d guess we’re probably operating at or near function pointer speed.
All that aside, I’m very impressed with the simplicity of the ExcelDNA hookup, which has the added bonus of not having to use C++, a benefit I’m always happy to have. It also supports F#, which ought to be a good fit for functions, although I have to confess that I’ve so far failed to get the F# sample to work…
Thanks Peter and Mike. I tend to use dlls by sending a large chunk of data in an array, and keep transfers to and from the spreadsheet to a minimum, so I’m not sure that the speed difference will be significant. On the other hand if I had high speed communication between the code and the spreadsheet I suppose I wouldn’t have to worry about avoiding it!
Anyway, I’ll probably take another look at xlls, one of these days.
Mike – I’ve added a link to your post on Excel DNA, and the BlogRoll is now plus one Grumpy Old Programmer.
Peter – or should that be Ross? (confused)
Yes is should be Ross, thats what happens when your try messing around on your mates blog leaving messages about post about your self… anyway.
If your looking into XLL then take a look at XLW.
The new spot for The XLL Add-In Library is codeplex. Somebody posted a comment there about being able to use it to build an add-in that works in Excel 97! Amazing.
Thanks Keith. I’ve downloaded it. I don’t know if/when I’ll have time to check it out, but if/when I do, I’ll report back here.
Cool. If you want a peek at the next version that includes the new Excel 2010 features and 64-bit support, the svn repository is http://nxll.kalx.net/. The blog project keeps track of the design decisions as I go along.
My goal is to make it simple for non experts to create high performance add-ins using the native Excel SDK.