Using Microsoft Windows DLLs and the Windows API in Microsoft Office Development

Presented by: Ken Getz

Ken Getz is a Senior Consultants with MCW Technologies, a Microsoft Solution Provider focusing on Microsoft® Visual Basic®, and the Microsoft® Office and Microsoft® BackOffice™ suites of products. He recently completed "VBA Developer's Handbook" (co-authored with Mike Gilbert), and "Access 97 Developer's Handbook" (co-authored with Paul Litwin and Mike Gilbert), both for Sybex, Inc.

Portions of the content of this session were excerpted from the following books, with permission of the publishers:

Access 95 How-To

Ken Getz and Paul Litwin

©1996, Waite Group Press, Inc..

VBA Developer's Handbook

Ken Getz and Mike Gilbert

©1997, Sybex, Inc.

Email: Error! Reference source not found.

External Functions Provide Extensibility

No matter how powerful the application language, most high-level Windows applications allow some mechanism for calling procedures that exist externally to the product. The designers of the application can't think of every possible use for their product, and by allowing developers access to external function libraries, they make their product extensible. Microsoft® Visual Basic® for Applications (VBA) allows you to call external functions, thereby extending that capabilities of the product.

Introducing the Dynamic Link Library (DLL)

In order to call an external procedure from within VBA, you'll need to refer to a procedure that exists in a specific type of external library. These libraries are usually files with the Dynamic Link Library extension, .DLL, though this isn't always the case (many of the Windows DLLs have an EXE extension). These libraries can be user-supplied, vendor supplied (the VBA DLLs that supply its functionality), or part of Windows itself (USER32.DLL, GDI32.DLL, etc.). The Windows DLLs provide a wealth of functionality that you can call into, armed with the necessary documentation. Though there are some limitations that will be discussed here, you should be able to call almost any DLL function that a C or C++ programmer could call.

Windows Itself is Composed Mostly of DLLs

Although you probably view Windows as a large executable program (if you think about these things at all), it is mostly composed of DLLs that other applications can call. Every Windows application requests information and action from Windows itself. Three major pieces of Windows are USER32.DLL, KERNEL32.DLL and GDI32.DLL. Though all three files used the EXE extension in Win3.x, they were actually DLLs, chock-full of procedures that every Windows application calls. Your VBA solutions can also call these procedures, once you know how to play by the rules.

Not All Useful DLLs are Part of Windows

Though Windows does provide a wealth of functionality in its DLLs, that doesn't mean that you won't find other DLLs just as useful. You can use external DLLs to solve many programming problems: You can write your own VBA-callable DLL, using C, Pascal, Delphi, Assembly Language, or any other language that can create a DLL. Visual Basic cannot be used for this task, at the moment. (Of course, there is an interesting add-on product for Visual Basic that allows you to create DLLs, but that's another story...)

Static vs. Dynamic Linking

To accurately understand the concept involved in using external libraries, you must first have a basic idea of what dynamic linking entails.

The old (MS-DOS) way

In the MS-DOS world, function libraries are almost always statically linked. That is, when the programmer builds the executable program, shared library functions that that program uses are copied from a shared library into the executable itself. That means that every MS-DOS program that uses the standard C library functions carts around the same chunks of code. If the programmer receives an update to the function library, she must rebuild all of the applications that use the function included in that library.

The new (Windows and OS/2) way

Under Windows and OS/2, applications can use dynamic linking. In this situation, function libraries are not copied into the application itself, but are stored as separate files. When you attempt to run the executable program, the operating environment loads the appropriate libraries and extracts the correct functions for you while the program is running. This way, if the library changes, the programmer can just distribute the changed library files, without having to rebuild the entire application. If you're a Visual Basic programmer, you're probably well-aware of this concept, since this is exactly how all the myriad VBX extensions are distributed.

Uses for External Function Calls

As powerful as VBA is, there are many areas in which it is lacking. For example, it can do very little for you, for example, when you need to know about the user's machine and the hardware installed. Windows can provide a great deal of information for you, and the topics in this session will focus on some of the things Windows can do for you that VBA cannot:

• Detect whether or not a specific application has been loaded
  
  
• Run an external application and wait for it to complete
  
  
• Retrieve system information

The items listed above give only a tiny representation of what you might want to do with external function calls.

The Programmer's Best Friend

Though not expressly geared for VBA developers, the book "Visual Basic Programmer's Guide to the Win32 API," by Daniel Appleman (Ziff-Davis Press) will be a vital resource for any application developer wishing to call functions in the Windows API from any dialect of BASIC. Armed with this book and the Microsoft Win32 API Reference, you'll have all the information you could need to figure out how to make Windows do what you need it to do. (You can also find a Win16 version of this book, if you're stuck programming in that environment.)

Why the 16- vs. 32-bit Issues?

When you hear about the Windows application programming interface (API) (if you hear about the Windows API) these days, you can't help but hear about the 16- vs. 32-bit issues. The fact is, things have changed. In a 16-bit operating system (which is what Windows 3.x was), integers are all 16 bits in size, and therefore many measurements are limited to a top value of 32767 (the maximum value in a signed integer value). In a 32-bit operating system, integers are now 32 bits in size, allowing for integers up to around 2 billion. Because the processor itself has been 32-bit since the introduction of the 386DX, it has been a waste all this time to restrict it to moving data around in 16-bit chunks. Using 32-bits for integer operations ought to, in theory, speed up applications. Whether that'll happen or not remains to be seen.

Because Windows is now a 32-bit operating system, all its DLLs are now 32 bit, as well. Sixteen-bit applications cannot just call 32-bit DLLs, and 32-bit applications cannot call 16-bit DLLs. (There are ways around this, but you don't want to muck with that!) To support 16-bit applications, Microsoft® Windows 95® and Microsoft® Windows NT® supply 16-bit versions of their DLLs that supply only the functionality that Win3.x provided. Therefore, 16-bit applications that ran under Win3.x can run fine under Win32 (the generic name for 32-bit Windows). On the other hand, 32-bit applications (like Microsoft® Office 95 apps) require 32-bit DLLs, and therein lies your problem.

To distinguish between the 16-bit and 32-bit DLLs, Microsoft has changed the all the DLL names. What used to be USER.EXE is now USER32.DLL, and GDI.EXE is now GDI32.DLL. As you'll see, in the section later on dealing with declaring API procedures, you must know, when you declare the function, the name of the library in which it resides. In addition, to allow for different character sets supported by Win32 (ANSI vs. Unicode) the DLLs include two versions of each procedure that handles strings, all with different names than were used in the Win16 API calls. Coupled with the fact that almost all numeric "handles" are now long integers from VBA's point of view, you'll find that every single API declaration will need to be modified for use in VBA.

VBA's Reaction to External Function Calls

When you call external procedures from within VBA, you're going outside the realm of what the VBA designers had in mind. Sure, there's no reason why you shouldn't call directly into the Windows API's if you need them, but you should be fairly warned that VBA may not like it. If you change something in the environment that VBA doesn't know about, it may not react kindly the next time it needs to use that resource.

Declaring External Functions

In order to call any external procedures from VBA, you must tell VBA a few things about the procedure you want to call. VBA needs to know the name of the library file, whether you're calling a subroutine or a function, the name of the procedure you want to call, the parameters you want to send, and, if the procedure is a function, what the data type of the return value is.

(The following information is modified from the Microsoft® Access 97 Online Help file)

Syntax 1
[Public|Private] Declare Sub name Lib "libname" _
 [Alias "aliasname" ] [([argumentlist])]
Syntax 2
[Public|Private] Declare Function name Lib "libname" _
 [Alias "aliasname"]([argumentlist])] [As type]

The Declare statement has these parts:

The argument argumentlist has the following syntax:

[ByVal] variable [ As type] [,[ByVal] variable _
 [ As type] ] . . .

The following table describes the parts of argumentlist:

Placement and use of the Declare statement

Use the Declare statement to declare external procedures (procedures contained in a DLL). A Declare statement for an external procedure can be used only in the Declarations section of a module. DLL procedures declared in any module are available to all procedures in all modules, unless you use the Private keyword: in that case, they're only available in the current module.

When Do You Use ByVal?

The ByVal keyword (or its absence, equivalent to using the ByRef keyword) tells VBA how to pass information to the called DLL. In general, using the ByVal keyword causes VBA to pass a copy of the parameter in question to the called procedure. Without the ByVal, VBA will pass the memory address of the variable to the procedure. Strings are handled a bit differently. VBA uses its own internal format for strings, which differs from that used by C, the standard language of most DLLs. Using the ByVal keyword will tell VBA to convert the string to a format that your DLL can understand, converting it to an ordered list of characters followed with a null (CHR$(0)), and will pass the address of that newly formatted string to the DLL. Since all the Windows DLLs require strings in this format, you'll always want to use the ByVal keyword when dealing with strings. With numeric values, you'll need to know if the DLL is expecting a value, or the address of a value. If you use one of the references listed in the resources section below to build your Declare statements, you shouldn't need to worry about this.

Note   Incorrect usage of ByVal will invariably cause either a “Bad DLL Calling Convention” error message, or an immediate General Protection Fault (GPF). In addition, once you get the “Bad DLL Calling Convention” message, your system is unstable and you should save your work, quit, and restart VBA and your VBA host.

Why Use an Alias?

You have the option, when using the Declare statement, of adding an Alias name to the declaration. At first, this might seem like too much work in a situation that is already somewhat foreign. Why use an alias?

• To change an invalid procedure name in the DLL to one VBA allows

  DLLs are written in languages that allow procedure names that VBA does not (for example, "_lwrite". VBA won't allow you to begin a procedure name with an underscore.) You can use an alias to rename such a procedure for use within VBA.

• To change the case of the DLL procedure call

  Calling DLL procedures from VBA is case sensitive. If you want to modify the exact spelling of a DLL procedure name, us an alias.

• To set the procedure name to a DLL function that is only exposed by ordinal number

  Some external procedures have had their names removed from the DLL by the developer, and are available only by their position within the DLL. You can use an alias to provide your own name for these functions.

• To have a unique procedure name

  VBA only allows a single global procedure with a given name. If you attempt to declare an external function and a procedure already exists with the same name, VBA will complain. Use a declaration to modify the "internal" name for the DLL procedure.

• To leave off the "A" required by ANSI versions of Windows API calls

All API calls involving strings, in Win32, are provided in both ANSI and Unicode versions. The ANSI version (required by VBA) of each function ends with a trailing "A". To use the function without that trailing "A", you can use an alias to rename the function internally.

The answer to these problem is to use an Alias name for any external procedure. Rather than using a declaration like this:

Declare Function GetActiveWindow Lib "User32" () As Long

have it look like this, instead:

Declare Function ahtGetActiveWindow Lib "User32" Alias _
 "GetActiveWindow" () As Long

In this case, you'd refer to the function as ahtGetActiveWindow (and not as GetActiveWindow), and as long as you make an effort to choose unique changes to the function name, you can be relatively sure that no other developer will have chosen their alias the same as yours.

In addition, you can use aliases to allow you to declare the same procedure multiple times within your application. Why? To control type-checking, mostly. For example the following declaration:

Private Declare Sub ClipCursor Lib "USER32" _
 (typRect As tagRect)

allows you to clip the cursor movement to a specific rectangle on the screen. To allow the cursor free reign again, you need to call the function, passing a null (0&) value for the parameter. In C, this is no problem. In Basic, however, you can't: VBA will complain about an invalid data type if you try. Therefore, you may find it useful to declare the function a second time:

Private Declare Sub ClipCursorClear Lib "USER32" _
 Alias "ClipCursor" (ByVal param As Any)

to allow yourself a way to both clip and clear the cursor. See frmClip for an example of doing this.

How does Windows find the Libraries?

When you use a Declare statement, you supply VBA with the name of the library that contains the procedure you want to use. You won't normally tell it, though, the exact location of the file. Windows has its own search priorities set up for finding libraries. It looks in the following locations, in the order listed:

1. The directory from which the application loaded.
   
   
2. The current directory.
   
   
3. The Windows system directory (the directory containing such system files as GDI32.DLL). For Windows NT, the 32-bit Windows system directory.
   
   
4. Windows NT only: The 16-bit Windows system directory.
   
   
5. The Windows directory
   
   
6. The directories listed in the PATH environment variable.
   
   
7. The list of directories mapped in a network.

If VBA can't find the specified library in any of those locations, it'll send you a “File Not Found” error message. If you're installing your own DLLs, make sure you place it in one of these locations.

Heed This Warning!

Calling external functions is prone to disaster until you've got the Declare statements set up just right. If you're calling Windows APIs, most likely you'll have a correct Declare statement set up for you. If you're writing your own DLL, or calling one that isn't part of Windows, chances are good that your Declare statement will not be correct the first time around. The consequences for using an incorrect Declare statement are often dire. Any one of VBA, Windows, or your entire computer might, and probably will, crash (OK, under Windows NT, it is highly unlikely that your computer will crash. It is certainly never happened to me.) Be forewarned: when playing with Declare statements, make absolutely sure you save all your work, in every running Windows application, before running your VBA application. Sooner or later, you'll make a mistake and bring Windows to its knees.

Finding Declarations

If you use the Microsoft Developer Network (MSDN) CD to find declarations for the various API calls you'd like to use, you'll find one immediate roadblock: the function descriptions in MSDN (and the other Microsoft documentation) are geared towards C/C++ programmers. We lowly VBA typed need to find some way to convert the information in MSDN into something we can use.

Where to turn for this information? You have a number of choices.

Write the declarations yourself

This hard-boiled solution should be the one you’re least likely to try. If someone has done the work for you, why should you repeat the effort? Some folks are just determined to take on the task themselves, however, or perhaps they need to call external functions that aren't part of the Windows API. In that case, it is important to understand the mappings between C/C++ data types, and VBA data types. The following table lists the standard C data types, and the corresponding VBA declaration for each.

Conversions between C Datatypes and VBA Datatypes

Use a tool to help find declarations

Both Visual Basic and Office Developer Edition ship with a tool that purports to aiding in your selection and placement of Windows API declarations, types, and constants. I beg to differ, but if you must, you can use Microsoft's API Text Viewer, shown in Figure 1. This tool requires you to load a text file (or a database version of the same text file, which speeds up the loading of the information), and allows you to select items to be placed on the Windows clipboard. Then, once you've located the final resting place for the information, you can paste it in. You may find this tool useful.

Figure 1. Use the API Text Viewer to locate and insert API declarations

Halfway in-between

I've never felt particularly comfortable with the API Text Viewer-because you can't see the actual declaration you're about to paste in, because you have to search around so much, because it is so incredibly slow, I've tended not to use it. My solution? Open up WIN32API.TXT in a text editor and search for what I need. Works for me!

Declaring User-Defined Data Types

Though you'll find uses for defining your own data types throughout VBA, one place where you cannot avoid using them is in your dealings with external functions. Many functions in external libraries expect to receive, as a parameter, a single variable reference that refers to a conglomerate of other types. These user-defined data types are conceptually similar to structures in C and records in Pascal.

To create a data type, you must define it in the Declarations section of a module. This definition does not create an actual variable of this new type, but simply creates the "template" which you can use when later declaring specific instances of the type.

For example, the following code will create a data type into which you can store the coordinates of a rectangular area:

Type tagRect
    lngTop As Long
    lngLeft As Long
    lngBottom As Long
    lngRight As Long
End Type

After defining this data type, you could create a variable of type tagRect anywhere in your application. Any instance of this type would contain four long integers representing the top, left, bottom and right coordinates of a rectangle. In addition, the scope and lifetime of variables declared to be of a user-defined type are just the same as for built-in variable types. That is, the Global, Dim, Private, Public and Static keywords work just the same for variables of type tagRect as they do for any other variable.

Referring to Elements of a User-Defined Type

To refer to the members of a user-defined type, use the syntax:

varName.field

For example, to refer to the lngLeft element of a tagRect variable that's been declared:

Dim rct As tagRect

you would use the expression

rct.lngLeft = 1000

Using a User-Defined Data Type

If your goal was to write a function that would retrieve the coordinates for a given form, you'd need to use the tagRect data type. VBA provides no such functionality, but the Windows API provides the GetWindowRect sub, which fills in a rectangle structure with the coordinates of the requested window. You'll need to pass a window handle to this function, as well as the address of a variable of the tagRect user-defined type. (See basSystemCoords for this example.)

The declaration for GetWindowRect might look something like this:

Declare Sub GetWindowRect Lib "USER32" _
 (ByVal hwnd As Long, rct As tagRect)

Then, you could write a simple subroutine which takes, as its parameters, a reference to the form in question and a tagRect variable to fill in. It might look something like this:

Sub TestCoords()
    Dim rct As tagRect
    Dim hWnd As Long
    
    hWnd = GetFocus()
    GetWindowRect hWnd, rct
    Debug.Print rct.lngTop, rct.lngLeft, _
     rct.lngBottom, rct.lngRight
End Sub

Converting from Win16 to Win32

(Otherwise known as: Your biggest nightmare!)

The crystal ball here indicates that there will be major gnashing of teeth and wailing when every VBA developer tries to run their converted 16-bit applications, only to find out that every API call they used in their 16-bit solutions will need rewriting. This will be, I predict, the major 16 to 32-bit conversion issue. Since many developers borrow code from where ever (books, Internet, Knowledge Base articles, etc.) and don't really "get" it, the concept of converting it themselves can be a bit daunting.

The following list brings up most of the conversion issues:

• Every library name is now different (usually just a "32" tacked on the end)
  
  
• Almost every handle object has been changed from a 16-bit value to a 32-bit value (integer to long integer).
  
  
• Windows NT (and one day, Windows 95) fully supports both ANSI and Unicode character sets. You have to tell Windows which version of a function (ANSI or Unicode) you're calling, because the API includes both for any functions that involve text. To do this, you must provide an alias for each function, telling Windows that you actually need to call a function like GetProfileStringA or GetProfileStringW when you call GetProfileString in your code.
  
  
• Most functions that used to return an integer now return a long integer.
  
  
• Many functions that were in common use have been superceded or no long exist. (For example, where did GetModuleUsage go?)
  
  
• Some functions have been deemed "outmoded" and should be replaced (GetPrivateProfileString, for example, should be replaced with calls to read from the Registry instead, but the old calls will still work).

The solutions to these problems range from trivial to painful:

• To handle most conversion issues, use either the API declaration tool that's part of VB, the Office Developer's Edition or an external reference to find the matching 32-bit API declaration. Copy it into your code, fix up the various data type differences, and you're all set. Or, you can use the WIN32API.TXT file that ships with VB and the ODE, as well.
  
  
• If the previous sources didn't help, you may need to resort to the MSDN (Microsoft Developer Network CD), a quarterly release which includes the entire Win32 API help file with each issue. This, however, requires you to have at least a passing understanding of C/C++ conventions, since the descriptions in this help file are geared towards the C-heads.
  
  
• If all else fails, you can (as I often do) search through the files in the INCLUDE directory of the Win32SDK CD, looking for the various function declarations and constants. It is not fun, but it almost always pays off.
  
  
• For functions that have been replaced with no warning (GetFreeSystemResources, for example, which has been replaced with GlobalMemoryStatus, but that doesn't tell you about resource usage), you'll just need to poke around in the MSDN CD, and see what you can find. Same goes for functions that are now obsolete.

What tools do you need?

• The 32-bit version of Daniel Appleman's book.
  
  
• MSDN CD. I couldn't work without it.
  
  
• Some listing of the Win32 API in a format that's useful for VBA programmers. I've included the Win32API.TXT file with this presentation's sample files, but you may want a tool that makes it a bit simpler to find information. That could be the help file from the Appleman book, or the Win32API viewer that's part of VB and the ODE.
  
  
• Updates of the original sources for your code. Authors love it when you buy the updates to their books!

Four Examples Using API Calls

Working with Window Captions

You may have, as part of an application, the need to set a window's caption. Although most of the Office applications supply a read-write Caption property (Microsoft® Access excluded) most other applications don't support Automation, or they don't have a Caption property. This example will show how you can modify a window's caption from within your application, using the Windows API.

Understanding how Windows uses windows

Before we can delve into the steps necessary to set a window's caption, we'll need to cover some basics about the ways in which Windows manages its child windows.

In Windows, just about every object you see on the screen is an object with properties and events. Every button, scroll bar, dialog box, and status bar is a window. To keep track of all these windows, Windows assigns to each a unique long integer window handle through which it can refer to the specific window. This window handle has come to generally be referred to as the window's "hWnd" (handle to a window).

Getting/setting the caption

In order to get or set any window caption, you'll need to use the GetWindowText and SetWindowText Windows API functions. Their declarations might look like this:

Declare Function GetWindowText Lib "user32" _
 Alias "GetWindowTextA" (ByVal hWnd As Long, _
 ByVal strBuffer As String, ByVal lngChars As Long) As Long

This function requires three parameters: a window handle, a buffer into which to place the current window caption, and a long integer representing the amount of space available in the buffer.

Declare Function SetWindowText Lib "user32" _
 Alias "SetWindowTextA" (ByVal hwnd As Long, _
 ByVal strBuffer As String) As Long

This function requires two parameters: a window handle for the window in question, and a string containing the new window caption. It returns 0 if the function failed, and a non-zero value if it succeeded. The next section will cover the technique for finding a particular window handle, but for now, I will just use Microsoft Access as an example, because it exposes the hWndAccessApp property for its main window.

Function SetAccessCaption(strCaption As String) As String
    ' This routine just doesn't do anything
    ' if Access isn't already running. We call this  
    ' "demo-ware".
    Dim oAccess As Access.Application
    On Error Resume Next
    Set oAccess = GetObject(, "Access.Application")
    ' You'll get error 429 if Access wasn't
    ' already running.
    If Err.Number = 0 Then
        SetAccessCaption = _
         SetWindowCaption(oAccess.hWndAccessApp, strCaption)
    End If
End Sub

Differences between C and Basic string handling

If you'll remember from the discussion of ByVal, C expects strings to be null-terminated, and Basic does not. In order to have a DLL fill in a string buffer, you'll need to pass it using ByVal to the DLL, and when it returns from the DLL call, it'll be null terminated. That's why, when calling GetWindowText, you must also pass the maximum length of the string buffer (in the lngChars parameter), so the DLL can know how many characters it has to play with. On return from the function call, the return value will contain the number of characters the DLL put into the buffer, and you can use that value to trim off the trailing null character (see the GetWindowCaption function for an example). In addition, since the DLL can't know how big the buffer really is, it'll write to that buffer, whether or not there's space reserved for the output string. If the DLL does this and there's not enough room in the buffer, you're pretty much guaranteed to crash. Make sure that you "puff" up the string (or use a fixed-length string). In the example, I used the Space function to fill the buffer with the maximum number of spaces that I will ever need.

Finally, the functions

You can use the following functions, then, to do your work for you. The GetWindowCaption function uses GetWindowText to retrieve a window caption for you, given the window handle. The SetWindowCaption function calls GetWindowCaption to retrieve the window caption, and stores it in a local variable. It then sets the window's caption, calling SetWindowText with the value you passed to the function. Finally, it returns the original window caption it stored away, so you could reset the caption later in your application, if you need to.

Function GetWindowCaption(hwnd As Long) As String
    ' Get the window caption of the window with
    ' hWnd as its handle.
    
    Const conMaxChars = 1024
    Dim strBuffer As String
    Dim lngCount As Long
    ' Puff up the buffer, so the DLL won't write
    ' over stuff it shouldn't.
    strBuffer = Space(conMaxChars)
    ' Get the original window text, chopped off at the
    ' correct number of characters. Better leave space
    ' for that final Null character!
    lngCount = GetWindowText(hwnd, strBuffer, conMaxChars - 1)
    GetWindowCaption = Left$(strBuffer, lngCount)
End Function
Function SetWindowCaption(hwnd As Long, strCaption As String) _
 As String
 
    ' Set the caption for the window with hWnd as its handle,
    ' and return the original caption.
    Dim strBuffer As String
    ' Get the old text.
    strBuffer = GetWindowCaption(hwnd)
    ' Set the new window text. The function
    ' returns 0 in failure, non-zero on
    ' success, but do we really care here?
    Call SetWindowText(hwnd, strCaption)
    
    ' Return the original value as the
    ' function's return value.
    SetWindowCaption = strBuffer
End Function

Detecting Whether a Specific App is Loaded

Normally, you need to know whether a specific application is currently running so that you can either activate that application, or use it as part of a DDE or OLE conversation. The sample form, frmAppsRunning (Figure 2), asks Windows the question, "Is this app running?" for each of six predefined window classes, and you can add one more of your own. For each application that frmAppsRunning finds, it fills in the window handle (hWnd) column and window caption column on the form. Finally, you can click on any of the enabled buttons in the right-hand column to switch to the running application.

Figure 2: frmAppsRunning tells you whether apps are running, and allows you to switch to them

Using class names

Every window, besides having a unique long integer that identifies it, belongs to a specific window class. Window class names are text strings, provided by the programmer, which have created the application that creates the window. For example, the main Microsoft Access window belongs to the class "OMain". You'll find other Windows application class names in the table below. You'll be using these names to help identify whether or not an application is currently loaded.

Windows provides a number of methods to determine whether a given application is currently running or not. This is particularly useful if you need to interact with an application (Microsoft® Excel, for example), but you don't want to start a new instance if one is already running. The simplest method is to use the FindWindow API function, which requires you to specify the window class name, and it returns the window handle of the first top-level window of that class it finds.

You'll need a declaration for the external function before you can use it:

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

You can use the following function, IsAppLoaded, to detect whether or not a given program is currently loaded. This function calls the FindWindow function to do its work.

Function IsAppLoaded(ByVal varClassName As Variant) As Long
    ' Report if an application is currently loaded
    ' In:
    '   strClassName: the Windows class name for the main window
    '                 of the app to check for.
    ' Out:
    '   Return Value: 0 if App isn't loaded, non-zero (the
    '                 app's hWnd) otherwise.
   If IsNull(varClassName) Then
      IsAppLoaded = 0
   Else
      IsAppLoaded = FindWindow(CStr(varClassName), 0&)
   End If
End Function

To check and see whether a given application is currently running, use the IsAppLoaded function, in basUsage. You pass a class name to this function as a parameter, and it returns the window handle of the application if it is running, or 0 if it is not. For example:

hWnd = IsAppLoaded("PP97FrameClass") 

will return a non-zero value if Microsoft® PowerPoint® 97 is currently running. (Note that the class names are not case sensitive.)

Once you know the window handle for the application, if you want to make that application active, you can use the AppActivate command. To switch to the application, though, you'll need to know the window caption. To make that easier, you can call the GetWindowCaption function, in basCaption, before attempting to activate the application. For example, this code will switch to Microsoft Excel, if it is running:

Dim hWnd as Long
hWnd = IsAppLoaded("XLMain")
If hWnd <> 0 Then
    AppActivate GetWindowCaption(hWnd)
End If	

Note: In previous versions of Basic, AppActivate required an exact match in order to find a window and activate it. In its current implementation, you can supply just the first portion (the application name). This makes it possible to use the same text whether or not the document within the application is maximized.

Some sample class names

To be able to ask Windows the question, "Is <some Application> running?", you'll need to know the Windows class name for the main window of the application. The table below lists the names for several Windows applications. As you can see, the class names appear somewhat arbitrary. Since they're assigned not by the marketing or documentation departments, but rather by the development staff, they often reflect the project's code name or the state of mind of the developer.

Class names for some Windows applications

Starting another application

If you find that you do need to start another application, you can use the VBA Shell function. The syntax for Shell is:

lngTaskID = Shell(CommandString[, WindowStyle])

The CommandString parameter is the application name (with extension, if it is anything besides .EXE) and any command-line arguments. If the application's file isn't in the MS-DOS PATH, then you'll need to specify the exact path, too.

The WindowStyle parameter is optional, and takes one of the following values:

WindowStyle values for Shell

If you don't specify a window style, VBA will use the vbMinimizedFocus setting.

The following code will start Microsoft Excel, if it is not already running:

Function StartExcel ()
    ' Start Excel, if it is not already running.
    ' Return True if Excel is running at the end of this
    ' function.  Return False if it couldn't be started.
    ' Unless Excel is in your path, this function won't
    ' be able to start it.
    Dim lngTask As Long
    ' Initialize to some non-zero value.
    lngTask = 1
    If Not IsAppLoaded("XLMain") Then
        lngTask = Shell("Excel.exe", vbHide)
    End If
    StartExcel = (lngTask <> 0)
End Function

Note: Don't expect IsAppLoaded to distinguish between multiple copies of the same application. That is, if you have two copies of Notepad running, you can't count on IsAppLoaded to return the handle to a specific instance of Notepad. It will return the handle of the first one it comes across. But that shouldn't bother you, if all you're looking to find out is if any copy of the application is currently running. On the other hand, you can pass FindWindow the exact caption of the window you're seeking, so you can modify IsAppLoaded to accept a caption as well, if you like.

Run an Application and Wait

From within an application, sometimes you'll need to run a MS-DOS batch file or utility program that requires some time to do its job. Or perhaps, you'd like to run some other Windows application. In both cases, you'd like your VBA application to pause until the other program has finished its work. Normally, the VBA code starts up the other application but then just keeps on going. This section will show you how you can make VBA wait until that other application has finished before moving on.

Perhaps you've accomplished this in a 16-bit Basic application, like Microsoft Access 2.0 or WordBasic. In general, people used the GetModuleUsage function to return a module usage count, and just wait until that became 0 before moving on. Unfortunately, any code you've got that uses this technique will have to be rewritten to use the method covered here: the GetModuleUsage function just doesn't exist in Win32.

What's going on?

The Shell function in VBA returns a unique integer value representing the running task. You can use this value, the instance handle for the running application, to track the state of the application. Given an instance handle, you can use the OpenProcess API function to retrieve the process handle for the process. Armed with that process handle, you can call the GetExitCodeProcess function continually, until it sees that the process has shut down. Since this happens automatically once a MS-DOS application has finished running, you can use this technique to wait until a MS-DOS window has closed before moving on in your application.

Check it out!

The sample form, frmTestWait allows you to try starting both a MS-DOS application and a Windows application, and wait for either to complete. There's also a button that allows you to start a MS-DOS application, but still continue the attached code. In any of these three cases (see Figure 3), the sample code attempts to load the text file C:\WAITTEST.TXT into a text box on the form once the application you've started finishes its work. (In the case where the code doesn't wait for the other application, of course, there's nothing to load.) To test it out, use frmWaitTest, trying each of the command buttons to test the functionality demonstrated in this How-To.

Figure 3: The sample form, frmWaitTest, after it is run an application

How's it work?

The secret to the RunAppWait subroutine is its use of the Windows API function, GetExitCodeProcess. This function takes as a parameter the process handle of an application, which you can retrieve by calling the OpenProcess API function, given the instance handle returned by the call to Shell. GetExitCodeProcess monitors a running process, and retrieves that process's exit code. As long as the process continues to run, GetExitCodeProcess retrieves the value STILL_ACTIVE (defined in basRunApp).

Consider the following code, which will check for the existence of a running application:

Do
   ' Attempt to retrieve the exit code, which will
   ' just not exist until the application has quit.
   lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
Loop While lngExitCode = STILL_ACTIVE

Though this will almost do what you need, it won't quite succeed. You've left VBA running a tight loop, waiting for the new application to finish. Unfortunately, VBA is grabbing all of the clock cycles in Windows, looping and waiting for the other application to be done. It won't be possible to quit the other application, because it'll never get enough attention from Windows to even let you pull down a menu!

The solution, then, is to be a good Windows citizen, allowing other applications their processing time. To do this, you must add a DoEvents statement inside the loop. This allows other applications to do their own work as VBA loops, waiting for the application you started to be finished. Thus, the RunAppWait subroutine looks like this:

Sub RunAppWait(strCommand As String, intMode As Integer)
   ' Run an application, waiting for its completion
   ' before returning to the caller.
   Dim hInstance As Long
   Dim hProcess As Long
   Dim lngRetval As Long
   Dim lngExitCode As Long
   On Error GoTo HandleErr
   ' Start up the application.
   hInstance = Shell(strCommand, intMode)
   hProcess = OpenProcess(PROCESS_QUERY_INFORMATION _
    Or SYNCHRONIZE, True, hInstance)
   Do
      ' Attempt to retrieve the exit code, which will
      ' just not exist until the application has quit.
      lngRetval = GetExitCodeProcess(hProcess, lngExitCode)
      DoEvents
   Loop While lngExitCode = STILL_ACTIVE
   
ExitHere:
   Exit Sub
HandleErr:
   Select Case Err.Number
      Case ERR_FILE_NOT_FOUND
         MsgBox "Unable to find '" & strCommand & "'"
      Case Else
         MsgBox Err.Description
   End Select
   Resume ExitHere
End Sub

Some comments

To use the Shell command, you must specify an executable file. If you need to run a MS-DOS internal command, or if you need to redirect the output from a program to a text file, you'll need to load a copy of COMMAND.COM to do your work. In addition, you'll need to use the /C parameter, indicating to COMMAND.COM that you just want a temporary instance, and that it should quit when the program you run finishes. For example, to run the CHKDSK.EXE program directly, you could use the following function call. (All these examples assume that the necessary programs are available in the MS-DOS PATH.)

hInstance = Shell("CHKDSK.EXE", vbMinimizedNoFocus)

To run DIR, on the other hand, you'll need to start COMMAND.COM first:

hInstance = Shell("COMMAND.COM /C DIR C:\*.BAT", _
 vbMinimizedNoFocus)

To redirect the output from a program to a text file, you'll also need to use COMMAND.COM:

hInstance = Shell("COMMAND.COM /C CHKDSK C: > C:\WAITTEST.TXT", _
 vbMinimizedNoFocus)

This solution relies on DoEvents in a loop to allow the secondary process to display its own window as it is running. If your secondary process is invisible, you'll get better results using a function that was meant for just this situation, WaitForSingleObject. This function, declaration shown below, waits until the process whose handle you've passed it has completed. It doesn't, however, give the secondary process screen time, so you'll see no activity.

Declare Function WaitForSingleObject Lib "kernel32" _
 (ByVal hHandle As Long, _
 ByVal lngMilliseconds As Long) As Long

If you're running a secondary application which requires interaction, use the first method. If you're starting up an application that runs in the background, you can use WaitForSingleObject instead.

Using Class Modules to Wrap API Functionality

Many programmers find it simpler to not call the Windows API directly, but to provide class modules that wrap up the API functionality. Why do this at all? And why use class modules, rather than standard modules?

Starting with the first question: why do this at all? Take, for example, the API functions that provide information about the current system configuration. VBA provides almost no native support for operations involving system information; because Windows itself provides easy-to-call API functions for determining and controlling the environment, VBA doesn’t have to duplicate that functionality. Of course, some of the API functions are tricky to call, and information you need is scattered throughout the Windows API. In addition, the Windows API provides so many functions for working with system information, and their functionalities overlap so much, that it is difficult to know which one to use in any given circumstance. Providing some sort of VBA "protection" from the API makes it easier to retrieve the information you need.

Why class modules? That is, what do you gain by having this functionality wrapped up in a class as opposed to a standard module? Unlike other situations in which you use class modules, in this case you don’t care about the multiple instancing. (You’ll never create more than one instance of a SystemInfo class in your application, for example.) What you do gain is the ability to treat disparate function calls as simple properties. For example, to retrieve information about a particular setting, you’ll often use the GetSystemMetrics API function. To change the same information, you need to use the SystemParametersInfo function. Rather than provide two separate functions for you, one to get and one to change the value, I've provided a single property, with its Let and Get Property procedures. This way, from your application, you can write simple code like this to retrieve a value, change it, and then set it back at a later time:

Dim lngTimeOut As Long
Dim si As New SystemInfo
' Store away the original screen saver timeout.
lngTimeOut = si.ScreenSaverTimeout
si.ScreenSaverTimeout = 1800
' Do work in here...
' Now reset the timeout and release the object.
si.ScreenSaverTimeout = lngTimeOut
Set si = Nothing

In addition, class modules provide another benefit: because class modules trigger their Initialize event when you create a new instance of the class, the class can call an API function that initializes a data structure. Several of the system information functions require you to pass a single data structure, with many elements. For these functions, the corresponding class can call the function in its Initialize event, retrieve all the information at once, and return it to you as properties of the class.

To see the techniques from the next few sections in action, check out frmSystemInfo, shown in Figure 4.

Figure 4: Use frmSystemInfo to test the system information class modules

Computer and Operating System Information

The SystemInfo class provides information on, and, in a few cases, allows you to set information about, your computer and the operating system. Of course, most of the properties of the SystemInfo class (SYSTEMINFO.CLS) must be read-only. Only the Beep, ComputerName, ScreenSaverActive, and ScreenSaveTimeout properties allow you to specify a value; the rest simply return information about your environment. The following table lists all the properties of the SystemInfo class.

Properties of the SystemInfo Class

The properties of the SystemInfo class can be broken down into five basic categories, as shown in the following table. The next section provides more information on these categories.

Categories of SystemInfo class Properties

Using the SystemInfo Class

This section describes each of the categories of properties in the SystemInfo class, explaining both how to use them and how they were implemented.

Computer and user information

The two properties ComputerName and UserName provide information about the network name for the computer and the logged-in user’s name. Both properties return strings, and the ComputerName property also allows you to set the name of the computer. For example, you might write code like this to use the properties:

Dim si As New SystemInfo
Dim strOut As String
strOut = si.UserName & " is logged into " & si.ComputerName
MsgBox strOut
' This won't take effect until you next reboot.
si.ComputerName = "CompuLand"

Retrieving these properties is simple: the Windows API provides the GetComputerName and GetUserName functions. In both cases, you pass in a buffer to contain the name and a long integer variable containing the length of the buffer. Windows fills in the buffer and places the length of the string it returned into the long integer variable. If the function returns a nonzero value, the code can use the Left function to retrieve as many characters from the buffer as Windows said it filled in. The following listing shows the code for retrieving the ComputerName property; the code for the UserName is almost identical.

Property Get ComputerName() As String
    Dim strBuffer As String
    Dim lngLen As Long
    strBuffer = Space(dhcMaxComputerName + 1)
    lngLen = Len(strBuffer)
    If CBool(GetComputerName(strBuffer, lngLen)) Then
        ComputerName = Left$(strBuffer, lngLen)
    Else
        ComputerName = ""
    End If
End Property

The code to set the computer name is even simpler. It calls the SetComputerName API function:

Property Let ComputerName(Name As String)
    Call SetComputerName(Name)
End Property

Note   The SetComputerName API call only writes the new computer name to the Registry. It doesn’t (and it really can’t) change the name of the computer as it is currently used on the network. The next time you restart the computer, it will use the new name.

Path information

The SystemDirectory, TempPath, and WindowsDirectory properties retrieve information about where you can expect to find files on your computer. In each case, Windows provides a single function to call in order to retrieve the information, and in each case, the code is almost identical. For example, the following listing includes the code for the WindowsDirectory property. You should be extremely familiar with this code if you’ve ever done any work with the Windows API that involves strings.

In the WindowsDirectory property procedure, the code first creates a buffer to hold the output string and makes sure it is large enough for the largest expected result, using the String function. Then it calls the GetWindowsDirectory API function, passing the buffer and the length of the buffer. GetWindowsDirectory attempts to place the path into the buffer and returns the length of the string it placed into the buffer. If the buffer wasn’t large enough, the function returns the length it would need to place into the buffer. If the function returns a value larger than the length passed into it, the property procedure resizes the buffer and tries again. This time, the string is guaranteed to fit.

Property Get WindowsDirectory() As String
    ' Retrieve the Windows directory.
    Dim strBuffer As String
    Dim lngLen As Long
    strBuffer = Space(dhcMaxPath)
    lngLen = dhcMaxPath
    lngLen = GetWindowsDirectory(strBuffer, lngLen)
    ' If the path is longer than dhcMaxPath, then
    ' lngLen contains the correct length. Resize the
    ' buffer and try again.
    If lngLen > dhcMaxPath Then
        strBuffer = Space(lngLen)
        lngLen = GetWindowsDirectory(strBuffer, lngLen)
    End If
    WindowsDirectory = Left$(strBuffer, lngLen)
End Property

Processor information

To retrieve processor information, the SystemInfo class uses the GetSystemInfo API function. This function fills a SYSTEM_INFO data structure with data. (See the class module for the gory details.) The Initialize event procedure of the SystemInfo class calls the API function, and the various properties retrieve information from the elements of the SYSTEM_INFO structure.

Although the processor information returned by the GetSystemInfo API function isn’t necessary for every application, it can be useful. The next few sections provide the details necessary to interpret the information provided by these properties.

NumberOfProcessors

Specifies the number of processors in the system.

ActiveProcessorMask

Specifies a mask value representing the processors in the system. The bit or bits set in the mask indicate the active processor (bit 0 is processor 0; bit 31 is processor 31). This value will be 1 for most computers.

PageSize

Specifies the page size and the granularity of page protection and commitment. This isn’t generally of much interest to VBA programmers.

AllocationGranularity

Specifies the granularity with which virtual memory is allocated. This value was hard coded as 64K in the past; since the Windows environment expands to different hardware platforms, other values may be necessary. Again, this value isn’t of much interest to VBA programmers.

MinimumApplicationAddress, MaximumApplicationAddress

Pointers to the lowest and highest memory addresses accessible to applications and Dynamic Link Libraries. Not generally needed for VBA programmers unless they’re making serious use of the Windows API functions that care about these addresses.

ProcessorType

Not relevant to Windows NT, which uses the ProcessorArchitecture, ProcessorLevel, and ProcessorRevision properties to provide information about the processor. This property provides the only means, in Windows 95, to gather such information. The value will be one of the items in the following list:

ProcessorArchitecture

Specifies the system’s processor architecture. For Windows 95, this value will always be 0. For Windows NT, the value can be any item from the following list:

ProcessorLevel

Not used in Windows 95, but in Windows NT it returns the system’s architecture-dependent processor level. The values can be any of the items in the first column of the following list. Use the ProcessorArchitecture value in the second column to determine the actual processor level.

ProcessorRevision

Not used in Windows 95, but in Windows NT this property specifies an architecture-dependent processor revision.

Version information

The properties in this area all use the GetVersionEx API function to fill in a OSVERSIONINFO structure with information about the operating system. In the Initialize event procedure for the SystemInfo class, the code calls GetVersionEx, so all the various properties need do is retrieve information from a module-level.

Conclusion

Using the Windows API in VBA applications allows you to accomplish all sorts of things you may have though impossible using VBA alone. If you have a need that VBA can't fulfill, dig through the available reference materials: it is possible that even though VBA doesn't provide a method, Windows does. As a start, here's a list of API topics that you might find interesting:

• Remove a form's system menu, maximize and minimize buttons
  
  
• Flash a window's title bar or icon
  
  
• Classify keypresses in a language-independent manner
  
  
• Restrict mouse movement to the current form
  
  
• Run another program and pause until it is done
  
  
• Shut down Windows under program control
  
  
• Run an application, given a data file associated with that application
  
  
• Check to see if an application is already running
  
  
• Retrieve a list of all the top-level windows
  
  
• Close a running Windows application
  
  
• Set file date and time stamps and compare the dates of two files
  
  
• Retrieve information about the disk drives in your computer
  
  
• Collect and display information on the system
  
  
• Create and cancel network connections programmatically
  
  
• Retrieve or set accessibility information

These are just a few of the many solutions the Windows API can provide for you. You’ll often find it useful to wrap API functionality in class modules, which supply the ability to use property procedures, making it even easier to work with the API functionality. Using modules to wrap up the functionality, you do the hard work only once-from then on, you only need to call the wrapper functions or properties.

© 1997 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Microsoft, Windows, Windows 95, Windows NT, Visual Basic, and PowerPoint are registered trademarks and BackOffice is a trademark of Microsoft Corporation.

Intel and Pentium are registered trademarks of Intel Corporation.

Other product or company names mentioned herein may be the trademarks of their respective owners.