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
- Path and name of the dll
- Name of the function you wish to call
- Type string
- The name you want to use in Excel cells
- A list of arguments to use in the function wizard
- The Macro type (2 for a function, 2 for a command)
- Which function wizard category to add the function to
- Short cut text if the function being registered is a command
- Path to help file
- Function help to show in the function wizard
- - 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. |
