| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
One of the richest libraries of programming code functions is supplied by Windows itself. This function library is commonly referred to as the Windows API (Application Programming Interface). Fortunately, as VBA programmers, you can tap into the Windows function library, utilizing these built-in Windows functions in your own VBA modules.
Furthermore, you might discover other DLLs (Dynamic-Link Libraries) that contain functions that would be useful in your applications. These are also 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 within both the Windows API and other DLLs can provide your applications with significant added functionality. It is 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 utilize a DLL function, you must do the following:
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. User-defined functions and subroutines written in VBA are declared using Sub or Function keywords. These keywords define the procedures so that VBA can locate the routines when they are called. Functions in a DLL are declared in the same way. After you have declared 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. Declare statements can be placed in the Declarations section of a module, form, or report. A Declare statement placed in a module is immediately available to your entire application. If the Declare statement is explicitly declared as Private, it is only available to the module in which it was declared. A Declare statement placed in the General Declarations section of a form or report is available only when the form or report has been loaded. Furthermore, a Declare statement placed in the General Declarations section of a form or report can have only Private scope.
A Declare statement can be used 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 Declare statement looks like this:
Private Declare Function GetKeyboardType Lib "user32" _ (ByVal nTypeFlag As Long) As Long
This Declare statement declares a function named GetKeyboardType, which is found in the Windows 95 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 has been declared as Private.
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 the library. Most Windows API functions are contained within the library files user32.exe, gdi32.exe, and kernel32.exe.
When a parameter is passed by reference, the memory address of the argument is passed to the function. When a parameter is passed 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 is not able to function properly. If you want to pass an argument by value, the ByVal keyword must be placed in front of the argument in the Declare statement. When calling library functions, you must know both the types of arguments that 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 can even result in a General Protection Fault (GPF) error.
String parameters require special handling when being passed to DLL functions. Windows has two ways of storing strings. They are referred to as BSTR and LPSTR. Unless you are dealing with an API call specifically involving 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 that 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. The following code demonstrates this point and appropriately handles the problem:
Sub WinSysDir() Dim strBuffer As String Dim intLength As Integer Dim strDirectory As String strBuffer = Space$(160) intLength = GetSystemDirectory(strBuffer, Len(strBuffer)) strDirectory = Left(strBuffer, intLength) MsgBox strDirectory End Sub
This code, and most of the code in this chapter, can be found in CHAP29EX.MDB on your sample code CD.
Notice that the Space$ function is used to store 160 spaces into the string variable called strBuffer. Actually, the Space$ function stores 160 spaces followed by a Null character into the strBuffer variable.
The GetSystemDirectory Windows API function receives two parameters. The first is the buffer that it will fill with the name of the Windows System folder—in this case, strBuffer. The second is the length of the buffer that will be filled—in this case, Len(strBuffer). The key here is that the length of the buffer that is passed to the GetSystemDirectory function is more than sufficient to hold the name of the Windows System folder.
The GetSystemDirectory function fills the buffer and returns the length of the string that it actually found. 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 GetSystemDirectory function looks like this:
Declare Function GetSystemDirectory _ Lib "kernel32" _ (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.
As you can probably see by now, 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 your database is backed up. If the Access application terminates and your database is not properly closed, you risk the chance of damage to the database. Backing up regularly ensures that if the database becomes corrupted during testing, you can retrieve the last good version from a backup.
When you declare a function to VBA, you are given the option to alias it. An alias is a substitute name that can be used to refer to a function. There are several reasons why you might want to alias a Windows API function:
These reasons for aliasing an API function are discussed in more detail in the sections that follow.
In is not uncommon for a DLL procedure name to contain a character not allowable within VBA code. An example is 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. The following declaration is an example:
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. The function is aliased as Lopen for use within the Access application.
The DLL procedure name that you want to use might share the same name as a VBA function. This conflict can be resolved only by aliasing the DLL function. Here's an example:
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 a VBA function. When you alias the function, there is no confusion as to whether the API or VBA GetObject function is being called.
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 you would prefer to use the name of the function without the A. You can accomplish this by using an alias. Here's an example:
Declare Function FindWindow _ Lib "user32" _ Alias "FindWindowA" _ (ByVal lpClassName As Any, ByVal lpWindowsName As Any) _ As Long
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 1 byte to represent a character, limiting the number of characters to 256. The Unicode standard uses 2 bytes to represent a character, allowing up to 65,536 characters to be represented. Access uses Unicode for string manipulation, which can lead to problems with DLL calls. To overcome this problem, you should always call the ANSI version of the API function (the version of the function that ends in an A).
Sometimes you simply want to ensure that a procedure name in a library that you are creating is unique, or you might want to ensure that the code you are writing will not conflict with any libraries that 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. Here's an example:
Declare Function ABGetWindowsDirectory Lib "kernel32" _ Alias "GetWindowsDirectory" _ (ByVal lpBuffer As String, ByVal nSize As Long) As Long
This statement declares the Windows API function GetWindowsDirectory in the library kernel32. The function is aliased as GetWindowsDirectoryAB. This function was aliased to differentiate it from other calls to the GetWindowsDirectory 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. Here's an example:
Declare Function GetAppSettings _
Lib "Utilities" _
Alias "#47" () As Long
This declaration declares a function with an ordinal number 47 in the library called Utilities. It can now be referred to as GetAppSettings whenever it is called in VBA code.
Some DLLs require the use of constants or user-defined types, otherwise known as structures or parameters. These must be placed 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. Here's an example:
Global Const SM_CXSCREEN = 0 Global Const SM_CYSCREEN = 1 Declare Function GetVersionEx _ Lib "kernel32" _ Alias "GetVersionExA" _ (lpOSInfo As OSVERSIONINFO) As Boolean
The constant declarations and function declarations are placed in the General Declarations section of a module. When the GetVersionEx function is called, the SM_CXSCREEN and SM_CYSCREEN constants are passed as arguments to the function:
Sub GetScreenInfo()
MsgBox "Screen Resolution is : " & _
GetSystemMetrics(SM_CXSCREEN) & _
" By " & _
GetSystemMetrics(SM_CYSCREEN)
End Sub
When the SM_CXSCREEN constant is passed to the GetSystemMetrics function, the horizontal screen resolution is returned, and when the SM_CYSCREEN constant is passed to the function, the vertical screen resolution is returned.
In working with types, you must first declare the type in the General Declarations section of a module. You can then pass elements of a user-defined type, or you can pass the entire type as a single argument to the API function. The following is an example of a Type declaration.
Type OSVERSIONINFO dwOSVersionInfoSize As Long dwMajorVersion As Long dwMinorVersion As Long dwBuildNumber As Long dwPlatformId As Long strReserved As String * 128 End Type
The Type structure OSVERSIONINFO is declared in the General Declarations section of the module.
Function GetOSInfo()
Dim OSInfo As OSVERSIONINFO
Dim strMajorVersion As String
Dim strMinorVersion As String
Dim strBuildNumber As String
Dim strPlatformId As String
' Set the length member before you call GetVersionEx
OSInfo.dwOSVersionInfoSize = Len(OSInfo)
If GetVersionEx(OSInfo) Then
strMajorVersion = OSInfo.dwMajorVersion
strMinorVersion = OSInfo.dwMinorVersion
strBuildNumber = OSInfo.dwBuildNumber And &HFFFF&
strPlatformId = OSInfo.dwPlatformId
MsgBox "The Major Version Is: " & strMajorVersion & Chr(13) & Chr(10) & _
"The Minor Version Is: " & strMinorVersion & Chr(13) & Chr(10) & _
"The Build Number Is: " & strBuildNumber & Chr(13) & Chr(10) & _
"The Platform ID Is: " & IIf(strPlatformId = 1, "Win 95", _
"Win NT") & Chr(13) & Chr(10)
End If
End Function
In this code, the statement Dim OSInfo As OSVERSIONIFO creates a Type variable. The entire structure is passed to the GetVersionEx function. The GetVersionEx function fills in the elements of the structure with various information about the operating system. This information is then retrieved and stored into variables that are displayed in a message box.
As you might imagine, Declare statements, constant declarations, and Type structures can be time-consuming and difficult to add to your Code modules. Fortunately, the Windows API Text Viewer, a tool that ships with the Access Developer's Toolkit, helps you to complete these tasks. It makes it easy for you to add Declare statements, types, and constants required by the Windows API function calls to your code. The Windows API Text Viewer can be accessed through the Windows 95 Start menu or by using a desktop shortcut. When you first launch the Windows API Text Viewer, it appears as in Figure 29.1. You can load either a text file or a database file containing declares, types, and constants.
Figure 29.1. The Windows API Text Viewer.
The Access Developer's Toolkit ships with a file called WIN32API.TXT. This file can be loaded and browsed so that you can easily obtain Declare statements, Type structures, and constants. To load the WIN32API.TXT file into the Windows API Text Viewer, follow four steps:
Figure 29.2. The Select a Text API File dialog.
Figure 29.3. A message offering conversion of a text file to an Access database.
Figure 29.4. The New Database dialog enables you to select a name for the database.
After a text file has been converted to a database, you should load the database each time you use the Windows API Text Viewer. To load the database file, take two steps:
Figure 29.5. The Select a Jet Database dialog enables you to specify the database you want to load into the Text Viewer.
Regardless of whether you have loaded a text or database file, the Windows API Text Viewer appears as in Figure 29.6. Notice that all the declares for the 32-bit API appear in the Available Items list box. Select each Declare statement that you want to add to your module and click Add. You can use the API Type drop-down to view and select types or constants. In Figure 29.7, the GetVersionEx and GetWindow declares have been added to the Selected Items list. The SM_CXSCREEN and SM_CYSCREEN constants, as well as the OSVERSIONINFO type, have also been added. Perform three steps to add the selected items to a module:
Figure 29.6. The Windows API Text Viewer after a file has been loaded.
Figure 29.7. The Windows API Text Viewer with several items selected.
Figure 29.8. A module after selected items have been pasted into it.
After a procedure is declared, 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 could cause your application to shut down without warning. In fact, external library calls are by nature very tricky. This means that you 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 are actually received by reference. The ByVal keyword simply means that the string is Null-terminated. The DLL procedure can actually modify the value of the string, which can mean problems. As discussed in the "Passing String Parameters" section of 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. This problem can be avoided by making the string argument long enough to accept the longest entry that you foresee being placed into the parameter.
If you were familiar with the 16-bit API, you need to be aware of some changes in working with the 32-bit API. These changes can cause you significant grief if you are not aware of them:
The potential uses for API calls are endless. You can use API calls to modify the System menu, obtain system information, or even switch between running applications. In fact, you can accomplish so many things using API calls that entire books are devoted to the topic. Several of the common uses of API calls are covered in the remainder of this chapter.
Using Windows API calls, you can obtain volumes of information about the system environment. Such information includes the type of hardware the application is running on, the amount of memory that exists or is available, and the operating system version that the application is running under. This is just a sampling of the system environment information that can be obtained via the Windows API.
It is handy and professional to include system information in your application's Help About box. It is also important to include this system information in your error handling and logging because such information can assist you in diagnosing the problem. This is discussed in Chapter 17, "Handling Those Dreaded Runtime Errors."
The form shown in Figure 29.9 is a custom About dialog that includes information about the system environment. This form utilizes several Windows API calls to obtain the system information displayed on the form.
Figure 29.9. A custom About dialog illustrating the capability to obtain system information from the Windows API.
Before any of the DLL functions required to obtain this information can be called, all the functions need to be declared to the compiler. This is done in the General Declarations section of the module basUtils. Any constants and Type structures used by the DLL calls also must be included in the General Declarations section. The General Declarations section of basUtils looks like this:
Option Compare Database Option Explicit Public Const MAX_PATH = 160 Declare Function abGetVersionEx _ Lib "kernel32" _ Alias "GetVersionExA" _ (lpOSInfo As OSVERSIONINFO) As Boolean Type OSVERSIONINFO dwOSVersionInfoSize As Long dwMajorVersion As Long dwMinorVersion As Long dwBuildNumber As Long dwPlatformId As Long strReserved As String * 128 End Type 'The function GetVersionEx gets information about ' 'the version of operating system that is currently ' 'running. The information is filled into the type 'structure OSVERSIONINFO. Const SM_CXSCREEN = 0 Const SM_CYSCREEN = 1 Const SM_MOUSEPRESENT = 19 Declare Function abGetSystemMetrics _ Lib "user32" _ Alias "GetSystemMetrics" _ (ByVal nIndex As Long) As Long 'The GetSystemMetrics function utilizes three constants to 'determine whether a mouse is present, and to determine 'the width and height of the screen. Type MEMORYSTATUS dwLength As Long dwMemoryLoad As Long dwTotalPhys As Long dwAvailPhys As Long dwTotalPageFile As Long dwAvailPageFile As Long dwTotalVirtual As Long dwAvailVirtual As Long End Type Declare Sub abGlobalMemoryStatus _ Lib "kernel32" _ Alias "GlobalMemoryStatus" _ (lpBuffer As MEMORYSTATUS) 'The GlobalMemoryStatus function retrieves information 'about current available memory. It points to a type 'structure called SYSTEM_INFO, filling in its elements 'with relevant memory information. Type SYSTEM_INFO dwOemID As Long dwPageSize As Long lpMinimumApplicationAddress As Long lpMaximumApplicationAddress As Long dwActiveProcessorMask As Long dwNumberOrfProcessors As Long dwProcessorType As Long dwAllocationGranularity As Long dwReserved As Long End Type Declare Sub abGetSystemInfo Lib "kernel32" _ Alias "GetSystemInfo" _ (lpSystemInfo As SYSTEM_INFO) 'The GetSystemInfo function returns information about 'the system. It fills in the type structue SYSTEM_INFO 'with relevant information about the system. Declare Function abGetWindowsDirectory _ Lib "kernel32" _ Alias "GetWindowsDirectoryA" _ (ByVal lpBuffer As String, _ ByVal nSize As Long) As Long 'The function GetWindowsDirectory retrieves the name of the 'directory within which windows is running Declare Function abGetSystemDirectory _ Lib "kernel32" _ Alias "GetSystemDirectoryA" _ (ByVal lpBuffer As String, _ ByVal nSize As Long) As Long 'The GetSystemDirectory function retrieves the name of the 'directory in which the Windows system files reside. Declare Function abGetTempPath _ Lib "kernel32" _ Alias "GetTempPathA" _ (ByVal nBufferLength As Long, _ ByVal lpBuffer As String) As Long 'The GetTempPath function retrieves the name of the 'directory where temporary files are stored
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 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 calls a subroutine called GetSysInfo. The GetSysInfo procedure looks like this:
Sub GetSysInfo(frmAny As Form)
Dim intMousePresent As Integer
Dim strBuffer As String
Dim intLen As Integer
Dim MS As MEMORYSTATUS
Dim SI As SYSTEM_INFO
frmAny.txtScreenResolution = abGetSystemMetrics(SM_CXSCREEN) & _
" By " & abGetSystemMetrics(SM_CYSCREEN)
intMousePresent = CBool(abGetSystemMetrics(SM_MOUSEPRESENT))
frmAny.txtMousePresent = IIf(intMousePresent, "Mouse Present", _
"No Mouse Present")
'Set the length member before you call GlobalMemoryStatus
MS.dwLength = Len(MS)
abGlobalMemoryStatus MS
frmAny.txtMemoryLoad = MS.dwMemoryLoad & "%"
frmAny.txtTotalPhysical = Format(Fix(MS.dwTotalPhys / 1024), "###,###") _
& "K"
frmAny.txtAvailablePhysical = Format(Fix(MS.dwAvailPhys / 1024), "###,###") _
& "K"
frmAny.txtTotalVirtual = Format(Fix(MS.dwTotalVirtual / 1024), "###,###") _
& "K"
frmAny.txtAvailableVirtual = Format(Fix(MS.dwAvailVirtual / 1024), _
"###,###") & "K"
abGetSystemInfo SI
frmAny.txtProcessorMask = SI.dwActiveProcessorMask
frmAny.txtNumberOfProcessors = SI.dwNumberOrfProcessors
frmAny.txtProcessorType = SI.dwProcessorType
strBuffer = Space(MAX_PATH)
intLen = abGetWindowsDirectory(strBuffer, MAX_PATH)
frmAny.txtWindowsDir = Left(strBuffer, intLen)
strBuffer = Space(MAX_PATH)
intLen = abGetSystemDirectory(strBuffer, MAX_PATH)
frmAny.txtSystemDir = Left(strBuffer, intLen)
strBuffer = Space(MAX_PATH)
intLen = abGetTempPath(MAX_PATH, strBuffer)
frmAny.txtTempDir = Left(strBuffer, intLen)
End Sub
Let's take a look at this subroutine in detail. The subroutine calls the function GetSystemMetrics. The GetSystemMetrics function is called 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. The GetSystemMetrics function, when passed the constant SM_MOUSEPRESENT, 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 elements of the structure are filled 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, number of processors, and the processor type.
Next, your 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.
At times you might want to prevent the user from closing a form using either the System menu or the Close button (x). This can be accomplished using the RemoveMenu API function call. If you want to reset the menu, you use the GetSystemMenu API function call. The declarations for these two function calls are
Declare Function abGetSystemMenu _ Lib "user32" _ Alias "GetSystemMenu" _ (ByVal hwnd As Long, ByVal bRevert As Long) _ As Long 'The GetSystemMenu function resets the System Menu Declare Function abRemoveMenu _ Lib "user32" _ Alias "RemoveMenu" _ (ByVal hMenu As Long, ByVal nPosition As Long, _ ByVal wFlags As Long) _ As Long 'The RemoveMenu function removes an item 'from the System Menu Public Const MF_BYPOSITION = &H400&
After the functions have been declared, you can call them like this:
Private Sub cmdHideClose_Click() Dim lngMenu As Long Dim lngTemp As Long 'Get Handle to System Menu of Current Window lngMenu = abGetSystemMenu(Me.hwnd, False) 'Remove the Sixth Item from the Menu (Separator Bar) lngTemp = abRemoveMenu(lngMenu, 5, MF_BYPOSITION) 'Remove the Sixth Item from the Menu (Close Menu Item) lngTemp = abRemoveMenu(lngMenu, 5, MF_BYPOSITION) End Sub
The Click event of the cmdHideClose command button gets a handle to the System menu of the current window. The parameter of False indicates that the menu can be modified. The RemoveMenu function is passed the handle to the window and the number of the window element that you want to remove. Both the separator bar and Close menu items are removed.
Private Sub cmdResetMenu_Click() Dim lngMenu As Long lngMenu = abGetSystemMenu(Me.hwnd, True) End Sub
To reset the menu, the GetSystemMenu function is called with the handle to the current window as the first argument and the value of True, indicating that the menu will be returned to its default state.
You can add some polish to the time and billing application by adding a custom About form to the application. You can add the form now, and integrate it into the application in Chapter 35, "Distributing Your Application." The frmSystemInformation form has been added to the application and the appropriate declares, constants, and types have been added to basWinAPI. The form and API functionality could also be added to a library so that they would be available to all applications.
External libraries, referred to as dynamic-link libraries, 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, or Visual Basic 4.