Chapter 25. Exploiting the Power of the Windows API

IN THIS CHAPTER

• Why This Chapter Is Important
• Declaring an External Function to the Compiler
• Working with Constants and Types
• Calling DLL Functions: Important Issues
• Using API Functions
• Practical Examples: Using Windows API Functions in Your Applications

Why This Chapter Is Important

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.

Declaring an External Function to the Compiler

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

1. Declare the function to the VBA compiler.
2. Call the function.
3. Use the return value.

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.

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 or can result in the function not working as expected.

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:

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:

• The buffer that it will fill with the name of the Windows System folder—in this case, strBuffer.
• The length of the buffer that will be filled—in 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 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 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.

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 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.

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:

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.

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:

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

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:

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.

Working with Constants and Types

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.

Working with Constants

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.

Working with Types

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.

Calling DLL Functions: Important Issues

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.

Using API Functions

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.

Manipulating the Windows Registry

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.

Getting Information About the Operating Environment

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.

Determining Drive Types and Available Drive Space

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.

Practical Examples: Using Windows API Functions in Your Applications

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.

Summary

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.