xlfRegister (Form 1)

  • Article

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Can be called from a DLL or XLL command that has itself been called by Microsoft Office Excel. This is equivalent to calling REGISTER from an Excel XLM macro sheet.

xlfRegister can be called in two forms:

  • Form 1: Registers an individual command or function.

  • Form 2: Loads and activates an XLL.

Called in Form 1, this function makes a DLL function or command available to Excel, sets its use count to 1, and returns its registration ID, which can be used to call the function later by using the xlUDF or the xlfCall function. The registration ID is also used to unregister the function using xlfUnregister (Form 1). If the function has already been registered, calling xlfRegister again increments its use count.

This form of the function also defines a hidden name which is the function text argument, pxFunctionText, and which evaluates to the registration ID of the function or command. When you are unregistering the function, this name should be deleted using the xlfSetName. For more information, see Known Issues in Excel XLL Development.

Excel12(xlfRegister, LPXLOPER12 pxRes, int iCount,
    LPXLOPER12 pxModuleText,   LPXLOPER12 pxProcedure,
    LPXLOPER12 pxTypeText,     LPXLOPER12 pxFunctionText,
    LPXLOPER12 pxArgumentText, LPXLOPER12 pxMacroType,
    LPXLOPER12 pxCategory,     LPXLOPER12 pxShortcutText,
    LPXLOPER12 pxHelpTopic,    LPXLOPER12 pxFunctionHelp,
    LPXLOPER12 pxArgumentHelp1, LPXLOPER12 pxArgumentHelp2,
        ...);

Parameters

pxModuleText (xltypeStr)

The name of the DLL that contains the function. This can be obtained by calling the XLL-only function xlGetName if the function being registered is also within the DLL currently executing.

pxProcedure (xltypeStr or xltypeNum)

If a string, the name of the function to call as it appears in the DLL code. If a number, the ordinal export number of the function to call. For clarity and robustness, always use the string form.

pxTypeText (xltypeStr)

An optional string specifying the types of all the arguments to the function and the type of the return value of the function. For more information, see the "Remarks" section. This argument can be omitted for a stand-alone DLL (XLL) that includes an xlAutoRegister function or xlAutoRegister12.

NoteNote

xlAutoRegister12 is only supported in Excel 2007 and later versions.

If xlfRegister is called with this argument missing, Excel calls xlAutoRegister or xlAutoRegister12, if either exists in the specified DLL, which should then properly register the function by providing this information.

pxFunctionText (xltypeStr)

The name of the function as it will appear in the Function Wizard. This argument is optional; if it is omitted, the function is not available in the Function Wizard, and can only be called with the CALL function using the functions registration ID from an XLM macro sheet. Therefore, for ordinary worksheet use, you should treat this argument as required.

pxArgumentText (xltypeStr)

An optional text string that describes the arguments to the function. The user sees this in the Function Wizard. If it is omitted, Excel constructs basic descriptions from pxTypeText.

pxMacroType (xltypeNum or xltypeInt)

An optional argument that indicates the type of XLL entry point. The default value, if omitted, is 1.

pxMacroType value

0

1

2

Can be called from a worksheet

Yes

Yes

No

Can be called from a macro sheet

Yes

Yes

Yes

Can be called from a defined name definition

Yes

Yes

Yes

Can be called from a conditional format expression

Yes

Yes

No

Listed in the Function Wizard for worksheet functions

No

Yes

No

Listed in the Function Wizard for macro sheet functions

No

Yes

Yes

In practice, you should use 1 for worksheet functions, 1 for macro sheet equivalent functions (registered as type #) that you want to call from the worksheet, and 2 for commands.

NoteNote

XLL commands are hidden and not displayed in dialog boxes for running macros, although their names can be entered anywhere a valid command name is required.

pxCategory (xltypeStr or xltypeNum)

An optional argument that lets you specify which category the new function or command should belong to. The Function Wizard divides functions by type (category). You can specify a category name or a sequential number, where the number is the position in which the category appears in the Function Wizard. For more information, see the "Category Names" section. If it is omitted, the User Defined category is assumed.

pxShortcutText (xltypeStr)

A one-character, case-sensitive string specifying the control key that will be assigned to this command. For example, "A" assigns this command to CONTROL+SHIFT+A. This argument is optional and is used for commands only.

pxHelpTopic (xltypeStr)

An optional reference (including path) to the Help file that you want displayed when the user chooses the Help button when your custom function is displayed.

pxFunctionHelp (xltypeStr)

An optional string that describes your custom function when it is selected in the Function Wizard.

pxArgumentHelp1 (xltypeStr)

Optional. The first of the strings that describe the custom function's arguments when the function is selected in the Function Wizard. In versions up to Excel 2003, xlfRegister can take at most 30 arguments, so that you can provide this help for the first 20 of your function's arguments only. In Excel 2007, xlfRegister can take up to 255 arguments, so that you can provide this help for up to 245 function parameters.

Property Value/Return Value

If registration was successful, this function returns the register ID of the function (xltypeNum), which can be used in calls to xlUDF and xlfUnregister within a DLL, or with CALL and UNREGISTER within an XLM macro sheet. Otherwise, it returns a #VALUE! error.

Remarks

Data Types

The pxTypeText argument specifies the data type of the return value and the data types of all arguments to the DLL function or code resource. The first character of pxTypeText specifies the data type of the return value. The remaining characters indicate the data types of all the arguments. For example, a DLL function that returns a floating-point number and takes an integer and a floating-point number as arguments would require "BIB" for the pxTypeText argument.

The data types and structures used by Excel to exchange data with XLLs are summarized in these two tables

The first table lists the types supported in all versions of Excel including Excel 2007.

Data type

Pass by value

Pass by ref (pointer)

Comments

Boolean

A

L

short [int] (0=false or 1=true)

double

B

E

char *

C, F

Null-terminated ASCII byte string

unsigned char *

D, G

Counted ASCII byte string

unsigned short [int]

H

16-bit WORD

[signed] short [int]

I

M

16-bit signed integer

[signed long] int

J

N

32-bit signed integer

FP

K

Floating-point array structure

Array

O

Three arguments are passed:

unsigned short int *

unsigned short int *

double []

XLOPER

P

Variable-type worksheet values and arrays

R

Values, arrays, and range references

Excel 2007 also supports the following data types which have been introduced to support the larger grids and long Unicode strings.

Data type

Pass by value

Pass by ref (pointer)

Comments

unsigned short *

C%, F%

Null-terminated Unicode wide-character string

unsigned short *

D%, G%

Counted Unicode wide-character string

FP12

K%

Larger grid floating-point array structure

Array

O%

Three arguments are passed:
signed int * / RW *

signed int * / COL *

double []

XLOPER12

Q

Variable-type worksheet values and arrays

U

Values, arrays, and range references

The string types F, F%, G, and G% are used for arguments that are modified-in-place.

When you are working with the data types displayed in the previous table, be aware of the following:

  • The C-language declarations assume that your compiler uses 8-byte doubles, 2-byte short integers, and 4-byte long integers by default.

  • All functions in DLLs and code resources are called using the __stdcall calling convention.

  • Any function that returns a data type by reference, that is, that returns a pointer to something, can safely return a null pointer. Excel interprets a null pointer as a #NUM! error.

Additional Data Type Information

This section contains detailed information about the E, F, F%, G, G%, K, O, P, Q, R, and U data types, and other information about the pxTypeText argument.

E Data Type

Excel expects a DLL using the E data type to pass pointers to floating-point numbers on the stack. This can cause problems with some languages (for example, Borland C++) that expect the number to be passed on the coprocessor emulator stack. The workaround is to pass a pointer to the number on the coprocessor stack. The following example shows how to return a double from Borland C++.

typedef double * lpDbl;
extern "C" lpDbl __stdcall AddDbl(double D1,
    double D2, WORD npDbl)
{
    lpDbl Result;
    Result = (lpDbl)MK_FP(_SS, npDbl);
    *Result = D1 + D2;
    return (Result);
}

F, F%, G, and G% Data Types

With the F, F%, G, and G% data types, a function can modify a string buffer that is allocated by Excel. If the return value type code is one of these types, Excel ignores the value returned by the function. Instead, Excel searches the list of function arguments for the first corresponding data type (F, F%, G, or G%) and then takes the current contents of the allocated string buffer as the return value. All versions of Excel allocate 256 bytes for F and G ASCII strings, and Excel 2007 allocates 65,536 bytes, enough for 32,768 Unicode characters, for F% and G% Unicode strings. Remember that the buffers must include a count character (types G and G%) or a null-termination character (types F and F%), so that the actual maximum string lengths are 255 and 32,767. Unicode strings, and therefore type F% and G% arguments, are available only through the C API in Excel 2007.

K and K% Data Types

The K and K% data types use pointers to the variable-sized FP and FP12 structures respectively. These structures are defined in XLLCALL.H. FP12 structures, and therefore type K% arguments, are only supported in Excel 2007.

O and O% Data Types

The O and O% data types can only be used for arguments, not return values, although values can be returned my modifying an O or O% type argument in place. Each passes three items: a pointer to the number of rows in an array, a pointer to the number of columns in an array, and a pointer to a two-dimensional array of floating-point numbers.

To modify an array passed by the O or O% data type in place, you could use ">O" or ">O%" as the pxTypeText argument. For more information about modifying an array, see the "Modifying in Place: Functions Declared as Void" section in this topic.

The O data type was created for direct compatibility with Fortran DLLs, which pass arguments by reference.

The O% is supported in Excel 2007 and later versions only, and accommodates the larger number of rows that Excel 2007 supports.

P and Q Data Types

When DLL function arguments are registered as taking type P XLOPERs or type Q XLOPER12s, Excel converts single-cell references to simple values and multi-cell references to arrays when preparing these arguments. In other words, P and Q types will always arrive in your function as one of these types: xltypeNum, xltypeStr, xltypeBool, xltypeErr, xltypeMulti, xltypeMissing, or xltypeNil, but not xltypeRef or xltypeSRef because these are always dereferenced. XLOPER12s, and therefore type Q arguments, are only supported in Excel 2007.

If types xltypeMissing or xltypeNil are used for return values, they are interpreted by Excel as numeric zero. xltypeMissing is passed when the caller omits an argument. xltypeNil is passed when the caller passes a reference to an empty cell. When a range of cells is converted to an xltypeMulti to be passed as type P or Q, any blank cells within the range are converted to xltypeNil array elements. Missing elements in a literal array are similarly passed as xltypeNil elements.

Volatile Functions and Recalculation

On a worksheet, you can make a DLL function or code resource volatile, so that it recalculates every time the worksheet recalculates. To do this, you just add an exclamation mark (!) after the last argument code in the pxTypeText argument.

NoteNote

By default, functions that take type R XLOPERs or type U XLOPER12s and that are registered as macro sheet equivalents (type # - see next section) are treated as volatile in Excel.

Modifying in Place: Functions Declared as Void

You can use a single digit n for the return type code in pxTypeText, where n is a number from 1 to 9. This tells Excel to take the value of the variable in the location pointed to by the nth argument in pxTypeText as the return value. This is also known as modifying in place. The nth argument must be a pass-by-reference data type (C, D, E, F, F%, G, G%, K, K%, L, M, N, O, O%, P, Q, R, or U). The DLL function or code resource must also be declared with the void keyword in the C/C++ languages (or the procedure keyword in the Pascal language).

For example, a DLL function that takes a null-terminated string and two pointers to integers as arguments can modify the string in place. Use "1FMM" as the pxTypeText argument, and declare the function as void.

Very old versions of Excel used > at the start of pxTypeText to signify that the first argument was to be modified in place—there was no way to modify any argument other than the first. The > is equivalent to n = 1 in current Excel versions and is still supported for backward compatibility only.

Registering Worksheet Functions as Macro Sheet Equivalents (Handling Uncalculated Cells)

Placing a # character after the last parameter code in pxTypeText gives the function the same calling permissions as functions on a macro sheet. These are as follows:

  • The function can retrieve the values of cells that have not yet been calculated in this recalculation cycle.

  • The function can call any of the XLM information (Class 2) functions, for example, xlfGetCell.

  1. If the number sign is not present: evaluating an uncalculated cell results in an xlretUncalced error, and the current function is called again once the cell has been calculated; calling any XLM information function other than xlfCaller results in an xlretInvXlfn error.

Registering Worksheet Functions as Thread-Safe (Excel 2007 Only)

Excel 2007 can perform multithreaded workbook recalculation. This means it can assign different instances of a thread-safe function to concurrent threads for reevaluation. Most of the built-in worksheet functions are thread-safe in Excel 2007. Excel 2007 also allows XLLs to register worksheet functions as being thread safe. You do this by including a $ character after the last parameter code in pxTypeText.

NoteNote

Only worksheet functions can be declared as thread safe. Excel does not consider a macro sheet equivalent function to be thread safe, so that you cannot append both # and $ characters to pxTypeText.

If you have registered a function as thread safe, it is your responsibility to ensure that it behaves in a thread-safe way, although Excel rejects any thread-unsafe calls via the C API. For example, if a thread-safe function tries to call xlfGetCell, the call fails with the xlretNotThreadSafe error.

Category Names

Here are some guidelines for determining which category you should put your XLL functions in.

  • If the function does something that could be done by the user as a part of your add-in user interface, you should put the function in the Commands category.

  • If the function returns information about the state of the add-in or any other useful information, you should put the function in the Information category.

  • An add-in should never add functions or commands to the User Defined category. This category is for the exclusive use of end users.

The category is specified using the pxCategory parameter to xlfRegister. This can be a number or text that corresponds to one of the hard-coded standard categories, or the text of a new category specified by the DLL. If the text given does not already exist, Excel creates a new category with that name.

The following table shows the standard categories that are visible when you view the Paste Function dialog box from within a worksheet:

Number

Text

1

Financial

2

Date & Time

3

Math & Trig

4

Text

5

Logical

6

Lookup & Reference

7

Database

8

Statistical

9

Information

14

User Defined

Engineering (new in Excel 2007)

Cube (new in Excel 2007)

In addition, these categories are also visible when you view the Paste Function dialog box from within a macro sheet.

Number

Text

10

Commands

11

DDE/External

12

Customizing

13

Macro Control

Example

See the code for the xlAutoOpen function in \SAMPLES\GENERIC\GENERIC.C.

See Also

Reference

REGISTER.ID

UNREGISTER

Concepts

Essential and Useful C API XLM Functions