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 REGISTER() Function

There is considerable confusion about xlls, what they are and how to create them. This section aims to clear up some of the confusion and raise the profile of this important, but often over-looked technology.

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)

Excel xlls

An xll is a windows32 dll that also contains some instructions that Excel can understand. These instructions tell Excel how to use the actual functions of the xll. You can just as easily use Declares to access xll functions in the same way you would do for Windows API calls for example.

The mechanism for registering xll functions with Excel is a little fiddly. It is probably one of the main hurdles preventing many Excel/VBA developers getting to grips with xlls. An alternative halfway house approach is to create a dll, and then use the REGISTER() functionality to expose its functions. This saves the need to learn a lot of complex C API before you can call a simple dll function directly from a cell.

Note one important difference between xll register and XLM REGISTER(): the xll version is global across the whole of Excel and can be used in any open workbook. The XLM version registers the function for that workbook only.

REGISTER()

Years ago the Excel team at Microsoft were very aware they needed to make it simple to leverage external resources in Excel. In particular it was important to be able to access resources that organisations had already coded in Win32 dlls. To this end they added several important pieces of functionality.

One is the CALL() function covered here.

Another is the REGISTER() function covered on this page.

CALL() lets you call a dll function directly from an XLM sheet cell, REGISTER() allows you to call it directly from a worksheet cell.

In VBA if you create a public function in a module you can automatically call it from a worksheet without further thought. In XLM if you create a macro and define it as a function you can call it directly from a worksheet cell.

In both these cases a name is defined and pointed to your code, you do it yourself in XLM, Excel does it automatically for you in VBA (and it overwrites any existing XLM/XLL names called the same thing).

The REGISTER() XLM function can be used to create a defined name and point it at an external dll based function. This makes that function available directly from a worksheet cell the same way as a VBA function would be. In VBA you can use Application.ExecuteExcel4Macro("REGISTER... but the quotes quickly become a challenge.

When an xll is opened in Excel it should call the xll equivalent of REGISTER() for each function it wants to be available to Excel. You can get the same functionality by creating a workbook or XLA with some VBA, or XLM in the register functions in a normal win32 dll. After the code is run those dll resources are available throughout Excel (with some work) until Excel is closed. You can UNREGISTER() the functions too at any time (but see below).

You can try REGISTER() either by using our dlls in this zip, using the Windows API (eg. GetTickCount), or by creating your own dll in Visual C++.

To register the function used as an example in the call section add this XLM macro:

Before you run it though add the following as a function on a normal worksheet:

=XLMFnAdd2()

It should return a #VALUE! or #NAME? error:

(It will return #VALUE! if the function has previously been registered but is not registered in the current Excel session. It will return #NAME? If the function has never been REGISTER()ed).

After you run the reg macro XLMFnAdd2() will return 42.

Note although we used XLM to register the function it is now being called directly from a worksheet cell out to the dll via a name, not via a macro. It has defined the name XLMFnAdd2 to equal the REGISTER.ID:

 

That name is workbook level so you could delete the macro sheet and the function would still work. Indeed if you add a name with the same value to another workbook it will also evaluate correctly.

REGISTER() Arguments

  1. Path and name of the dll
  2. Name of the function you wish to call
  3. Type string
  4. The name you want to use in Excel cells
  5. A list of arguments to use in the function wizard
  6. The Macro type (2 for a function, 2 for a command)
  7. Which function wizard category to add the function to
  8. Short cut text if the function being registered is a command
  9. Path to help file
  10. Function help to show in the function wizard
  11. - onwards help text for each argument in the function wizard.

 

If your dll has the following function:

__declspec(dllexport) double Add2 (double n1, double n2)
{
return n1 + n2;
}

Then you could register it with this:

=REGISTER("E:\Call_Reg.dll","Add2","BBB","Test",,1)

And then call it from a worksheet cell with this:

=Test(10.2,5.3) or =Test(E25,F25)

 

To use REGISTER() via VBA

Public Sub VBAReg()

Dim s As String
' XLM = =REGISTER("E:\Call_Reg.dll","Add2","BBB","Test",,1)

s = Chr(34) & "E:\Call_Reg.dll" & Chr(34) & "," & _
Chr(34) & "Add2" & Chr(34) & "," & _
Chr(34) & "BBB" & Chr(34) & "," & _
Chr(34) & "VBATest" & Chr(34) & ",,1"
Debug.Print s
Application.ExecuteExcel4Macro ("REGISTER(" & s & ")")

End Sub

And then you can call the same dll function using:

=VBATest(100,200)

Interestingly if you REGISTER() the function via VBA rather than XLM, the resulting name is very hidden. It does not appear in the define names box, and is not listed if you use VBA to list all hidden names.

Using CALL() and REGISTER()

REGISTER() is a function - it has a return value. The return value is the REGISTER.ID - the big (negative) number in the define name dialog above. (Its a big positive number in the case of the 'Test' function used below)

This CallviaID function calls the dll function referenced by the ID in D2, that value can be seen by switching to value view (from the default view formulas view) in the macro sheet:

Entering =CallviaID() in a cell will now return the value 19.3 (10.1 + 9.2)

A quick look in the define name box shows the same value for the name Test:

You wil notice that the parameters (10.1 and 9.2) were hard coded in this example - that is just to save explaining how to create and use parameters in XLM. That detail is covered in the XLM part of this series.

This particular usage scenario seems fairly unnecessary. This availability of CALL(REGISTER.ID) is a probably a hangover from the days when it was also available as a worksheet function. Far more likely is to run REGISTER either via VBA or XLM and then use the resulting name directly in a worksheet cell. You can use the REGISTER.ID with Application.Run in VBA, in that case it makes most sense to cache the value in long, or a cell, or a name.

UNREGISTER()

For completeness we should mention UNREGISTER. This is also an XLM function, it takes a REGISTER.ID as a parameter and unregisters the function. Or it would if it had ever worked. Which it hasn't. Restarting Excel unregisters everything.

 

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