The CALL Function 

This page was written by Laurent Longre, and is included on my site at his request and with his kind permission.  I have made some minor changes in formatting and spelling.
          

NOTE:  The CALL function has been disabled in Excel 2000, because it represented a very serious security risk.  This page refers to Excel 97 only, not Excel 2000 or Excel 2002.  

 

 

The CALL function

The following formulas are based on the CALL function. CALL, inherited from Excel 3/4, allows you to use DLL functions directly in worksheets.

Syntax :

=CALL(dll_name,function_name,type_string,arg1,...,argN)

Where 
dll_name  is the name of the DLL that contains the function. This name must contain a full path if the DLL file is not located in the Windows folder, the System folder or a folder named in the PATH environment string.

function_name is  name of the function.

type_string  is a text string specifying the data type of the returned value and the data types of all arguments to the DLL. The first letter of type_string  specifies the return value.

arg1, ... argN are the arguments of the function. Their types must correspond to the type string.  Up to 27 arguments may be specified.

The type_string consists in a set of letters indicating the type of each argument. The first letter corresponds to the returned value. Here are the letters used in the following examples:

Code

Description

Transfered by...

C type

B 8-byte floating-point number (IEEE) Value double
C Zero (null) terminated string (max. length = 255 characters) Reference char *
F Zero (null) terminated string (max. length = 255 characters) Reference (modify in place) char *
J 4 bytes wide signed integer Value long int
P Excel's OPER data structure Reference OPER *
R Excel's XLOPER data structure Reference XLOPER *

CALL has also a second (complementary) syntax. For a more detailed description of this syntax and of the type string, see the "CALL Worksheet Function" topic in the Excel Help file.

Example:

=CALL("C:\Temp\MyDll","MyFunc","BJJB",25,3,98.65)

This formula calls the function  "MyFunc" contained in C:\Temp\Mydll.dll. It returns a floating point number (B), and receives three arguments: two long integers ("JJ" = 25 and 3) and one floating point number ("B" = 98.65).

Using XLM functions with CALL

Combining CALL and Excel4

The CALL function, combined with Excel's C API "Excel4" function, allows you to use a large part of the "old" XLM (Excel 4) functions directly in worksheets.

The Excel4 function, contained in the file  ...\Office\Xlcall32.dll, is a callback function which exposes all XLM functions and macros to stand-alone add-in DLLs (XLLs).

C prototype :

int cdecl Excel4(int xlfn, LPXLOPER operRes, int count,...);

Where
xlfn is a number indicating which Excel function or macro to call.

operRes is pointer to an XLOPER structure which receives the result of the function / macro

count is the number of arguments

The arguments following count must be pointers to XLOPER structures ("LPXLOPER").

Excel4 returns a number which indicates wether the call has successed or not. We won't use this value in the following formulas.

Excel4 can be used directly in worksheets, according to this syntax:

=CALL("Xlcall32","Excel4",type_string,xlfn,,count,Arg1,...,ArgN)

The type_string argument must begin with "2JRJ" and (in most cases) end with a "#".

Example :

The formula

=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,18,A1)

returns the name of the font, as text, of the cell A1.

Some explanations about this formula :

"2JR" means that the returned value is the second argument of Excel4 (the "operRes" described above).  When the type_string begins with some number "N", CALL modifies the Nth argument "in place". It allocates memory for the returned value, and frees this memory automatically.   That's why we omit the 5th argument of CALL (which is the second argument of Excel4); CALL  reserves memory for the result in this argument, and Excel4 fills it with the result of the called function.

"JRR"  The returned value and each argument must be declared in the type_string as pointers to XLOPER ("R") or OPER("P") structures. An XLOPER can contain any valid Excel type (range reference, number, string, error code, boolean or "missing argument").

"#" at the end of the string indicates that Excel4 is allowed to call all "class 2" functions, or any worksheet function and any XLM function that returns a value but performs no action (thus, not a macro-function). The use of this letter should be normally reserved to the REGISTER function, but it works also with CALL, though this feature is not documented.

A second example:

=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,18,A1)

"2JRJRR#" indicates that the function returns the result of the function number 185 (first "J"), this result is stored in the second ("2") argument (first "R", the missing argument following the 185), which is modified in place.  The called function has 2 arguments (second "J"), respectively 18 and A1, both declared as LPXLOPERs (ending "RR").  It's an XLM function ("#").

The function number 185 corresponds to the XLM function GET.CELL. In an XLM macro-sheet, the above formula would be simply :

=GET.CELL(18,A1)

Where the 18 is the function code to return the name of the font in cell A1.

The main XLM information functions which can be used in this way are the following:

Function name

Number

Type string

XLM Syntax

GET.CELL

185

"2JRJRR#"

GET.CELL(type_num, reference)
GET.DOCUMENT

188

"2JRJRR#"

GET.DOCUMENT(type_num, name_text)
GET.WINDOW

187

"2JRJRR#"

GET.WINDOW(type_num, window_text)
GET.WORKBOOK

268

"2JRJRR#"

GET.WORKBOOK(type_num, name_text)
GET.WORKSPACE

186

"2JRJR#"

GET.WORKSPACE(type_num)

These functions are described in the help file for XLM functions (Macrofun.hlp), which can be downloaded in Microsoft's site http://support.microsoft.com/support/downloads/LNP129.asp.

Examples :

Using XLM functions in worksheets avoids having to write VBA (or even XLM) user defined functions for getting any information about a cell, a sheet or Excel's environment. XLM functions invoked directly with CALL and Excel4 are faster than custom VBA functions.

Here are some examples:

Warning : don't build the following formulas in the Function Wizard; instead, type them directly in the cells or in the formula bar.

=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,18,A1)
returns the name of the font used in cell A1 (calls the GET.CELL function)

=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,A1)
returns the color index of the first character in A1 (GET.CELL)

=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,41,A1)
returns the formula in the cell
A1 (GET.CELL)

=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,53,A1)
returns the contents of A1 as it is currently displayed, as text, including any additional numbers or symbols resulting from the cell's formatting
(GET.CELL)

=CALL("Xlcall32","Excel4","2JRJRR#",268,,2,4,
CALL("Xlcall32","Excel4","2JRJRR#",185,,2,66,INDIRECT("A1")))

returns the number of sheets in the workbook (GET.WORKBOOK and GET.CELL)

=CALL("Xlcall32","Excel4","2JRJR#",188,,1,50)
returns the total number of pages that would be printed based on current settings (GET.DOCUMENT)

The first two  formulas above are not immediately recalculated (because changing a font or a color in a cell doesn't cause any calculation at all). Formulas 3 and 4 are recalculated when the value in A1 changes. The 5th formula recalculates only when you delete a sheet in the workbook (inserting a sheet doesn't cause a recalculation).

"Static" Rand() :

This formula returns a "static" (not volatile) random value in the range [0,1).  It calls the standard worksheet function RAND(), but as an XLM function. If you want to update the returned random value(s), just press Ctrl-Alt-F9.

=CALL("Xlcall32","Excel4","2JRJ",63)

The following returns a random integer in the range [1,100] (still static):

=INT(CALL("Xlcall32","Excel4","2JRJ",63)*100)+1

Circular references... without circular references :

DLL functions called with a number sign (#) in the type string can deal with the contents of the cell in which they are entered, whereas normal worksheet formulas and VBA functions can't do it without using Circular References:

=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,5,INDIRECT("RC",0))

(equivalent to the XLM formula =GET.CELL(5,INDIRECT("A1"))

The "RC" refers, in R1C1 reference style, to the cell which the formula is entered.

This feature allows to use formulae like the following, which "stores" the highest value entered in the cell A1. You don't need to enable circular references in the Options menu.

=MAX(A1,CALL("Xlcall32","Excel4","2JRJRR#",185,,2,5,INDIRECT("RC",0)))

Using GET.CELL in array formulae :

The GET.CELL XLM function, which retrieves several informations about cells (font, size, etc.), can be used in array formulas.  For instance, you can use it to count the number of cells in a range whith text colored in red.

The syntax of these array formulae is relatively "tortured", because GET.CELL is supposed to work only with single cells, not ranges [... I just say  is supposed to be use].

The following formulae are based on GET.CELL(24,CellRef), which returns the color index of the first character in CellRef (index of red = 3). In our particular syntax, this function corresponds to:

=CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,CellRef)

Number of cells with text colored in Red, in an 1-column range ("Rg"):

{=SUM(N(CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,
TRANSPOSE(OFFSET(Rg,ROW(INDIRECT("1:"&ROWS(Rg)))-1,,1)))=3))}

Number of cells with text colored in Red, in an 1-row range ("Rg"):

{=SUM(N(CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,
TRANSPOSE(OFFSET(Rg,,COLUMN(Rg)-COLUMN(INDEX(Rg,,1)),,1)))=3))}

Number of cells with text colored in Red, in any range ("Rg")

{=SUM(N(CALL("Xlcall32","Excel4","2JRJRR#",185,,2,24,
TRANSPOSE(OFFSET(Rg,ROW(INDIRECT("1:"&ROWS(Rg)))-1,
COLUMN(Rg)-COLUMN(INDEX(Rg,,1)),1,1)))=3))}

A shorter, though slightly slower (but still much faster than a custom VBA function), version of this formula follows:

{=SUM(N(CALL("Xlcall32", "Excel4", "2JRJRR#", 185,,2,24,TRANSPOSE(INDIRECT(ADDRESS(ROW(Rg),COLUMN(Rg),4))))=3))}

When you change the color of a cell, the result of the formula is not updated automatically. You must wait for the next calculation in the sheet, or simply press F9.

The first argument of GET.CELL ("type_num") specifies the expected information. In the above formulas, '24' corresponds to the information "font color of the first character in the cell". If you replace 24 with 18 and 3 with "Arial", these formulae will count the number of cells whose font is set to Arial.

� Copyright, 1998, Laurent Longre.