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

Advertisements

5 Responses to “Excel CALL()”

  1. Ross Says:

    Nice approach Simon, I know that using call is often seen as inviting trouble, but it’s a good way to expose code already there too. The excel 97 devs hand book has quite a bit about the use of call.

    I think nowadays, speed gives way to security.

    I have used the com interface way before (with a micky mouse VB dll i think! – not too sure around this whole thing anyway), it was quick. do you have any speed comparisons?

  2. Paul Says:

    Wow. Thanks for this example. I never would have figured it out otherwise. The Excel 2002 help on CALL did not mention RETURN or the need to define a name. The RETURN function wasn’t even in the Excel 2002 help.

  3. Excel REGISTER() « Smurf on Spreadsheets Says:

    […] By Simon Not long ago (ha ha) I did a post on the CALL() function. I mentioned then I would also cover CALLs sibling function […]

  4. Simon Says:

    Paul
    I had a read of the help – its from a very old version of Excel. And I think it refers to the time when CALL could be used directly from a worksheet. That’s been stopped for security reasons.
    RETURN() is XLM search for macrofun.hlp.

  5. Mathe Says:

    thankl you site

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: