Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Declaring an External Function to the Compiler

To use a DLL function, you must perform the following steps in order:

The VBA language is not intrinsically aware of the functions available in external libraries. Declaring a DLL function means making the VBA compiler aware of the name of the function, the library it is located in, the parameters it expects to receive, and the values it expects to return.

If you do not properly declare the library function to the VBA compiler, you receive an error message stating Sub or Function Not Defined. You declare user-defined functions and subroutines written in VBA using Sub or Function keywords. These keywords define the procedures so that VBA can locate the routines when you call them. You declare functions in a DLL in the same way. After you declare a DLL function to the compiler, Access knows where to locate it, and you can use it throughout your application.

You declare an external function to VBA using a Declare statement. You can place Declare statements in the Declarations section of a standard module, a standalone class module, or the class module behind a form or report. A Declare statement placed in a standard module is immediately available to your entire application. If you explicitly declare the Declare statement as private, it is available only to the module in which you declared it. A Declare statement that you place in the General Declarations section of a standalone class module or the class module behind a form or report is available only after you load the form or report or after you instantiate the class. Furthermore, a Declare statement placed in the General Declarations section of a standalone class module or the module behind a form or report can have only private scope.

You can use a Declare statement to declare both subroutines and functions. If the procedure returns a value, you must declare it as a function. If it does not return a value, you must declare it as a subroutine.

A sample Declare statement looks like this:

Private Declare Function GetKeyboardType Lib "user32" _
(ByVal nTypeFlag As Long) As Long

This statement declares a function called GetKeyboardType, which is located in the Windows 9.x or Windows NT System folder in a DLL file called user32. It receives a long integer parameter by value and returns a long integer. Notice that this function was declared as private.

NOTE

Remember that the function name and library name are both case-sensitive. Unless you explicitly include the path as part of the Declare statement, the default system path, the Windows folder, and the Windows System folder are all searched for in the library. Most Windows API functions are contained within the library files user32.dll, gdi32.dll, and kernel32.dll.

CAUTION

Do not include unnecessary Declare statements in your applications. Each Declare statement consumes memory, whether or not you use the declaration. A large number of unused Declare statements can dramatically increase the amount of memory and resources required by your application.

Passing Parameters to DLL Functions

You pass parameters to a DLL function in the same way you pass them to a VBA routine. The only difference is that it is very important that you pass the parameters by reference or by value, as appropriate, and that you always pass the correct data type for each argument. Sending the correct data type means that, if the function expects a long integer value, you shouldn't send a double. Doing so can make your application unstable. The next section covers passing by reference versus passing by value.

Passing by Reference Versus Passing by Value

When you pass a parameter by

reference , the memory address of the argument is passed to the function. When you pass a parameter by

value , the actual value of the argument is passed to the function. Unless explicitly told otherwise, VBA passes all parameters by reference. Many library functions expect to receive parameters by value. If such a function is passed a reference to a memory location, it cannot function properly. If you want to pass an argument by value, you must place the ByVal keyword in front of the argument in the Declare statement. When calling library functions, you must know the types of arguments a function expects to receive and whether the function expects to receive the parameters by reference or by value. Passing an argument by reference rather than by value, or passing the incorrect data type for an argument, can cause your system to become unstable, and even can result in a General Protection Fault (GPF) or illegal operation.

Passing String Parameters

String parameters require special handling when being passed to DLL functions. Windows has two ways of storing strings: the BSTR and LPSTR formats. Unless you are dealing with an API call specifically involving object linking and embedding (OLE), the string you are passing to the function is stored in the LPSTR format. DLL functions that receive strings in the LPSTR format cannot change the size of the string they are passed. This means that, if a DLL function is passed a small string that it must fill in with a large value, the function simply overwrites another area of memory with the extra characters. This usually results in a GPF or illegal operation. The following code demonstrates this point and handles the error that is generated:

Sub WinSysDir()
Dim strBuffer As String
Dim intLength As Integer
Dim strDirectory As String
strBuffer = Space$(160)
intLength = abGetSystemDirectory(strBuffer, Len(strBuffer))
strDirectory = Left(strBuffer, intLength)
MsgBox strDirectory
End Sub

NOTE

The code here and most of the code in this chapter is located in CHAP23EX.MDB on your sample code CD-ROM.

Notice that the example uses the Space$ function to store 160 spaces in the string variable strBuffer. Actually, the Space$ function stores 160 spaces, followed by a Null character in the strBuffer variable.

The abGetSystemDirectory Windows API function receives two parameters:

• The buffer that it will fill with the name of the Windows System folderin this case, strBuffer.

• The length of the buffer that will be filledin this case, Len(strBuffer). The key here is that the example assumes that the length of the buffer that is passed to the GetSystemDirectoryA function is more than sufficient to hold the name of the Windows System folder.

The GetSystemDirectoryA function fills the buffer and returns the length of the string that it finds. By looking at the left intLength number of characters in the strBuffer variable, you can determine the actual location of the Windows System folder.

NOTE

The abGetSystemDirectory function name is an alias for the real function name, which is GetSystemDirectoryA. To learn more about aliases, refer to the section of this chapter entitled "Aliasing a Function."

The Declare statement for the GetSystemDirectoryA function looks like this:

Declare Function abGetSystemDirectory _
Lib "kernel32" _
Alias "GetSystemDirectoryA"
(ByVal lpBuffer As String, ByVal nSize As Long) _
As Long

Notice the ByVal keyword that precedes the lpBuffer parameter. Because the ByVal keyword is used, Visual Basic converts the string from BSTR to LPSTR format by adding a Null terminator to the end of the string before passing it to the DLL function. If the ByVal keyword is omitted, Visual Basic passes a pointer to the function where the string is located in memory. This can cause serious problems, such as database corruption.

CAUTION

Windows API calls are fraught with potential danger. To reduce the chances of data loss or database corruption, always save your work before testing a procedure containing an external function call. If the Access application terminates, at least you won't lose your work. In addition, always make sure that you back up your database. If the Access application terminates and you do not close your database properly, you risk damaging the database. Regularly backing up ensures that if the database becomes corrupted during testing, you can retrieve the last good version from a backup. Fortunately, Access 2003 comes complete with a backup feature that makes it easier than ever to back up your databases.

Aliasing a Function

When you declare a function to VBA, you are given the option to alias it, as in the preceding function. To

alias means to refer to a function by a substitute name. You might want to alias a Windows API function for several reasons:

• A DLL procedure has a name that includes an invalid character.

• A DLL procedure name is the same as a VBA keyword.

• You want to omit the A required by ANSI versions of the API call.

• You want to ensure that you have a unique procedure name in an Access library or application.

• You want to call a DLL procedure referenced by an ordinal number.

• You want to give your API functions a distinctive prefix to prevent conflicts with API declarations in other modules or add-ins.

The sections that follow further discuss the reasons for aliasing an API function.

Function Calls and Invalid Characters

It is not uncommon for a DLL procedure name to contain a character that is not allowed in VBA codefor example, a DLL procedure that begins with an underscore (_). VBA does not allow a procedure name to begin with an underscore. To use the DLL function, you must alias it, as this example shows:

Declare Function LOpen _
Lib "kernel32" _
Alias "_lopen" _
(ByVal lpPathName As String, ByVal ReadWrite As Long) _
As Long

Notice that the Windows API function _lopen begins with an underscore. You can alias the function as LOpen for use in the Access application.

DLL Functions with Duplicate Names

The DLL procedure name you want to use might share the same name as a VBA keyword. You can resolve this conflict only by aliasing the DLL function. The following code aliases a DLL function:

Declare Function GetObjectAPI _
Lib "gdi32" _
Alias "GetObject" _
(ByVal hObject As Long, _
ByVal nCount As Long, _
lpObject As Any) As Long

The GetObject function is part of the Windows API and is also a VBA function. When you alias the function, there is no confusion as to whether you wish to call the API or the VBA GetObject function.

Eliminating the "A" Suffix Required by ANSI

Many API function calls have both ANSI and Unicode versions. The ANSI versions of the functions end with an "A." You might want to call the ANSI version of a function, but prefer to use the name of the function without the "A." You can accomplish this by using an alias, as this code shows:

Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As Any, ByVal lpWindowName As String) As Long

This Declare statement creates an alias of FindWindow for the ANSI function FindWindowA.

NOTE

Unicode is a standard developed by the International Standards Organization (ISO). It was developed to overcome the 256-character limit imposed by the ANSI character standard. The ANSI standard uses only 1 byte to represent a character, limiting the number of characters to 256. This standard uses two bytes to represent a character, allowing up to 65,536 characters to be represented. Access uses Unicode for string manipulation, which can lead to conversion problems with DLL calls. To overcome this limitation, you always should call the ANSI version of the API function (the version of the function that ends with an "A").

Unique Procedure Names in an Access Library or Module

Sometimes you simply want to ensure that a procedure name in a library you are creating is unique, or you might want to ensure that the code you are writing will not conflict with any libraries you are using. Unless you use the Private keyword to declare each procedure, external function declarations are global throughout Access's memory space. This can lead to potential conflicts because Access does not allow multiple declarations of the same external routine. For this reason, you might want to place a unique identifier, such as your initials, at the beginning or end of the function declaration, as in this example:

Declare Function ABGetWindowsDirectory Lib "kernel32" _
Alias "GetWindowsDirectoryA" _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long

This statement declares the Windows API function GetWindowsDirectoryA in the library kernel32. The function is aliased as ABGetWindowsDirectory. This function was aliased to differentiate it from other calls to the GetWindowsDirectoryA function that might share this procedure's scope.

Calling Functions Referenced with Ordinal Numbers

Every DLL procedure can be referenced by an ordinal number in addition to its name. In fact, some DLLs use only ordinal numbers and do not use procedure names at all, requiring you to use ordinal numbers when declaring the procedures. When you declare a function referenced by an ordinal number, you should declare the function with the Alias keyword, as in this example:

Declare Function GetAppSettings _
Lib "Utilities" _
Alias "#47" () As Long

This code declares a function with an ordinal number 47 in the library called Utilities. You can now refer to it as GetAppSettings whenever you call it in VBA code.