xlls With ExcelDNA

Excel DNA is a free open source tool to expose .net UDFs to Excel via the xll interface.

The code is here, the blog is here. The forums and discussions are here.

It has been my destiny to try ExcelDNA for a very long time, 2 or more years I think it’s been on my todo list. The primary blocker was the need to have .net 2.0, and me keeping my main dev machine at 1.1 for client compatibility reasons. Anyway, new year, new machine, new approach. I’m on Office 2007 and Visual Studio 2008. So Excel DNA is suddenly on my radar.

Excel DNA has 2 main modes, text file mode and .net assembly mode.

In the first one of these Excel DNA is effectively 3 components [EDIT: My bad – as Mike and Ross and Govert have pointed out it is 2 Components because you don’t need to distribute first one]

• The ExcelDNA.Integration.dll assembly to glue the next two bits together
• The ExcelDNA.xll caller
• The UDF in a text file.

To work with ExcelDNA in text mode you only need notepad, or a decent text editor (Notepad++ for example). Visual studio might be handy, but is not essential.

You put the 3 2 components in the same folder, rename the .xll to the same name as the text file. Give the text file a .dna extension, double click the .xll and enable macros and your functions from the text file are there. The code in the text file can be written in any .net language. The demos have VB, C# and F#.

Once you open the xll ExcelDNA registers all the functions it finds in the .dna file. Note there is no direct file lock on the .dll. This means you can code and refresh the .dll without restarting Excel – this can be really handy. I guess if you go and change the parameters or return type you’ll be in for some fun, but for changes to the implementation this is a real time and frustration saver.

In the .net assembly mode there are 4 3 components, the 3 2 from above and the .net assembly. But now instead of the .dna text file containing the function, it just contains a pointer to the .dll assembly, that contains a compiled class library. These can be marked threadsafe, as can the text files I think. This approach makes a big difference, in my limited testing a threadsafe .dll calculates in about a quarter of the time of the text file approach, and this puts it about twice as slow as raw excel or a C xll, close to 10 times faster than VBA.

If intellectual property concerns are an issue then you need to go the .net dll approach, and trust in a .net obfuscater.

For the ease of use, the fact you don’t need Visual Studio, or if you do use it to get the fastest performance, you only need a free Express edition, Excel DNA is extremely impressive.

To create a faster than VBA udf you just need to copy your VBA out to a text file, add a couple of flags, make sure its valid VB.net, change the file name and the .xll name, open the xll and you are away. If you have .net code to expose to Excel just compile it and point the text file to it. My results were a little erratic with the class library I was using, so I’ll need to check that again. As the whole thing is open source I can always trace things right through to understand what the issues are. Bear in mind it is still under development…

[Edit: Multi-Threading could be unreliable in certain situations in 0.21, this is fixed in 0.22]

Deployment is straightforward too, there are several files (3 or 4 2 or 3) that need to end up in the same folder, .net 2.0 or later is needed. But there is no registry messing required, no formal install.

ExcelDNA also covers command based stuff, but I didn’t test that for this review as my focus for now is mainly on UDFs.

ExcelDNA is targeted towards Excel VBA devs who want better udf performance, and it delivers that well. I think its something that deserves a bit of investigation for many Excel VBA devs because it is so accessible.

In summary:

• Simple deployment
• Good performance
• Simple development
• Simple to expose existing .net resources to Excel
• Free and open source
• Also has command features, that I did not test yet.
• Choice of .net language

On the downside:

• Still under development
• Multiple file approach may not suit all situations
• Lifetime management might be a pain, as with most add-ins

Are you using ExcelDNA?

What are your experiences?

cheers

Simon