Excel CALL()

I had a great product idea the other day. Xlls are fairly hard to write, and not many people do them. So I thought I could write an xll that would expose other peoples code to Excel as worksheet functions. I thought it through a bit, wrote half a paragragh of description, then thought – Hang on, isn’t this CALL()? (This has been in Excel since V4 at least, so those clever Excel folks beat me to it by what, 15 years? maybe more)

The CALL() function allows you to point to a function defined in a dll and then call that from a worksheet cell. Unfortunately CALL is not a worksheet function, its a macro sheet one. Well its not a worksheet function anymore if you applied the ‘Disable CALL worksheet function’ security patch, which I think is probably rolled into the recent versions of Excel. I suspect there are very few of us left still using XLM, which is a shame as it has some fantastic features. Speed being one of them, doing stuff in XLM can be loads faster to run than VBA.

So anyway I fired up VC++ and created a win32 dll project and selected the one that exports some symbols. This would have been enough except for C++ name mangling, so I then added a .def file to export their boiler plate function. This is named “fn” + whatever the VC++ project is called, takes no arguments and returns an int (the number 42). I compiled a release version and copied the dll somewhere easy to find.

I then added a macro sheet to a test workbook put these 3 lines in, defined it as a name (of type ‘function’) and then called it from a worksheet.

• =RESULT(7)
• =CALL(“E:\add2.dll”,”fnAdd2″,”J”)
• =RETURN(B2)

What these mean

1. Says the result will be of type number, text or logical
2. calls the dll and holds the result, the J tells Excel it will be of data type long
3. returns the result held in the cell above (these last 2 could be combined as RETURN(CALL(.., but I thought this was clearer)

Once I’d finally worked out/remembered that CALL is case sensitive everything worked fine.

I have not really seen much about this approach, apart from all the security advisories (have you?). It seems to me that this gives you most of the benefits of xlls without the hassle of learning the Excel C API. It means you can focus on getting your functions right in your dll (must be a native win32 one , not a Mickey Mouse VB or .net one). You could of course use VBA and Declare the dll functions, but that takes your worksheet function through the oh so slow COM interface.

If you want to interact with internal Excel stuff then you probably need to go the xll/C API route.

The test dlls, the VC project, and the test workbook are all here. Let us know what you think. (codo.dll beeps when the cell is calculated – just a warning so you can choose where/when to look at this ;-))

Does anyone already use this on a regular basis?

cheers

Simon