Xlls with Planatech XLL+

A 30 day trial is available here.

Pricing is here, (about 1,100 USD/700GBP for a single dev license).

XLL+ is a C++ based Visual Studio Add-in for writing xlls. That means you need VS Professional not an Express version. It’s also C++, if that moves it outside your interest area, I would say read on don’t skip the rest of this – perhaps there are features that would make it worth your while to invest in C++.

XLL+ adds a new xll project type.

As you are setting up the project you need to consider which advanced features you may want to include

I’ll discuss some of these below, the defaults are fine for most simple xll projects, but you may need to make changes here for some of the more powerful features. This is a review rather than a tutorial though, so I won’t be covering all. The product comes with good help files, and they can be downloaded separately if you want to check then out before downloading the tool.

In one of these XLL+ projects you can use tools>>New XLL+ Function to get a simple wizard to guide you through creating a udf (or command).

There are some incredibly powerful features here just exposed by a single check box.

Cache results, for example easily allows your function to hold a local copy of its results to save an expensive database round trip for example. The wizard generates the code to check the cache first, only calling your function if needed. I guess you have to decide how the cache gets invalidated.

There are others like asynchronous functions, this will be a new integral part of Excel 2010 and the 2010 SDK, but with XLL+ you can use it in every version of Excel. With an async function the calc sets it off, and its return value is initially ##NO DATA## or something, then at some future point when your function has finished calculating it calls back into Excel to update that value to be the real result. This is very valuable for slow functions.

Once you get through the wizards and drop into the actual code (C++ as mentioned before) the most obvious thing is that XLL+ has generated two versions of your function: a pre 2007 one, and a 2007 one. This saves a major pain if you are targeting multiple Excel versions. It has also sorted all the registration issues to get your function correctly registered in each version on Excel. This can be changed via the wizard for example if you want to add arguments or change data types. Helpfully it wraps it all up in exception handlers too.

XLL+ then provides a bunch of useful classes to interact with the Excel host, some of these are not available in UDFs, but XLL+ can also generate commands (the equivalent of the Sub in VBA). In many cases manipulating the Excel OM through the C API is staggeringly fast compared to the VBA equiv. For example the worksheet password remover here (written without XLL+) is an order of magnitude faster than a VBA or VB6, or .net version that uses the COM OM. I recently did some styles stuff in XLL+, again 10-100 times faster than the VSTO equivalent. XLL+ doesn’t give full coverage, but it allows raw calls to the underlying SDK for anything that is not common enough to be included.

At the end of the day though you are going to need to write your udf in C++, unless….

You decide to make use of the ‘new-in-the-latest-version’ feature to wrap .net functions and pass them through the xll interface. For an enterprise with an existing codebase in C# being able to expose those through the fast xll interface instead of slow automation functions is a massive benefit. Deployment is easy too, just put the xll and the .net assembly dll it wraps in the same folder and you are done, no registry, not path hassles, it just works. There are limitations around the data types the .net component exposes and whether they can be converted effectively to types the xll interface can pass. The end result is a little slower than a pure C/C++ xll, because there is still some marshalling from native to managed, but its much faster than the automation approach because there is no COM layer in the way.

This would also work if you code your udf in VB.net of course

XLL+ also provides logging out of the box so you can keep track of whats going on, and there are basic performance profiling features too. And as it is all written in C++ you can extend as you see fit with your own, or someone elses C++. The other big (and somewhat advanced) feature is handles, XLL+ makes it very simple to create a large object in the xll memory then make various bits of that available to Excel cells.

I would say XLL+ is certainly targeted more at the advanced end of Excel developments, its not really a hobbyist’s toy. If you can see the benefits to the work you do then you really need to set aside something like a week to get to grips with the product and start to understand how you can best get it to work for you.

In the past I have just fired it up and hacked together a UDF that does what I want, roughly. These last few weeks though I have spent much more time reading the help files and trying the samples, even those that do not seem relevant to my current projects. I now have a good sense of what the product can do and how best to make it do it.

I compared XLL+ performance to one of my hand written C UDFs and was shocked to see XLL+ was faster. What’s this I thought? all that clunky C++ faster than lean mean C? then I realised XLL+ had registered its version as thread safe and I hadn’t with mine (yep thats right, I’m in E2007 at the moment). Once I fixed that they were identical, except the XLL+ one was easier to write (and a much bigger file: 500kb v 48kb).

For me killer features are

  • Simple initial deployment
  • Excellent performance
  • Easy to develop compared to raw xlls
  • Ability to wrap .net dlls and pass them through the xll interface for decent performance (way better then VBA for example)
  • Easy Excel version neutrality
  • Automatic help file generation

The biggest downsides are

  • Need strong C++ to get the most from the product (perhaps not if you purely wrap .net assemblies)
  • The way the Excel OM is exposed is quite a leap for a VBA dev.
  • Significant learning curve for Excel devs

In summary I think XLL+ is perhaps aimed more at enterprise type C++ devs needing to expose their powerful C++ libraries to Excel users, rather than Excel power users and developers. The latter will need to invest in some C++ to get the best return on their financial investment if they buy the product. (And good luck getting C++ training!) That said dedicated performance buffs should seriously consider making the investment.

Have you used/do you use XLL+? what do you think?

Next up (in a couple of days) I‘m going to take a look at the opposite end of the cost spectrum.

Cheers
Simon

ps I’m going to do a proper performance summary in a week or so once I have reviewed all the tools in my list.

Advertisements

One Response to “Xlls with Planatech XLL+”

  1. ross Says:

    Simon,
    These post are really really good, I wish, wish, wish I had the time to read them, I’m snowed at the mo. I can only flick through them, good news I’ve got somthing to come back too!

    Excellent work old boy!

Leave a 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: