Raw xlls

When I say raw, what I mean is just with the Microsoft supplied SDK (and your choice of Visual Studio version (including Express versions)).

I’m not going to repeat all the stuff from here, so if this area is new to you then feel free to start over there.

If you are planning an xll that will simply take some basic parameters (strings, doubles, arrays etc) and return some simple data type then a raw xll may be just the thing. The biggest gotcha for a VBA dev is the lifetime of variables. A local variable in C lasts until the function it is defined in ends. Which means that if you return that variable as the result, by the time it gets back to Excel the address may not be valid. 99 times out of 100 you may get the expected result, every now and then you might get nonesense or an access violation crash. The quick fix prior to Excel 2007 was to declare the variable used as the return as static. Sadly that doesn’t fly in a multithreaded world.

The quick answer is if you want to take advantage of the potential 25% or so speed increase of MTC in 2007 then unless your C/C++ skillz are up to it you may want to look at tools to help, or a hybrid approach. The 2007 SDK does have some Thread Local Storage features, but thats quite a step up from a simple C function.

You can write the function as a normal native windows dll project in C or C++, and then you just need to register it with Excel.

You can do the registration part in VBA or XLM, and once its registered it can be called directly from cells. I covered REGISTER() here. Or you can embed that code in the xll using the xlAutoOpen routine to call Excel4 with xlfRegister.

The biggest ‘correction’ I get when talking to clients about xlls is they assume when I say C or C++ that I mean C#. But xlls are native code, and Microsoft has been unable and/or unwilling to create a compiler from C# to native code.

If your add-in will be calling back into the Excel OM, for example to get cell colours, or some other property, then you need to be sure your XLM skillz are up to the job. For example if you want to know the address of the last cell you need to check MacroFun.hlp to work out what number to pass GET.DOCUMENT (which becomes xlfGetDocument in C). Here again some of the commercial tools may help, as might a hybrid approach.

Raw xlls are a single file (usually) so initial deployment is easy, lifetime management is not so easy as the xll will be locked whenever its loaded into Excel meaning updates are not simple to deploy.This is like xlas which is why many enterprises use an add-in loader. xlls need no installation and the file can be anywhere on the local machine or the network, uninstallation involves either deleting the file or removing the link in the \Excel\Addins part of the registry.

If you are targeting multiple versions of Excel, then there is additional overhead in lining up data types to version, but only really if you want to benefit from the improvements in 2007 and beyond. A xll targeting Excel 2003 will work fine in 2007, no recompilation is necessary, and if the base function is threadsafe you can just register it as threadsafe in 2007 to participate in MTC.

There are no hidden references in xlls, if the function name is used in a cell and Excel finds it in its registered functions list it calls it (if not, you just get a #NAME? error). Unlike VBA UDFs in xlas where the references can get very tangled and file path sensitive.

So in summary raw xlls make sense when

  • you have reasonable C/C++ skills
  • you understand Excels C API
  • The function only deals with basic data types
  • simple initial deployment is useful
  • future updates will be rare
  • you don’t want to buy a commercial tool.

The write/test/correct cycle is more involved than VBA because you have to unregister your dll from Excel to release the file lock. The unregister functionality has never worked so the easiest is to shut down Excel to edit the xll.

There are tons of C++ resources around and its very much a living language, both within the Microsoft world, and outside. C++ gets a big revamp in VS2010.

I have a base project here that separates out the actual function, and all the registration mischief. Hopefully that split makes it simple to see the different elements. I am contemplating making the tool I use to generate those sort of projects available commercially, I’d welcome any views on that…

Anything to add? or disagree with? (xlls with tools coming next)

Cheers

Simon

Advertisements

3 Responses to “Raw xlls”

  1. EP Says:

    Has this always been the behavior of excel to lock the all when loaded? In 2003 is was able to update the xll on a shared drive even if users had the xll referenced and excel open (they wouldnt see the updates until their session was restarted). But now in 2007 Im not able to update the file until all users exit excel. Is there anyway around this? Why was I able to do this in 2003 but cant with 2007?

    Thanks.

  2. EP Says:

    sorry meant to say……

    Has this always been the behavior of excel to lock the *xll* when loaded

  3. Simon Says:

    AFAIK Excel always locks everything it can, and always has. In my experience 2003 also locked loaded xlls, not sure why we are seeing different behavoir, maybe its different network v local?
    We have a network loader that is just a shell that copies the network version of the xll locally every Excel start.
    Charles Williams has one here:
    http://www.decisionmodels.com/downloads.htm
    In 2007 it seems the unregister function suddenly works so you can just unregister every thing to release the lock.

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: