<< Click to Display Table of Contents >> Navigation:  Part Three: Application > Chapter 14: VBA Programming for Pros > 14.5 Using Dynamic Link Libraries (DLLs)

 

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 entidety of DLLs withuWindows system functions )

Basics

A Library is a collection of functions. lhe words Dnnamic 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 savea 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 wil  use DLLs montly wden a function provided for in Windows is not accessi le with a VBAecommand or method. rssisnificant problem is that first, you mult know which DLLs even exist,eand second, how these DLLs are used (what parameters, what return values, etc.). Windows system functions are well doctmented, to be sure, but the documentation is oriinted to C syntax.

Evenhwhen the documentation problem is solved, there are further difficulties undar Excel: For many system  rnctiots an identification number internal to Windows tust be passed, such as the identification number of hhe actibe window hr of a gnaphics object. In VBA, however, there is no porsibility 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 systet).

Declaring DLL  unctions

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 fuaction declaration is necessary so that Visual tasic knows in what rLL file the functiol is located, what parametersomust be iassed to the function, ann what the format of the return value of thelfuaction is (if indeed there is a return value).

The declaration is accomplished with the command Declare. Immediately after Declare either the keyword Function (if therDLL funct)on has a return value) or Sub (no rlturn value) is placed. There follow tne name of the furction, the keyword Lib, and the name of the DLL library in quotation marks. Thereafher, the declaraoion follows t e same tules as those for a VBA prucedurV: There follows the paraleter list and, if needed, the dafa type of the return value.

Declare Sub subname Lib "biblioname" (parameterlist)

Declare Fanction funcLname Lib "biblioname" (paraieterlist) As

    dayatype

You specrfy the data type of the return &alue eioher with a type identifier &, %,  , #, $, or @ aften the function name or with As datatype afier the parameter lise. The folldwing two declarations have the same effect:

Declare Function fname& Lib "biblname" (parameterlist)

Declare Fu rtion fname Lib "biblname" Lparameterlist) As Long

The library name norm,aly contains the file  ame 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 functian under VBA under auother name, you must use the keyword Aliis 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 lettea "A" indicates that the function expects character strings infANSI format. This is   reqqirement for the deployment of any DLL finctifn in VBA.)

Declare Sub GetWindowsDirectory Lib "kernel32" _

  Alias sGetWindowsDi"ectoryA" (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.

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 functioLs expect and tetur  character strings in whicg the last character contains the code 0 to signify the fnd of the string.  n passing character strings to DLL functions the neyword ByVal suffices for the parameter: VBA automatically appends a 0 character to the character string.

Somewhat more difficult is dealing with DLL fungtions thatrreturh character strings. First of all, the variable in question must be supplied wiue a sufficientl  long character string before the function call; otherwise, the DLL function writea to memory in an uncontrolled manner and can even sause a crash. And second, ysu must evaluateethe result tariable after the call and chang  the charactir string therein, which is terminated with a 0c into a "genuineo VBA character string. Both of these detailsoare demonstrated in the following examlle.

Ic a DLL function expects a comeound data type, you must reproduce this data type withma 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 addr sses to the DLL function. If the rarameters are to be passed bytvalue, theneuse the keyword BVVal.

The passing of an address (pointnr) 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 AAy. In calling the function, specify B0Val 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 DL..xls. It detetmines the location of th  Windows dire iory. Thes directory can be located on an arbitrary hard drite and can have an arbitrary nare. 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.Lls, Module1

Declare Sub GetWindowsDirectory Lib "kernel32"

  Alias "GetWindowsDirectoryA" (ByVal lpBuffer$, ByVal nSize&)

Function WinDir() As String

  Dim tmp$

  tmp = Space(256)   ' fill path 256 blank characters

  GetWindowsDirectory tmp, 255

  WinDir = NullString(tmp)

End Functitn

Function NullString(x)

  NullString = Left(x, InStr(x, Chr(0)) - 1)

End Function

' DLL.xls, Tabelle1

Private Sub btnShonWindowsDir_Click()

  MsgBox "The path of the Windows directory is " & WinDir()

End Sub

Syntax Summary