14.5 Using Dynamic Link Libraries (DLLs)

Dynamic link libraries are libraries of functions. With DLLs you have access to almost all Windows system functions and can even ”if necessary ”replace timecritical VBA procedures with your own C routines in the form of a DLL. The following sections are restricted to a discussion of how the so-called API functions in VBA can be invoked. (API stands for Application Programming Interface and denotes the entirety of DLLs with Windows system functions.)

Tip	The use of DLL functions requires a great amount of background knowledge about the inner workings of Windows and about Windows programming that cannot be supplied in this section.

Basics

A Library is a collection of functions. The words Dynamic Link attached thereto indicate that the library is loaded only when it is actually needed. Loading proceeds automatically when a function from the DLL is used.

DLLs are usually saved as *.dll files. DLLs are located either in the Windows system folder (with DLLs common to all programs) or directly in the folder of a program (if the DLL is conceived for that program alone).

Many of the system functions in Windows are contained in DLLs. The three most important libraries are the GDI library (graphics functions), the User library (window, menus , mouse), and the Kernel library (memory management).

In addition to these system functions (whose DLL files come with Microsoft Windows) there exist application-specific DLLS. These DLLs have been created for a specific program. In the Office directory, for example, there are many such DLLs. However, there DLLs are of little interest to application programmers to the extent that they are seldom publicly documented. Thus you do not know what functions are contained there and how they are used. (Moreover, you can access almost all Office functions much more conveniently with VBA-compatible object libraries.)

If you work with the programming language C or C++, you can program your own DLLs. This has the advantage that time-critical VBA procedures can be replaced with very efficient C code. Perhaps you are thinking that you can write the entire program in C. That would entail an enormous effort. With DLLs you can unite the advantages of VBA and C in a single program: Fast program development of 95 percent with VBA with time-critical special functions programmed in C.

Problems and Limitations

You will use DLLs mostly when a function provided for in Windows is not accessible with a VBA command or method. A significant problem is that first, you must know which DLLs even exist, and second, how these DLLs are used (what parameters, what return values, etc.). Windows system functions are well documented, to be sure, but the documentation is oriented to C syntax.

Even when the documentation problem is solved , there are further difficulties under Excel: For many system functions an identification number internal to Windows must be passed, such as the identification number of the active window or of a graphics object. In VBA, however, there is no possibility to determine the identification numbers of Excel objects. Thus it is impossible to call many elementary DLL functions (such as determining the definition of the graphics system).

Declaring DLL Functions

In principle it is easy to invoke DLL functions in VBA programs: You simply declare the function and then call it as you would any other VBA procedure. However, you will at once determine that the declaration of DLL functions is not always so easy.

A function declaration is necessary so that Visual Basic knows in what DLL file the function is located, what parameters must be passed to the function, and what the format of the return value of the function is (if indeed there is a return value).

The declaration is accomplished with the command Declare . Immediately after Declare either the keyword Function (if the DLL function has a return value) or Sub (no return value) is placed. There follow the name of the function, the keyword Lib , and the name of the DLL library in quotation marks. Thereafter, the declaration follows the same rules as those for a VBA procedure: There follows the parameter list and, if needed, the data type of the return value.

 Declare Sub subname Lib "biblioname" (parameterlist) Declare Function functname Lib "biblioname" (parameterlist) As     datatype

You specify the data type of the return value either with a type identifier &, %, !, #, $, or @ after the function name or with As datatype after the parameter list. The following two declarations have the same effect:

 Declare Function fname& Lib "biblname" (parameterlist) Declare Function fname Lib "biblname" (parameterlist) As Long

The library name normally contains the file name of the DLL, for example, Shell32.dll . An exception is the system libraries "GDI32," "User32," and "Kernel32," whose names are given without the identifier *.dll .

If you wish to save a function under VBA under another name, you must use the keyword Alias in the declaration. In the example below the DLL function GetWindowsDirectoryA is declared in such a way that it can be used in VBA under the abbreviated name GetWindowsDirectory . (The additional letter "A" indicates that the function expects character strings in ANSI format. This is a requirement for the deployment of any DLL function in VBA.)

 Declare Sub GetWindowsDirectory Lib "kernel32" _   Alias "GetWindowsDirectoryA" (parameterlist)

As soon as a DLL function is actually used, Windows searches the DLL file in the Windows directory, in the Windows system directory, and in the Excel directory. If your DLL is located in none of these directories, you must specify the exact library name, say, "C:\Test\Mine.dll" . Note that DLLs that are located in the same directory as that in which the Excel file will be located only if the exact path is specified.

The Parameter List of the DLL Declaration

You must supply the data types of the parameters of the DLL function and the type of parameter passing in the parameter list. Many difficulties arise from the differing data types between VBA and the programming language C, for which the DLL functions are usually conceived.

VISUAL BASIC	C
ByVal x As Byte	BYTE x
x As Byte	LPBYTE x
ByVal x As Integer	short x
x As Integer	short far *x
ByVal x As Long	LONG x
x As Long	LPLONG x
ByVal x As Single	float x
x As Single	float far *x
ByVal x As Double	double x
x As Double	double far *x

It is true, in general, that most DLL functions expect values, while VBA usually passes a pointer. For this reason numerical parameters must be declared ByVal almost without exception.

With character strings the main problem is that the DLL functions expect and return character strings in which the last character contains the code 0 to signify the end of the string. In passing character strings to DLL functions the keyword ByVal suffices for the parameter: VBA automatically appends a 0 character to the character string.

Somewhat more difficult is dealing with DLL functions that return character strings. First of all, the variable in question must be supplied with a sufficiently long character string before the function call; otherwise , the DLL function writes to memory in an uncontrolled manner and can even cause a crash. And second, you must evaluate the result variable after the call and change the character string therein, which is terminated with a 0, into a " genuine " VBA character string. Both of these details are demonstrated in the following example.

If a DLL function expects a compound data type, you must reproduce this data type with a Type instruction. Many DLL functions are capable of processing variable data types. Such parameters should be declared with the data type As Any . VBA then does no automatic data type checking and when called passes along the given parameters as addresses to the DLL function. If the parameters are to be passed by value, then use the keyword ByVal .

The passing of an address ( pointer ) causes few problems, since VBA normally passes all parameters by reference, using 32-bit-far addressing (the standard address format for Windows). If you wish to pass a null pointer to a DLL function, you must set the data type of this parameter in the declaration with As Any . In calling the function, specify ByVal 0& .

Calling DLL Functions

Calling a DLL function follows (given that the function has previously been declared) in the same way as for normal procedures.

 Dim tmp$ tmp = Space(256) ' filled with 256 characters GetWindowsDirectory tmp, 255

Example: Determine Windows Directory

The following example can be found in DLL.xls . It determines the location of the Windows directory. This directory can be located on an arbitrary hard drive and can have an arbitrary name. Therefore, in the kernel library there is the function GetWindowsDirectory that determines the path of this directory.

Before this function can be used it must be defined with Declare . The actual function call takes place in WinDir . There the variable tmp is first filled with 256 blank characters and then passed to GetWindowsDirectory . The DLL function returns a 0-terminated character string; that is, the actual length of the character string must be determined up to this 0 character. To accomplish this the function NullString is called, which removes all characters beyond the 0 character.

 ' DLL.xls, Module1 Declare Sub  GetWindowsDirectory  Lib "kernel32"   Alias "GetWindowsDirectoryA" (ByVal lpBuffer$, ByVal nSize&) Function  WinDir  () As String   Dim tmp$   tmp = Space(256)   ' fill with 256 blank characters   GetWindowsDirectory tmp, 255   WinDir = NullString(tmp) End Function Function  NullString  (x)   NullString = Left(x, InStr(x, Chr(0)) - 1) End Function ' DLL.xls, Tabelle1 Private Sub  btnShowWindowsDir  _Click()   MsgBox "The path of the Windows directory is " & WinDir() End Sub

Tip

The above example is of a strictly pedagogical nature. The Windows directory can be found much more easily, namely, with the method Environ("windir") of the VBA library or with the method GetSpecialFolder (WindowsFolder), which is defined for the object FileSystemObject of the Microsoft Scripting library (see also Chapter 5).

Syntax Summary

DLL FUNCTIONS
Declare Sub subname _	declare DLL function without return value
Lib "dllname" (parameterlist) Declare Function funcname _	DLL function with return value
Lib "dllname" (paralist) As datatype Declare Sub/Function vbname _Lib "dllname" Alias "dllfnname"	DLL function, where the function name DLL in the and the name in VBA code differ