Introduction to DLLs

Created: April 26, 1992

ABSTRACT

MicrosoftÒ Excel can call external code you write in C, Pascal, assembly language, or any other programming language that can be compiled as a dynamic link library (DLL) for the Microsoft WindowsÔ graphical environment or as a code resource for the AppleÒ MacintoshÒ. You can also use Microsoft Excel’s external code resource capability to call system functions from the operating environment. Microsoft Excel can pass arguments to the code resource, and the code resource can return a value to Microsoft Excel.

This article presents examples illustrating the different data types that can be passed between Microsoft Excel and DLLs. It includes sample code for both Windows DLLs and Macintosh code resources. The examples also show the correct syntax for the CALL and REGISTER macro functions that you use to call the DLLs from Microsoft Excel.

WHEN TO USE DLLs

Here are a few examples of appropriate applications for DLLs:1

Using existing high-performance math routines that you have written in another language (complex number calculations, actuarial tables, fast Fourier transforms, and so on)

Creating custom drivers to play sounds, to display animated graphics, or to interface to a custom piece of hardware in your system

Using Microsoft Excel as a front-end to a large application that already exists

Making system calls that return environment information that standard Microsoft Excel functions do not return

Note:

Because Microsoft Excel is not a DLL, you cannot call Microsoft Excel functions from a DLL. You can use dynamic data exchange (DDE), however, to run Microsoft Excel macro functions from a DLL.

THE REGISTER FUNCTION

The REGISTER function loads the DLL into memory and returns a handle that Microsoft Excel uses in the CALL function. You use the REGISTER function to declare the data types for the function arguments and for the function return value. After you REGISTER a DLL, you can use the CALL function to run the DLL. After a DLL has been declared in a REGISTER function, you can CALL that DLL as many times as you want.

When you no longer require the DLL, use the UNREGISTER function to remove it from memory and to free that memory for other use.

REGISTER Function Syntax

The REGISTER function has the following syntax:

REGISTER (module_text,procedure_text,type_text,function_text,argument_text)

where:

To form the type_text argument, concatenate the code for the data type of the return value with the codes for the data type of each argument. If the function does not return a value, use the “>” character as the code for the return value.

You can also use the REGISTER function to create functions that appear in the Formula Paste Function dialog box. For example, the following macro formula creates a function named CalculateBessel using the DLL named MATH.DLL. MATH.DLL contains a procedure named BESSEL that accepts two arguments.

REGISTER("MATH.DLL","BESSEL","BIB","CalculateBessel","Index,Argument")

After Microsoft Excel calculates this REGISTER function, the CalculateBessel function appears in the Formula Paste Function dialog box, as shown in Figure 1.

Figure 1.

You can paste the CalculateBessel function onto a worksheet or a macro sheet, or you can type it directly.

Data Types

The following table lists the data types that can be passed to, and returned from, a DLL. All types use the Pascal calling convention for stack management.

The “Code” column lists the letter that you use in the REGISTER function to declare the data type. The “Data type” column describes how the data type should be declared in a DLL written in C. The “Passed arguments” column lists the types of arguments Microsoft Excel passes to the DLL. The “Return value type” column describes the type of argument Microsoft Excel expects the DLL to return.

Data declarations in C

The following table shows the correct data declarations for the Microsoft Windows and ThinkC development environments. If the data type has a type definition in WINDOWS.H, for example:

typedef char far *LPSTR;

that synonym is included in parentheses.

The K data type is a pointer to a structure. Use the following structure declarations for the K data type.

In this example, the floating point array declaration:

double Array[1];

reserves storage for only one element. This represents a single-cell array in Microsoft Excel.

Data type considerations

Code-resource functions that don’t return a value (for example, functions declared as void in C or as a SUBROUTINE in FORTRAN) must use “>” as the data type code for the return value. A DLL that doesn’t return a value usually modifies a variable that was passed to it by reference. Upon return, Microsoft Excel can use the value because it has the pointer to the value. Codes A, H, I, and J are passed by value and cannot be modified by the DLL. In these cases, the passed value is also used as the return value. For compatibility reasons, code B is parsed by all versions of Microsoft Excel as if it were passed by value and cannot be modified by the DLL, as illustrated in the next section.

To make a code-resource function volatile, use “!” as the last character in the type_text argument. Volatile functions are recalculated each time the worksheet is recalculated.

Notes

Codes E, F, and G are pointers to buffers. In earlier versions of Microsoft Excel, these buffers could be used only to return values from the called procedure. In Microsoft Excel version 3.0, the buffers are filled with the argument to the CALL function. This is useful when passing arguments by reference rather than by value.

In earlier versions of Microsoft Excel, arguments to called procedures were not coerced. In Microsoft Excel version 3.0, arguments are coerced the same way that arguments to other functions are coerced; that is, if instead of a number you give a reference to a cell that contains the number or a name defined as that number, the number value is used.

THE CALL FUNCTION

The CALL function is used to call a DLL; it can also pass arguments to the DLL if necessary. The CALL function has two forms. The first form must be used in conjunction with a REGISTER function and can be used on macro sheets only. This form has the following syntax:

CALL(register_ref,arguments)

where register_ref is the value returned by a previously executed REGISTER function; it is usually a reference to the cell containing the REGISTER function. The arguments are the arguments to be passed to the called DLL.

The second form of the CALL function can be used on either macro sheets or worksheets. This form replaces register_ref with the module_text, procedure_text, and type_text arguments from the REGISTER function:

CALL(module_text,procedure_text,type_text,arguments)

You can also combine the REGISTER and CALL functions in one formula as follows:

=CALL(REGISTER("Example","TestBool","AA"),TRUE)

If you plan to call TestBool more than once, use the REGISTER function only once and have subsequent CALL functions refer to the cell that contains the REGISTER result.

DLL EXAMPLES

The following examples illustrate each data type and function return value. You can use these examples as starting points for DLL development. The sample DLLs are written in C.

Note:

This sample code was compiled for the Windows environment using Microsoft C version 6.0 and the Microsoft Windows Software Development Kit (SDK). The sample code was compiled for the Macintosh using the ThinkC version 4.0 development environment.

Boolean by Value--Data Type A

The following routine illustrates the use of data type A, which is the Boolean data type. In this routine, the passed argument is simply negated and returned to Microsoft Excel.

You can execute the functions in Figure 2 to run this routine and to operate on a Boolean value. The REGISTER function assumes that this DLL is named TestBool and exists in the file named XLWI.DLL.

Figure 2.

Note that the AA arguments of the REGISTER function define the data type of the returned value as type A and the data type of the passed argument as type A. The result of the operation, FALSE, is returned in cell A4. When running Microsoft Excel, you can press the Command+` key combination to see the return value on the macro sheet.

IEEE Floating-Point by Reference (Macintosh)--Data Type B

IEEE Floating-Point by Value (Windows)--Data Type B

The following routine returns the floating-point value multiplied by two. Notice the difference between the B data type on the Macintosh and the B data type in Windows. On the Macintosh, the B type passes and returns a pointer; in Windows, the B type passes and returns a value. Also note that in Microsoft Excel version 2.2 or earlier, the B type passed and returned pointers for both the Macintosh and Windows environments.

You can execute the functions in Figure 3 to call this routine to pass a pointer and to operate on an IEEE floating-point number. The REGISTER function assumes that the DLL is named TestDouble and exists in the file named XLWI.DLL.

Figure 3.

The result of the macro is 2.5, which is returned in cell A8.

Zero-Terminated String by Reference--Data Type C

The following routine accepts a pointer to a zero-terminated (or null-terminated) string and returns a pointer to a string. The returned string contains a “$” character for each character in the passed string. For example, if you pass “Hello” to this function, the return value would be “$$$$$”. This assumes that both strings have the null termination (\0) at the end.

The ThinkC code includes the SETUPA4.H header file, which contains the memory management macros RememberA0, SetUpA4, and RestoreA4. These macros are required when you use static variables (in this case, static char buffer[256]) in a ThinkC code resource. For more information, see the ThinkC User’s Manual.

You can execute the functions in Figure 4 to call this routine. The REGISTER function assumes that this DLL is named TestChar and exists in the file named XLWI.DLL.

Figure 4.

The returned value, $$$$$$$$, appears in cell A12.

Pascal String by Reference--Data Type D

The following sample returns the string “Hi There.” as a Pascal string. The first byte of the Pascal string contains the number of characters in the string, which is nine in this example.

The ThinkC code includes the SETUPA4.H header file, which contains the memory management macros RememberA0, SetUpA4, and RestoreA4. These macros are required when you use static variables (in this case, static char buffer[256]) in a ThinkC code resource. For more information, see the ThinkC User’s Manual.

You can execute the functions in Figure 5 to call this routine. The REGISTER function assumes that this DLL is named TestByte and exists in the file named XLWI.DLL.

Figure 5.

The returned string appears in cell A16.

IEEE Floating-Point by Reference--Data Type E

The following routine returns a pointer to the original passed value if the value does not equal zero. If the value equals zero, the function returns a pointer to null.

You can execute the functions in Figure 6 to test this function. The REGISTER function assumes that this DLL is named TestFloatBuff and exists in the file named XLWI.DLL.

Figure 6.

The results of the operation are 1.1, returned in cell A20, and #NUM! (the pointer to null produces this error value), returned in cell A21.

Zero-Terminated String by Reference--Data Type F

The following routine returns a pointer to the zero-terminated contents of a string buffer. As with data type E, data type F causes Microsoft Excel to allocate the memory for the buffer and to pass a pointer to the DLL.

You can execute the functions in Figure 7 to call this routine. The REGISTER function assumes that this DLL is named TestZBuff and exists in the file named XLWI.DLL.

Figure 7.

The data type of the return value is type F. Data type F is listed as a string buffer in the previous description of the REGISTER function. It is similar to data type C (see the “Zero-Terminated String by Reference—Data Type C” section, earlier in this article), except that data type F passes a pointer to an uninitialized buffer instead of passing values to the DLL.

The result of the operation is Greetings, which is returned in cell A25.

Pascal String by Reference--Data Type G

This example is similar to the example for Data Type F in the previous section, except that the string length is determined by the count appearing in the first byte and not by the occurrence of the zero-termination character.

You can execute the functions in Figure 8 to call this routine and to pass the string back to a string buffer allocated by Microsoft Excel. The REGISTER function assumes that this DLL is named TestByteBuff and exists in the file named XLWI.DLL.

Figure 8.

The result of the operation is Good Day, which is returned in cell A29.

Unsigned Integer by Value--Data Type H

The following routine multiplies the passed argument by two and returns it to Microsoft Excel.

You can execute the functions in Figure 9 to call this routine to operate on an unsigned integer value. The REGISTER function assumes that this DLL is named TestUInt and exists in the file named XLWI.DLL.

Figure 9.

The return value is 44444 in cell A33.

Signed Integer by Value--Data Type I

This routine is similar to the previous routine for data type H, except that it returns a signed integer.

You can execute the functions in Figure 10 to call this routine to operate on a signed integer value. The REGISTER function assumes that this DLL is named TestInt and exists in the file named XLWI.DLL.

Figure 10.

The result of the operation is –6, which is returned in cell A37.

Unsigned Long Integer by Value--Data Type J

This routine is similar to the preceding example, except that it returns a long integer value.

You can execute the functions in Figure 11 to call this routine to operate on a long integer value. The REGISTER function assumes that this DLL is named TestLong and exists in the file named XLWI.DLL.

Figure 11.

The value returned is 44444444 in cell A41.

Floating-Point Array Structure by Reference--Data Type K

The following routine illustrates the use of the K data type, which passes a floating-point array to a DLL or returns a floating-point array to Microsoft Excel. When a range of cells is passed as an argument, Microsoft Excel converts the range of cells into a data structure containing three elements:

The number of rows (unsigned short)

The number of columns (unsigned short)

An array containing rows times columns of four-word floating-point numbers (double array[])

The argument passed to the DLL is a pointer to a floating-point array. This array represents a range of cells on a sheet. The DLL adds 1 to each cell and returns a pointer to another floating-point array that contains the result.

You can execute the functions in Figure 12 to call this routine. Notice that the ChangeK call in cells A4 through C5 must be entered as an array.

Figure 12.

After you run this macro, the values shown in Figure 13 appear on the sheet:

Figure 13.

Boolean by Reference--Data Type L

This example is similar to the example for data type A, except that the data is passed by reference.

You can execute the functions in Figure 14 to call this routine. The REGISTER function assumes that this DLL is named TestBufBool and exists in the file named XLWI.DLL.

Figure 14.

The result of this function call is the logical value FALSE, which appears in cell A54.

Signed Integer by Reference--Data Type M

This data type is similar to data type I, except that the data is passed by reference.

You can execute the functions in Figure 15 to call this routine to operate on a signed integer value. The REGISTER function assumes that this DLL is named TestBufInt and exists in the file named XLWI.DLL.

Figure 15.

The result of this function call is the integer value –6, which is returned in cell A58.

Signed Long Integer by Reference--Data Type N

This data type is similar to data type J, except that a signed long integer is passed by reference.

You can execute the functions in Figure 16 to call this routine to operate on a signed long integer value. The REGISTER function assumes that this DLL is named TestBufLong and exists in the file named XLWI.DLL.

Figure 16.

This function call returns the integer value 44444444 in cell A62.

Floating-Point Array Structure by Reference--Data Type O

The O data type is a floating-point data type that passes three pointers to a DLL. The O data type has no return value; it provides direct compatibility with FORTRAN DLLs (declared as SUBROUTINE), which pass arguments by reference. You can use the O data type with a DLL written in any programming language that can be compiled as a DLL for Microsoft Windows or as a code resource for the Apple Macintosh.

When a range of cells is passed as an argument, Microsoft Excel converts the range into three pointers:

Pointer to a 16-bit integer containing the number of rows

Pointer to a 16-bit integer containing the number of columns

Pointer to an array containing rows times columns of IEEE floating-point numbers

The following code illustrates the O data type. The three pointers passed to the DLL represent a range of cells on a sheet. The DLL approximates a squarewave function over one cycle, using a Fourier series containing seven terms, and returns the values in the cells.

Windows

SUBROUTINE SQUARE2(I, J, A)

INTEGER*2 I, J

REAL*8 A(J,I)

DOUBLE PRECISION PI

PARAMETER (PI = 3.141592654D0)

REAL Theta

do k = 1, I

Theta = 2*PI*k/I

a(1,k) = sin(Theta) + sin(3*Theta)/4.0D0 + sin(5*Theta)/6.0D0

a(1,k) = a(1,k) + sin(7*Theta)/8.0D0 + sin(9*Theta)/10.0D0

a(1,k) = a(1,k) + sin(11*Theta)/12.0D0 + sin(13*Theta)/14.0D0

a(1,k) = a(1,k) * 4 / PI

end do

END

To use this DLL, array-enter the following formula in cells A1 through A100 on a worksheet:

=CALL("square.dll","SQUARE2",">O!",$A$1:$A$100)

This is the second form of the CALL function, which can be used on a macro sheet or on a worksheet. Because data type O has no return value, you must use the “>” character in the function argument.

Next, chart the cell values returned by the DLL to see the Fourier series approximation of the squarewave (Figure 17).

Figure 17.

OPER Structure by Reference--Data Type P

Using code P as part of the type_text argument to the REGISTER function lets you send and return any Microsoft Excel data type, which can then be identified through the last two bytes of the data type. With code types A to O, you must specify a particular Microsoft Excel data type for each argument.

Note In Windows, the WORD and LPSTR types are defined in WINDOWS.H as follows:

typedef unsigned int WORD;

typedef char far *LPSTR;

The table below lists data types that can be passed by OPER data structures. The first eight bytes contain the value of the object; the last two bytes identify the type of object.

The next two types are used only as arguments. You cannot use these types for return values.

Example

The following code illustrates the P data type and the OPER structure.

Windows

typedef struct oper {

union

{

double num;

LPSTR str;

WORD bool;

WORD err;

struct

{

struct oper far *lparray;

int rows;

int columns;

} array;

} val;

WORD type;

} OPER;

typedef OPER far *LPOPER;

#define typeNum 0x0001

#define typeStr 0x0002

#define typeBool 0x0004

#define typeErr 0x0010

#define typeMulti 0x0040

#define typeMissing 0x0080

#define typeNil 0x0100

/* ------------------------------ >O */

void far pascal TestOper(a)

LPOPER a;

{

LPSTR str;

int i,items;

LPOPER lparray;

switch (a->type)

{

case typeNum:

str="\x007 number";

break;

case typeStr:

str="\x007 string";

break;

case typeBool:

str="\x008 boolean";

break;

case typeErr:

str="\x006 error";

break;

case typeMissing:

str="\x008 missing";

break;

case typeNil:

str="\x006 empty";

break;

case typeMulti:

items=a->val.array.rows*a->val.array.columns;

lparray=a->val.array.lparray;

for (i=0; i<items; i++)

{

switch ((lparray+i)->type)

{

case typeNum:

str="\x007 number";

break;

case typeStr:

str="\x007 string";

break;

case typeBool:

str="\x008 boolean";

break;

case typeErr:

str="\x006 error";

break;

case typeMissing:

str="\x008 missing";

break;

case typeNil:

str="\x006 empty";

break;

}

(lparray+i)->type=typeStr;

(lparray+i)->val.str=str;

}

return;

}

a->type=typeStr;

a->val.str=str;

}

ThinkC

typedef struct oper {

union

{

short double num;

char *str;

unsigned int bool;

unsigned int err;

struct

{

struct oper *parray;

int rows;

int columns;

} array;

} val;

unsigned int type;

} OPER;

typedef OPER *POPER;

#define typeNum 0x0001

#define typeStr 0x0002

#define typeBool 0x0004

#define typeErr 0x0010

#define typeMulti 0x0040

#define typeMissing 0x0080

#define typeNil 0x0100

/* ------------------------- >O */

pascal void main(a)

POPER a;

{

char *str;

int i,items;

POPER parray;

switch (a->type)

{

case typeNum:

str="\x007 number";

break;

case typeStr:

str="\x007 string";

break;

case typeBool:

str="\x008 boolean";

break;

case typeErr:

str="\x006 error";

break;

case typeMissing:

str="\x008 missing";

break;

case typeNil:

str="\x006 empty";

break;

case typeMulti:

items=a->val.array.rows*a->val.array.columns;

parray=a->val.array.parray;

for (i=0; i<items; i++)

{

switch ((parray+i)->type)

{

case typeNum:

str="\x007 number";

break;

case typeStr:

str="\x007 string";

break;

case typeBool:

str="\x008 boolean";

break;

case typeErr:

str="\x006 error";

break;

case typeMissing:

str="\x008 missing";

break;

case typeNil:

str="\x006 empty";

break;

}

(parray+i)->type=typeStr;

(parray+i)->val.str=str;

}

return;

}

a->type=typeStr;

a->val.str=str;

}

This sample code takes a pointer to an OPER structure that represents a cell value and returns the OPER data type that is in the cell. The Microsoft Excel macro fragment in Figure 18 shows how to call the DLL. Note that the TestOper(A54:A60) DLL call in cells A73:A79 must be array-entered.

Figure 18.

Cells A54:A60 (the argument to the array-entered TestOper call) contain the sample macros for the M and N data types, and cell A4 contains part of the sample macro for the A data type. Cell A21 contains the DLL call TestFloatBuff(0) from the E data type sample macro, which returns the #NUM! error value. These arguments are shown in Figure 19.

Figure 19.

After you run this macro successfully, the values in Figure 20 appear in the macro sheet.

Figure 20.

1This article refers to both MicrosoftÒ WindowsÔ DLLs and AppleÒ MacintoshÒ code resources as DLLs.

2Please note the important difference between the B data type on the Macintosh and the B data type in Windows: On the Macintosh, the B type passes and returns a pointer; in Windows, the B type passes and returns a value. In Microsoft Excel version 2.2 or earlier, the B type passed and returned pointers for both Macintosh and Windows.