Rapid Application Development / Advanced Excel Development

Excel xll Add-in Development - Excel CALL() Function

For details of our custom xll User Defined Function development service please go here.

We can also offer training and coaching in xll development.

Xll Series Contents

(note this is a work in progress - more pages will be added and linked too as and when we have time to complete them to an acceptable level)

• Series intro
• Brief introduction to xlls - what, why, etc.
• The Codematic xll base project
• Installing Visual Studio C++ 2008 Express.
• Installing the Excel Xll SDK for use with Visual Studio 2008 (C++ Express)
• Very brief overview of C and C++ editors (VS2008)
• Creating a simple dll
• Debugging xlls
• Introduction to XLM.
• Using CALL to access external dll functions from XLM (this page)
• Registering dll functions for use from a worksheet cell - using XLM and VBA
• The XLOPER data type and xlcall.h
• The Excel4() function and xlcall.h
• Converting a dll to xll by adding in the registration information
• The Microsoft xll Software Development Kit and framework.
• The Codematic custom xll development service

CALL()

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.

In Excel

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),

• =RESULT(7)
• =CALL("E:\add2.dll","fnAdd2","J") (change the path to suit your location)
• =RETURN(B2)

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

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 this is clearer)
4. 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

• Argument 1 is the Register ID returned from the REGISTER()
• Argument 2 onwards are the parameters to pass to the dll
• The path, function name and type string are all pulled in by Excel from the Register function.
• This usage is covered as part of the REGISTER() page here.

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.

=CALL("E:\Call_Reg.dll","Add2","BBB",20,30)

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)

If you require any more information please get in touch.