Codematic Excel developers logo
Codematic spreadsheet-excel development image

Rapid Application Development / Advanced Excel Development

 
Products for Excel
Commercial Products:
  - Classic Ribbon
  - Alt-FileSearch
  - Password Remover
Spreadsheet Quality Products
Free Products
Excel Development
Excel Development Index
Excel VBA
- VBA IDE (editor)
- VBA Training
- VBA Best Practice
- VBA Performance
- COM Performance
- VBA Security
Excel and Databases
Excel and Pivot Tables
Excel Add-ins
Worksheet Functions
Excel and xlls
Excel (in)security
Excel testing
Excel and .net
Excel External Links
Excel Developer Types
Professional Excel Development
Excel 2007
Excel 2010
Excel Development Archive
Spreadsheet Services
Spreadsheet Development
Spreadsheet Migration
Spreadsheet Maintenance
Spreadsheet Review
Spreadsheet Management
Resources
Excel User Confs
Consultant Profile
Book Reviews
Links
Other
Site Map

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)

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

 

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.

 

 

Upcoming Events:

25 January 2012 - UK Excel Developer Conference - London


Products for sale:

AltFileSearch

Office 2007 FileSearch replacement logo

New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)


wsUnprotector

worksheet password remover logo

Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)


Classic Ribbon Tab

classic ribbon for office 2007 logo

Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)


 

Products coming soon:

Link Manager

(Find and control external links in Excel Workbooks)

Due by Q1 2111.

XLAnalyst Pro

(Excel VBA based spreadsheet auditing tool)

Due before the end of 2111.

  ;-)
This page was last reviewed on December 21, 2011

©Codematic Ltd 1999-2011