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

Advertisements

9 Responses to “xlls With ExcelDNA”

  1. Mike Staunton Says:

    I’m going to ensure that all my VBA UDFs can be ported easily (with just say half a dozen search-and-replaces) to ExcelDNA – even without marking them as threadsafe they run 7-8 times quicker than in VBA

    One improvement that I’d like to see is that the error messages from ExcelDNA are written to a text file rather than a window as now – since often ExcelDNA will pick up redundant variables and the like that VBA ignores

    ExcelDNA v20 has increased the limit on UDFs in a .dna file from 250 to 1000 so that should keep most people happy – and since deployment is so easy, I think it’s pretty much a no-brainer for my VBA UDFs

  2. mikewoodhouse Says:

    Govert told me that ExcelDNA.Integration.dll doesn’t need to be present – it’s embedded in the main dll. I don’t think it was clear in the docs, though – I was under the same misapprehension.

  3. ross Says:

    I think you only need the 2 things, the XLL wrapper and the source file (eirther flavour). I messed around with it a few months back after Mikes post, and I think it’s brill.
    I’m trying toi line up Govert for a Podcast, but he’s a slipper fish!

    I’ve got to go back to it and try a userform, I’m not 100% yet if you need introp for that, i think you do.

    Ta
    Ross

  4. Simon Says:

    Thanks for the corrections folks
    It is seriously good, I’m going to find some time to look at the command side too.
    There is an example with a user form in the docs Ross. Not that any of us would waste our time reading instructions or owt!

  5. Ross Says:

    Yeah I’ve got the code On my desk top to be fair, it’s c#, and I need to find the time to look at it – it’s going in the book though ;-)

  6. sam Says:

    What about IP….you code is sitting in a Text file…rather than inside the XLL

  7. Simon Says:

    Sam you can wrap a .net dll directly too, and in version 0.25 you can pack everything into 1 xll file aswell.

  8. The MIE Podcasts – Govert van Drimmelen « Methods In Excel Says:

    […] South Africa. Excel DNA has had some attention from the Excel blog sphere in the past few months, Simon, Mike, and Mathias have all posted about it. I’ve been using it for a while now, and it’s […]

  9. Property Management Software Says:

    This seems to a better option for me…I’ll try to get this software and test whether I can apply it to my files or not…it would be great if I am able to use it in a better way…thanks for sharing..:)

Leave a Reply to Property Management Software Cancel reply

Please log in using one of these methods to post your comment:

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


%d bloggers like this: