One of the richest libraries of programming code functions is supplied by Windows itself. This function library commonly is referred to as the Windows API (Application Programming Interface). Fortunately, as a Visual Basic for Applications (VBA) programmer, you can tap into the Windows function library by using these built-in Windows functions in your own VBA modules.
Furthermore, you might discover other dynamic link libraries (DLLs) that contain functions that would be useful in your applications. These DLLs also are available to you as long as you are properly licensed to use and distribute them.
A DLL is a library of procedures that applications can link to and use at runtime. Functions contained in the Windows API and other DLLs can provide your applications with significant, added functionality. It is often much more efficient to use an external DLL to accomplish a task than to attempt to write a VBA function to accomplish the same task.
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:
This statement declares a function called GetKeyboardType
, which is located in the Windows 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.
Remember that both the function name and library name are 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
.
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.
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.
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 or can result in the function not working as expected.
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:
The code here and most of the code in this chapter is located in CHAP25EX.ACCDB
on the sample code website.
Notice that the example uses the Space$
function to store 160 spaces in the string variable strBuffer
. Actually, the Space$
function returns 160 spaces, followed by a Null
character in the strBuffer
variable.
The abGetSystemDirectory
Windows API function receives two parameters:
strBuffer
.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 path 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.
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 titled “Aliasing a Function.”
The Declare
statement for the GetSystemDirectoryA
function looks like this:
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.
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 2007 comes complete with a backup feature that makes it easier than ever to back up your databases.
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
required by ANSI versions of the API call.The sections that follow further discuss the reasons for aliasing an API function.
It is not uncommon for a DLL procedure name to contain a character that is not allowed in VBA code—for 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:
Notice that the Windows API function _lopen
begins with an underscore. You can alias the function as LOpen
for use in the Access application.
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:
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 want to call the API or the VBA GetObject
function.
A
Suffix Required by ANSIMany 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:
This Declare
statement creates an alias of FindWindow
for the ANSI function FindWindowA
.
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 one 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
).
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.
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:
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.
Some DLLs require the use of constants or user-defined types, otherwise known as structures or parameters. You must place them in the General Declarations section of your module, along with the Declare
statements you have defined.
Constants are used by many of the API functions. They provide you with an English-like way of sending required values to an API function. You use the constant as an alias for a specific value. Here’s an example:
Global Const SM_CXSCREEN = 0
Global Const SM_CYSCREEN = 1
You place the constant declarations and function declarations in the General Declarations section of a module. When the GetSystemMetrics
function is called in the following example, the SM_CXSCREEN
and SM_CYSCREEN
constants are passed as arguments to the function:
When the code in the example passes the SM_CXSCREEN
constant to the GetSystemMetrics
function, the function returns the horizontal screen resolution; when the code passes the SM_CYSCREEN
constant to the function, the code returns the vertical screen resolution.
When working with types, you first must declare the type in the General Declarations section of a module. You then can pass elements of a user-defined type, or you can pass the entire type as a single argument to the API function. The following code shows an example of a Type
declaration:
You declare the Type
structure OSVERSIONINFO
in the General Declarations section of the module, as shown in Listing 25.1.
Listing 25.1. Declaring the Type
Structure OSVERSIONINFO
in the General Declarations Section of the Module
In this listing, the statement Dim OSInfo As OSVERSIONIFO
creates a Type
variable. The entire structure is passed to the GetVersionEx
function (declared in basAPICalls
), which fills in the elements of the structure with information about the operating system. The code retrieves and stores this information into variables that it displays in a message box.
After you declare a procedure, you can call it just like any VBA function. The main issue is that you must ensure that you are passing correct values to the DLL. Otherwise, the bad call can cause your application to shut down without warning. In fact, external library calls are very tricky. You therefore should always save your work before you test the calls.
Most DLLs expect to receive standard C strings. These strings are terminated with a Null
character. If a DLL expects a Null
-terminated string, you must pass the string by value. The ByVal
keyword tells VBA to pass the string as Null
terminated.
Although you must pass strings by value, they actually are received by reference. The ByVal
keyword simply means that the string is Null
terminated. The DLL procedure actually can modify the value of the string, which can cause problems. As discussed in the “Passing String Parameters” section earlier in this chapter, if you do not preallocate space for the procedure to use, it overwrites any memory it can find, including memory currently being used by your application, another application, or even the operating system. You can avoid this problem by making the string argument long enough to accept the longest entry that you think will be placed into the parameter.
The potential uses for API functions are endless. You can use API functions to modify the System menu, obtain system information, or even switch between running applications. In fact, you can accomplish so many things using API functions that entire books are devoted to the topic. The remainder of this chapter covers several of the common uses of API functions.
Four built-in VBA functions help you manipulate the Windows Registry. They include GetAllSettings
, GetSetting
, SaveSetting
, and DeleteSetting
. These four functions allow you to manipulate and work only with a specific branch of the Registry, HKEY_CURRENT_USERSoftwareVB
, and VBA program Settings. Sometimes you need to read from or write to other parts of the Registry. This is one situation in which the Windows API can really help you out. Using the Windows RegQueryValueEx
function, you can extract information from Registry keys. Using the RegSetValueEx
function, you can write information to the Registry. The declarations for these two functions (found in the basAPICalls
module) look like this:
Before you use either function, you must first obtain a handle to the Registry key you wish to affect. This requires the RegOpenKeyEx
function:
Finally, when you are done reading from or saving to the Registry, you must use the RegCloseKey
function to close the Registry key. The declaration for the RegCloseKey
function looks like this:
Listing 25.2 shows how you can use the RegQueryValueEx
function to read from the Registry.
Listing 25.2. Using RegQueryValueEx
to Read Registry Information
You will find this code in the frmRegistry
form in the sample database. Notice that the code first retrieves a handle to the requested Registry key. It then uses the RegQueryValueEx
function to retrieve the designated value from the Registry. After the code is complete, it closes the Registry key. For example, you could request the value Last User
from the SoftwareMicrosoftOffice12.0AccessSettings
Registry key. The value stored for the MRU1
setting is displayed in the txtValue
text box.
Listing 25.3 shows how you can use the RegSetValueEx
function to write to the Registry.
Listing 25.3. Using RegSetValueEx
to Write Information to the Registry
In this listing, the routine first opens a handle to the designated Registry key. It then calls the RegSetValueEx
function, passing the handle, the value you want to modify, the type of data the key contains, and the new value. Finally, it closes the Registry key.
I generally do not make a practice of writing information to the Windows Registry. If you write to an important Registry key and make a mistake, you can render the Windows operating environment unusable. When you must write to the Windows Registry, do so sparingly and carefully.
Listing 25.3 shows you how to write to a Registry key that contains a string. To write to a Registry that expects a DWORD value, you must use the REG_DWORD
constant rather than the REG_SZ
constant.
By using Windows API calls, you can get volumes of information about the system environment, including the type of hardware on which the application is running, the amount of memory that exists or is available, and the operating system version under which the application is running. It is handy and professional to include system information in your application’s Help About box. It also is important to include this system information in your error handling and logging because such information can help you diagnose the problem. This is discussed in Chapter 17, “Error Handling: Preparing for the Inevitable.”
Figure 25.1 shows a Custom About dialog box that includes system environment information. This form uses several Windows API calls to get the system information displayed on the form.
Figure 25.1. A Custom About dialog box illustrating the capability to obtain system information from the Windows API.
Before you can call any of the DLL functions required to obtain this information, you must declare all the necessary functions to the compiler. This example accomplishes this in the General Declarations section of the module basUtils
. You must also include any constants and type structures used by the DLL calls in the General Declarations section. Listing 25.4 shows what the General Declarations section of basAPICalls
looks like.
Listing 25.4. The General Declarations Section of basAPICalls
As you can see, several type structures, constants, and Declare
statements are required to obtain all the information that appears on the form. When the form (frmSystemInformation
) is opened, all the Windows API functions are called, and the text boxes on the form are filled with the system information. The Open
event of the form frmSystemInformation
calls a subroutine called GetSysInfo
, which is shown in Listing 25.5.
Listing 25.5. The GetSysInfo
Subroutine
Now look at this subroutine in detail. The subroutine calls the function GetSystemMetrics
(aliased as abGetSystemMetrics
) three times. The first time, it is sent the constant SM_CXSCREEN
, and the second time, it is sent the constant SM_CYSCREEN
. These calls return the horizontal and vertical screen resolutions. When passed the constant SM_MOUSEPRESENT
, the GetSystemMetrics
function returns a logical True
or False
, indicating whether a mouse is present.
The GlobalMemoryStatus
API call fills in a structure with several pieces of information regarding memory. The code fills the elements of the structure with the memory load, total and available physical memory, and total and available virtual memory.
The GetSystemInfo
API call also provides you with valuable system information. It fills in a structure with several technical tidbits, including the active processor mask, the number of processors, and the processor type.
Finally, the function calls GetWindowsDirectory
, GetSystemDirectory
, and GetTempPath
. These three functions return the Windows folder, System folder, and temp file path, respectively. Notice that buffer space is preallocated before each call. Because each call returns the length of the folder name retrieved, you then take the characters on the left side of the buffer for the number of characters specified in the return value.
Often, you need to determine the types of drives available and the amount of space free on each drive. Fortunately, Windows API functions are available to help you to accomplish these tasks. The frmListDrives
form lists the type of each drive installed on the system and the amount of free space on each drive, as shown in Figure 25.2. The declarations that are required for the APIs are shown in Listing 25.6.
Figure 25.2. The frmListDrives
form showing the type of each drive installed on the system and the amount of free space on each drive.
Listing 25.6. API Declarations
The Click
event of the cmdListDrives
command button located on frmListDrives
calls a subroutine called GetDriveInfo
, sending it the txtDrives
text box. Listing 25.7 shows the GetDriveInfo
procedure.
Listing 25.7. The GetDriveInfo
Procedure
The routine loops through all available drive letters. For each drive letter, the code calls two user-defined functions: TypeOfDrive
and NumberOfBytesFree
. Listing 25.8 shows the TypeOfDrive
function.
Listing 25.8. The TypeOfDrive
Function
The TypeOfDrive
function receives a drive letter as a parameter. It calls the Windows API function GetDriveType
to determine the type of drive whose drive letter was passed to the function. The GetDriveType
function returns a numeric value that indicates the type of the specified drive. The returned value is evaluated with a case statement, and text representing the drive type is returned from the function.
The NumberOfBytesFree
function determines how many bytes are free on a particular drive, as shown in Listing 25.9.
Listing 25.9. The NumberOfBytesFree
Function
This function receives a drive letter as a parameter. It then calls the GetDiskFreeSpace
Windows API function, sending it the drive letter and several long integers. These long integers are filled in with the information required to determine the number of bytes free on the specified drive.
After the code determines the type of drive and number of bytes free, the GetDriveInfo
procedure concatenates the information with the text contained in a text box on the frmListDrives
form. If the drive specified is unavailable, the amount of available disk space is not printed.
Add an error handler to your application that displays system information. Review all the type structures and function declarations. Also review the function calls. Notice how the return values are used. Make sure that you understand how the Windows API calls make the retrieval of the information included on the system information form possible.
External libraries, referred to as dynamic link libraries (DLLs), open up the entire Windows API as well as other function libraries to your custom applications. Using external libraries, your applications can harness the power of functions written in other languages, such as C, Delphi, Visual Basic, Visual Basic .NET, or C#. In this chapter, you learned how to declare API functions, type structures, and constants, and how to call Windows API functions. Using the techniques that you learned, you can easily extend beyond the power of Access, harnessing the power of the operating system environment.
18.116.50.87