The CALL Function

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 Nthargument "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.