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)