The CALL() function allows you to point to a function defined in a dll and then call that from a worksheet cell.
In the good old days CALL() was a worksheet function, enabling simple access to external resources. Unfortunately in a more security aware world that kind of access is no longer acceptable. It has now been made a macro sheet only function. This ensures that users get a macro security warning when opening a workbook that uses CALL().
Users of old (97?) Excel versions or ones that have not applied the 'Disable CALL worksheet function' security patch (which has likely been rolled into any recent service pack) may still have direct access. Most will need to wrap an XLM and expose it as an XLM worksheet function. Or you could use VBA to wrap the CALL().
Very few people still use XLM (we do), which is a shame as it has some fantastic features. Speed being one of them, many operations in XLM can be massively faster to run than VBA.
To try out call you can use the simple dll's we have included in the zip below, or compile the VC project that is included. Or you can create your own VC project.
- Open VC++ and create a win32 dll project
- select the one that exports some symbols.
- To avoid C++ name mangling you should add a .def file to export their boiler plate function. In the example in the zip this is named "fn" + whatever the VC++ project is called, takes no arguments and returns an int (the number 42).
- Compile a release version and copy the dll somewhere easy to find.
Open a new workbook
add a macro sheet (right click over the worksheet tabs>>Insert>>MS Excel 4.0 Macro)
Add the following 3 lines (in column B),
- =CALL("E:\add2.dll","fnAdd2","J") (change the path to suit your location)
Define the cell containing RESULT(7) as your choice of name (choose Macro type 'function' in the bottom right hand option box)
and then call it from a worksheet.
What the above XLM means
- Says the result will be of type number, text or logical
- calls the dll and holds the result, the J tells Excel it will be of data type long
- returns the result held in the cell above (these last 2 could be combined as RETURN(CALL(.., but this is clearer)
- Beware - CALL() is case sensitive.
This approach of writing a simple windows dll, and wrapping calls to it in CALL() XLM 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 ones).
If you want to interact with internal Excel objects then you probably need to go the xll/C API route.
There are two main versions of the CALL() function depending on whether you want to call directly out to the dll, or whether you have used REGISTER() to register the dll function with Excel.
CALL() version 1 - Direct call out to dll (the version shown above)
- Argument 1 is the full path to the dll - this is case sensitive
- Argument 2 is the function name you want to call in the dll (also case sensitive) (can be ordinal (as a number))
- Argument 3 is the type text string. This is rather obtuse, but very compact. The first letter is the data type of the return value, subsequent letters specify the data type of the arguments passed to the function. Finally '!' at the end means volatile, # means macro sheet permissions. Quick key letter summary A = boolean (equiv), B = double, R = Range (roughly), P = Range.value (roughly)
- Arguments 4 onwards are the actual parameter to pass out to the dll.
CALL() version 2 - Calling a previously registered function
You could of course use VBA and Declare the dll functions, but that takes your worksheet function through the slow COM interface.
You can pass arguments via CALL(), these need defining as PARAMETER()s in the XLM function.
This passes the numbers 20 and 30 to a function called Add2 which returns the sum.
Parameters in XLM are covered in the XLM section
The test dlls, the VC project, and the test workbook are all here. (codo.dll beeps when the cell is calculated - just a warning so you can choose where/when to look at this)