Add-in and Automation functions

Click one of the links in the following list to see detailed help about the function.

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

Excel > Function reference > Add-in and automation functions

CALL function

Caution   Incorrectly editing the registry may severely damage your operating system, requiring you to reinstall it. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data. For the most recent information about using and protecting your computer's registry, see Microsoft Windows Help.

Calls a procedure in a dynamic link library or code resource. There are two syntax forms of this function. Use syntax 1 only with a previously registered code resource, which uses arguments from the REGISTER function. Use syntax 2a or 2b to simultaneously register and call a code resource.

Important  This function is provided for advanced users only. If you use the CALL function incorrectly, you may cause errors that will require you to restart your computer. This function is only available from an Excel macro sheet.

Syntax 1

Used with REGISTER

CALL(register_id,argument1,...)

Syntax 2a

Used alone (in Microsoft Excel for Windows)

CALL(module_text,procedure,type_text,argument1,...)

Syntax 2b

Used alone (in Microsoft Excel for the Macintosh)

CALL(file_text,resource,type_text,argument1,...)

Register_id   is the value returned by a previously executed REGISTER or REGISTER.ID function.

Argument1, ...   are the arguments to be passed to the procedure.

Module_text   is quoted text specifying the name of the dynamic link library (DLL) that contains the procedure in Microsoft Excel for Windows.

File_text   is the name of the file that contains the code resource in Microsoft Excel for the Macintosh.

Procedure   is text specifying the name of the function in the DLL in Microsoft Excel for Windows. You can also use the ordinal value of the function from the EXPORTS statement in the module-definition file (.DEF). The ordinal value must not be in the form of text.

Resource   is the name of the code resource in Microsoft Excel for the Macintosh. You can also use the resource ID number. The resource ID number must not be in the form of text.

Type_text   is text specifying the data type of the return value and the data types of all arguments to the DLL or code resource. The first letter of type_text specifies the return value. The codes you use for type_text are described in detail in Using the CALL and REGISTER Functions. For stand-alone DLLs or code resources (XLLs), you can omit this argument.

Example

Syntax 1 (32-Bit Microsoft Excel)

IN 32-bit Microsoft Excel for Windows, the following macro formula registers the GetTickCount function from 32-bit Microsoft Windows. GetTickCount returns the number of milliseconds that have elapsed since Microsoft Windows was started.

REGISTER("Kernel32","GetTickCount","J")

Assuming that this REGISTER function is in cell A5, after your macro registers GetTickCount, you can use the CALL function to return the number of milliseconds that have elapsed:

CALL(A5)

Syntax 1 (16-Bit Microsoft Excel)

In 16-bit Microsoft Excel for Windows, the following macro formula registers the GetTickCount function from 16-bit Microsoft Windows. GetTickCount returns the number of milliseconds that have elapsed since Microsoft Windows was started.

REGISTER("User","GetTickCount","J")

Assuming that this REGISTER function is in cell A5, after your macro registers GetTickCount, you can use the CALL function to return the number of milliseconds that have elapsed:

CALL(A5)

Example

Syntax 2a (32-Bit Microsoft Excel)

On a worksheet, you can use the following CALL formula (syntax 2a) to call the GetTickCount function:

CALL("Kernel32","GetTickCount","J!")

The ! in the type_text argument forces Microsoft Excel to recalculate the CALL function every time the worksheet recalculates. This updates the elapsed time whenever the worksheet recalculates.

Syntax 2a (16-Bit Microsoft Excel)

On a worksheet, you can use the following CALL formula (syntax 2a) to call the GetTickCount function:

CALL("User","GetTickCount","J!")

The ! in the type_text argument forces Microsoft Excel to recalculate the CALL function every time the worksheet recalculates. This updates the elapsed time whenever the worksheet recalculates.

Tip  You can use optional arguments to the REGISTER function to assign a custom name to a function. This name will appear in the Insert Function dialog box, and you can call the function by using its custom name in a formula. For more information, see REGISTER.

See Also

• Add-in and Automation functions
• REGISTER.ID function

Excel > Function reference > Add-in and automation functions

EUROCONVERT function

Converts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation). The currencies available for conversion are those of European Union (EU) members that have adopted the euro. The function uses fixed conversion rates that are established by the EU.

If this function is not available, and returns the #NAME? error, install and load the Euro Currency Tools Add-in.

Install and load the Euro Currency Tools Add-in add-in

1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
2. Select Excel Add-ins in the Manage list box, and then click Go.
3. In the Add-Ins Available list, select the Euro Currency Tools box, and then click OK.
4. If necessary, follow the instructions in the setup program.

Syntax

EUROCONVERT(number,source,target,full_precision,triangulation_precision)

Number   is the currency value you want to convert, or a reference to a cell containing the value.

Source   is a three-letter string, or reference to a cell containing the string, corresponding to the ISO code for the source currency. The following currency codes are available in the EUROCONVERT function:

Target   is a three-letter string, or cell reference, corresponding to the ISO code of the currency to which you want to convert the number. See the previous Source table for the ISO codes.

Full_precision   is a logical value (TRUE or FALSE), or an expression that evaluates to a value of TRUE or FALSE, that specifies how to display the result.

The following table shows the currency specific rounding rules, that is, how many decimal places Excel uses to calculate a currency's conversion and display the result.

Triangulation_precision   is an integer equal to or greater than 3 that specifies the number of significant digits to be used for the intermediate euro value when converting between two euro member currencies. If you omit this argument, Excel does not round the intermediate euro value. If you include this argument when converting from a euro member currency to the euro, Excel calculates the intermediate euro value that could then be converted to a euro member currency.

Remarks

• Excel truncates any trailing zeros in the return value.
• If the source ISO code is the same as the target ISO code, Excel returns the original value of the number.
• Invalid parameters return #VALUE.
• This function does not apply a number format.
• This function cannot be used in array formulas.

Examples

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Notes 

• These examples assume conversion rates of 1 euro = 6.55957 French francs and 1.95583 deutsche marks. The EUROCONVERT function uses the current rates established by the EU. Microsoft will update the function if the rates change. To get full information about the rules and the rates currently in effect, see the European Commission publications about the euro.
• The examples show the resulting value stored in the cell, not the formatted value.

See Also

• Add-in and Automation functions
• Financial functions
• List of worksheet functions (by category)

Excel > Function reference > Add-in and automation functions

GETPIVOTDATA function

Returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report.

 Note    You can quickly enter a simple GETPIVOTDATA formula by typing = in the cell you want to return the value to and then clicking the cell in the PivotTable report that contains the data you want to return.

Syntax

GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

Data_field   is the name, enclosed in quotation marks, for the data field that contains the data you want to retrieve.

Pivot_table   is a reference to any cell, range of cells, or named range of cells in a PivotTable report. This information is used to determine which PivotTable report contains the data you want to retrieve.

Field1, Item1, Field2, Item2   are 1 to 126 pairs of field names and item names that describe the data you want to retrieve. The pairs can be in any order. Field names and names for items other than dates and numbers are enclosed in quotation marks. For OLAP PivotTable reports, items can contain the source name of the dimension as well as the source name of the item. A field and item pair for an OLAP PivotTable might look like this:

"[Product]","[Product].[All Products].[Foods].[Baked Goods]"

Remarks

• Calculated fields or items and custom calculations are included in GETPIVOTDATA calculations.
• If pivot_table is a range that includes two or more PivotTable reports, data will be retrieved from whichever report was created most recently in the range.
• If the field and item arguments describe a single cell, the value of that cell is returned regardless of whether it is a string, number, error, and so on.
• If an item contains a date, the value must be expressed as a serial number or populated by using the DATE function so that the value will be retained if the spreadsheet is opened in a different locale. For example, an item referring to the date March 5, 1999 could be entered as 36224 or DATE(1999,3,5). Times can be entered as decimal values or by using the TIME function.
• If pivot_table is not a range in which a PivotTable report is found, GETPIVOTDATA returns #REF!.
• If the arguments do not describe a visible field, or if they include a report filter in which the filtered data is not displayed, GETPIVOTDATA returns #REF!.

Examples

The range that contains the PivotTable report is:

GETPIVOTDATA("Sales",$A$4)

returns the grand total of the Sales field, $49,325.

GETPIVOTDATA("Sum of Sales",$A$4)

also returns the grand total of the Sales field, $49,325; the field name can be entered exactly as it looks on the sheet, or as its root (without "Sum of," "Count of," and so forth).

GETPIVOTDATA("Sales",$A$4,"Month","March")

returns the grand total for March, $30,337.

GETPIVOTDATA("Sales",$A$4,"Month","March","Product","Produce","Salesperson","Buchanan")

returns $10,201.

GETPIVOTDATA("Sales",$A$4,"Region","South")

returns #REF! because the South region data is not visible.

GETPIVOTDATA("Sales",$A$4,"Product","Beverages","Salesperson","Davolio")

returns #REF! because there is no total value of beverage sales for Davolio.

See Also

• Database functions

Excel > Function reference > Add-in and automation functions

REGISTER.ID function

Caution   Incorrectly editing the registry may severely damage your operating system, requiring you to reinstall it. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data. For the most recent information about using and protecting your computer's registry, see Microsoft Windows Help.

Returns the register ID of the specified dynamic link library (DLL) or code resource that has been previously registered. If the DLL or code resource has not been registered, this function registers the DLL or code resource and then returns the register ID.

REGISTER.ID can be used on worksheets (unlike REGISTER), but you cannot specify a function name and argument names with REGISTER.ID.

For more information about DLLs and code resources and data types, see Using the CALL and REGISTER Functions.

 Note    Because Microsoft Excel for Windows and Microsoft Excel for the Macintosh use different types of code resources, REGISTER.ID has a slightly different syntax for each operating environment.

Syntax 1

For Microsoft Excel for Windows

REGISTER.ID(module_text,procedure,type_text)

Syntax 2

For Microsoft Excel for the Macintosh

REGISTER.ID(file_text,resource,type_text)

Module_text   is text specifying the name of the DLL that contains the function in Microsoft Excel for Windows.

Procedure   is text specifying the name of the function in the DLL in Microsoft Excel for Windows. You can also use the ordinal value of the function from the EXPORTS statement in the module-definition file (.DEF). The ordinal value or resource ID number must not be in text form.

Type_text   is text specifying the data type of the return value and the data types of all arguments to the DLL. The first letter of type_text specifies the return value. If the function or code resource is already registered, you can omit this argument.

File_text   is text specifying the name of the file that contains the code resource in Microsoft Excel for the Macintosh.

Resource   is text specifying the name of the function in the code resource in Microsoft Excel for the Macintosh. You can also use the resource ID number. The ordinal value or resource ID number must not be in text form.

Examples (32-Bit Microsoft Excel)

The following formula registers the GetTickCount function from 32-bit Microsoft Windows and returns the register ID:

REGISTER.ID("Kernel32", "GetTickCount", "J!")

Assuming that GetTickCount was already registered on another sheet using the preceding formula, the following formula returns the register ID for GetTickCount:

REGISTER.ID("Kernel32", "GetTickCount")

Examples (16-Bit Microsoft Excel)

The following formula registers the GetTickCount function from 16-bit Microsoft Windows and returns the register ID:

REGISTER.ID("User", "GetTickCount", "J!")

Assuming that GetTickCount was already registered on another sheet using the preceding formula, the following formula returns the register ID for GetTickCount:

REGISTER.ID("User", "GetTickCount")

See Also

• CALL function

Excel > Function reference > Add-in and automation functions

SQL.REQUEST function

Important  The SQL.REQUEST function is not supported in Microsoft Office Excel 2007 because the Excel 2002 Add-in: Open Database Connectivity (XLODBC.XLA) is only supported in Excel 2002.

Connects with an external data source, and runs a query from a worksheet. SQL.REQUEST then returns the result as an array without the need for macro programming. If this function is not available, you must install the Microsoft Excel ODBC add-in program (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) (XLODBC.XLA). You can install the add-in from the Microsoft Office Web site.

Syntax

SQL.REQUEST(connection_string,output_ref,driver_prompt,query_text,col_names_logical)

Connection_string   supplies information, such as the data source name, user ID, and passwords, required by the driver being used to connect to a data source and must follow the driver's format. The following table provides three example connection strings for three drivers.

• You must define the data source name (DSN) used in connection_string before you try to connect to it.
• You can enter connection_string as an array or a string. If connection_string exceeds 250 characters, you must enter it as an array.
• If SQL.REQUEST is unable to gain access to the data source using connection_string, it returns the #N/A error value.

Output_ref   is a cell reference where you want the completed connection string placed. If you enter SQL.REQUEST on a worksheet, then output_ref is ignored.

• Use output_ref when you want SQL.REQUEST to return the completed connection string (you must enter SQL.REQUEST on a macro sheet in this case).
• If you omit output_ref, SQL.REQUEST does not return a completed connection string.

Driver_prompt   specifies when the driver dialog box is displayed and which options are available. Use one of the numbers described in the following table. If driver_prompt is omitted, SQL.REQUEST uses 2 as the default.

Query_text   is the SQL statement that you want to execute on the data source.

• If SQL.REQUEST is unable to execute query_text on the specified data source, it returns the #N/A error value.
• You can update a query by concatenating references into query_text. In the following example, every time $A$3 changes, SQL.REQUEST uses the new value to update the query.

"SELECT Name FROM Customers WHERE Balance > "&$A$3&"".

Microsoft Excel limits strings to a length of 255 characters. If query_text exceeds that length, enter the query in a vertical range of cells, and use the entire range as the query_text. The values of the cells are concatenated to form the complete SQL statement.

Column_names_logical   indicates whether column names are returned as the first row of the results. Set this argument to TRUE if you want the column names to be returned as the first row of the results. Use FALSE if you do not want the column names returned. If column_names_logical is omitted, SQL.REQUEST does not return column names.

Return Value

• If this function completes all of its actions, it returns an array of query results or the number of rows affected by the query.
• If SQL.REQUEST is unable to access the data source using connection_string, it returns the #N/A error value.

Remarks

• SQL.REQUEST can be entered as an array. When you enter SQL.REQUEST as an array, it returns an array to fit that range.
• If the range of cells is larger than the result set, SQL.REQUEST adds empty cells to the returned array to increase it to the necessary size.
• If the result set is larger than the range entered as an array, SQL.REQUEST returns the whole array.
• The arguments to SQL.REQUEST are in a different order than the arguments to the SQLRequest function in Visual Basic for Applications.

Example

Suppose you want to make a query of a dBASE database named DBASE4. When you enter the following formula in a cell, an array of query results is returned, with the first row being the column names:

SQL.REQUEST("DSN=NWind;DBQ=c:\msquery;FIL=dBASE4", c15, 2,

"Select Custmr_ID, Due_Date from Orders WHERE order_Amt>100", TRUE)

See Also

• Add-in and Automation functions

Excel > Function reference > Add-in and automation functions

Using the CALL and REGISTER Functions

Caution   Incorrectly editing the registry may severely damage your operating system, requiring you to reinstall it. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data. For the most recent information about using and protecting your computer's registry, see Microsoft Windows Help.

The following describes the argument and return value data types used by the CALL, REGISTER, and REGISTER.ID functions. Arguments and return values differ slightly depending on your operating environment, and these differences are noted in the data type table.

Data Types

In the CALL, REGISTER, and REGISTER.ID functions, the type_text 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 type_text 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 type_text argument.

The following table contains a complete list of the data type codes that Microsoft Excel recognizes, a description of each data type, how the argument or return value is passed, and a typical declaration for the data type in the C programming language.

Remarks

• The C-language declarations are based on the assumption that your compiler defaults to 8-byte doubles, 2-byte short integers, and 4-byte long integers.
• In the Microsoft Windows programming environment, all pointers are far pointers. For example, you must declare the D data type code as

unsigned char far

* in Microsoft Windows.

• All functions in DLLs and code resources are called using the Pascal calling convention. Most C compilers allow you to use the Pascal calling convention by adding the Pascal keyword to the function declaration, as shown in the following example:

pascal void main (rows,columns,a)

• If a function uses a pass-by-reference data type for its return value, you can pass a null pointer as the return value. Microsoft Excel will interpret the null pointer as the #NUM! error value.

Additional Data Type Information

This section contains detailed information about the F, G, K, O, P, and R data types and other information about the type_text argument.

F and G Data Types

With the F and G data types, a function can modify a string buffer that is allocated by Microsoft Excel. If the return value type code is F or G, then Microsoft Excel ignores the value returned by the function. Instead, Microsoft Excel searches the list of function arguments for the first corresponding data type (F or G) and then takes the current contents of the allocated string buffer as the return value. Microsoft Excel allocates 256 bytes for the argument, so the function may return a larger string than it received.

K Data Type

The K data type uses a pointer to a variable-size FP structure. You must define this structure in the DLL or code resource as follows:

typedef struct _FP

{

    unsigned short int rows;

    unsigned short int columns;

    double array[1];        /* Actually, array[rows][columns] */

} FP;

               

The declaration

double array[1]

allocates storage for only a single-element array. The number of elements in the actual array equals the number of rows multiplied by the number of columns.

O Data Type

The O data type can be used only as an argument, not as a return value. It 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.

Instead of returning a value, a function can modify an array passed by the O data type. To do this, you can use ">O" as the type_text argument. For more information, see "Modifying in Place — Functions Declared as Void" below.

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

P Data Type

The P data type is a pointer to an OPER structure. The OPER structure contains 8 bytes of data, followed by a 2-byte identifier that specifies the type of data. With the P data type, a DLL function or code resource can take and return any Microsoft Excel data type.

The OPER structure is defined as follows:

typedef struct _oper

{

    union

    {

        double num;

        unsigned char *str;

        unsigned short int bool;

        unsigned short int err;

        struct

        {

            struct _oper *lparray;

            unsigned short int rows;

            unsigned short int columns;

        } array;

    } val;

    unsigned short int type;

} OPER;

               

The type field contains one of these values.

The last two values can be used only as arguments, not return values. The missing argument value (128) is passed when the caller omits an argument. The empty cell value (256) is passed when the caller passes a reference to an empty cell.

R Data Type — Calling Microsoft Excel Functions from DLLs

The R data type is a pointer to an XLOPER structure, which is an enhanced version of the OPER structure. In Microsoft Excel version 4.0 and later, you can use the R data type to write DLLs and code resources that call Microsoft Excel functions. With the XLOPER structure, a DLL function can pass sheet references and implement flow control, in addition to passing data. A complete description of the R data type and the Microsoft Excel application programming interface (API) is beyond the scope of this topic. The Microsoft Office XP Developer's Guide contains detailed information about the R data type, the Microsoft Excel API, and many other technical aspects of Microsoft Excel.

Volatile Functions and Recalculation

Microsoft Excel usually calculates a DLL function (or a code resource) only when it is entered into a cell, when one of its precedents changes, or when the cell is calculated during a macro. On a worksheet, you can make a DLL function or code resource volatile, which means that it recalculates every time the worksheet recalculates. To make a function volatile, add an exclamation point (!) as the last character in the type_text argument.

For example, in Microsoft Excel for Windows, the following worksheet formula recalculates every time the worksheet recalculates:

CALL("Kernel32","GetTickCount","J!")

Modifying in Place — Functions Declared as Void

You can use a single digit n for the return type code in type_text, where n is a number from 1 to 9. This tells Microsoft Excel to modify the variable in the location pointed to by the nth argument in type_text, instead of returning a value. This is also known as modifying in place. The nth argument must be a pass-by-reference data type (C, D, E, F, G, K, L, M, N, O, P, or R). The DLL function or code resource must also be declared with the void keyword in the C language (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 type_text argument, and declare the function as void.

Versions prior to Microsoft Excel 4.0 used the > character to modify the first argument in place; there was no way to modify any argument other than the first. The > character is equivalent to n = 1 in Microsoft Excel version 4.0 and later.