16

Programming with the Windows API

Visual Basic for Applications is a high-level language that provides us with a rich, powerful, yet quite simple set of functionality for controlling the Office suite of products, as well as many other applications. We are insulated, some would say protected, from the “mundane minutiae” of Windows programming that a C++ programmer has to contend with.

The price we pay for this protection is an inability to investigate and control many elements of the Windows platform. We can, for example, use Application.International to read most of the Windows Regional Settings and read the screen dimensions from Application.UsableWidth and Application.UsableHeight, but that's about it. All the Windows-related items available to us are properties of the Application object and are listed in Appendix A.

The Windows platform includes a vast amount of low-level functionality that is not normally accessible from VBA, from identifying the system colors to creating a temporary file. Some of the functionality has been exposed in VBA but only to a limited extent, such as creating and using an Internet connection (for example, we can open a page from the Internet using Workbooks.Open “<URL>”, but we can't just download it to disk). There are also a number of other object libraries typically available on a Windows computer that provide high-level, VBA-friendly access to the underlying Windows functionality. Examples of these are the Windows Scripting Runtime and the Internet Transfer Control.

There are times, though, when we need to go beyond the limits of VBA and the other object libraries and delve into the files that contain the low-level procedures provided and used by Windows. The Windows Operating System is made up of a large number of separate files, mostly dynamic link libraries (DLLs), each containing code to perform a discrete set of interrelated functions. DLLs are files that contain functions that can be called by other Windows programs or other DLLs. They cannot be “run” like a program themselves.

These files are collectively known as the Windows Application Programming Interface, or the Windows API. Some of the most common files you'll use in the Windows API are:

This chapter explains how to use the functions contained in these files in your VBA applications and includes a number of useful examples. All of the Windows API functions are documented in the Platform SDK section of the MSDN Library at: http://msdn.microsoft.com/library/default.asp, which can be thought of as the online help for the Windows API.

Anatomy of an API Call

Before we can use the procedures contained in the Windows DLLs, we need to tell the VBA interpreter where they can be found, the parameters they take, and what they return. We do this using the Declare statement, which VBA Help shows as:

[Public | Private] Declare Sub name Lib “libname”  [Alias “aliasname”]
[([arglist])]
[Public | Private] Declare Function name Lib “libname”  [Alias “aliasname”]
[([arglist])] [As type]

The VBA Help gives a good explanation of the syntax of these statements, but does not include any example code using an API method. The following is the declaration used to find the Windows TEMP directory:

Private Declare Function GetTempPath Lib “kernel32” _
        Alias “GetTempPathA” ( _
        ByVal nBufferLength As Long, _
        ByVal lpBuffer As String) As Long

The preceding declaration tells VBA that:

• The function is going to be referred to in the code as GetTempPath
• The API procedure can be found in kernel32.dll library
• The library method we are referring to is GetTempPathA (case sensitive)
• GetTempPath takes two parameters, a Long and a String (more about these later)
• GetTempPath returns a Long

The declarations for most of the more common API functions can be found in the file win32api.txt. The Developer version of Office XP and any of the more recent versions of Visual Basic include this file and a small API Viewer applet to help locate the declarations. At the time of writing, the text file can be downloaded from the “Free Stuff” page of the Office Developer section of Microsoft's Web site, http://www.microsoft.com/officedev/o-free.htm, or directly from http://www.microsoft.com/downloads and searching on win32api.exe.

Interpreting C-Style Declarations

The MSDN library is the best source for information about the functions in the Windows API, but is primarily targeted towards C and C++ programmers and displays the function declarations using C notation. The win32api.txt file contains most of the declarations for the core Windows functions in VBA notation, but has not been updated to include some of the newer Windows DLLs (such as the OLE functions in olepro32.dll and the Internet functions in WinInet.dll). It is usually possible to convert the C notation to a VBA Declare statement, using the method shown next.

The declaration shown in MSDN for the GetTempPath function (at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/gettemppath.asp) is:

DWORD GetTempPath(
   DWORD nBufferLength,    // size, in characters, of the buffer
   LPTSTR lpBuffer     // pointer to buffer for temp, path
);

This should be read as:

<Return data type> <Function name>(
           <Parameter data type> <Parameter name>,
       <Parameter data type> <Parameter name>,
);

Rearranging the C-style declaration to a VBA Declare statement gives the following (where the C-style DWORD and LPSTR are converted to VBA data types as shown):

Declare Function functionname Lib “libraryname”  Alias “GetTempPathA” (ByVal
nBufferLength As DWORD, ByVal lpBuffer As LPSTR) As DWORD

On the Windows platform, there are two types of character sets. The ANSI character set has been the standard for many years and uses one byte to represent one character, which only gives 255 characters available at any time. To provide simultaneous access to a much wider range of characters (such as Far Eastern alphabets), the Unicode character set was introduced. This allocates two bytes for each character, allowing for 65,535 characters.

To provide the same functionality for both character sets, the Windows API includes two versions of all the functions that involve strings, denoted by the “A” suffix for the ANSI version and “W” for the Unicode (or Wide) version. VBA always uses ANSI strings, so we will always be using the “A” version of the functions – in this case, GetTempPathA. The C-style declarations also use different names for their data types, which we need to convert. While not an exhaustive list, the following table shows the most common data types:

Some API definitions on the MSDN also include the IN and OUT identifiers. If the VBA type is shown in the table as ‘ByVal <Name> As Long’, it should be changed to ‘ByRef…’ for the OUT parameters.

Note that strings are always passed ByVal (by value) to API functions.
This is because VBA uses its own storage mechanism for strings, which the C
DLLs do not understand. By passing the string  ByVal, VBA converts its own storage structure into one that the DLLs can use.

Putting these into the declaration, conversion and removing unnecessary prefixes yields:

Private Declare Function GetTempPath Lib “libraryname”  Alias “GetTempPathA” _ (ByVal nBufferLength As Long, ByVal Buffer As String) As Long

The only thing that the declaration doesn't tell you is the DLL that contains the function. Looking at the bottom of the MSDN page, the “Requirements” section includes the line:

Library: Use kernel32.lib.

This tells you that the function is in the file kernel32.dll, giving the final declaration of:

Private Declare Function GetTempPath Lib “kernel32.dll” Alias “GetTempPathA” _ (ByVal nBufferLength As Long, ByVal Buffer As String) As Long

The resolved function declaration is almost identical to the declaration defined in the win32api.txt file, which should be your first reference point for all API function definitions.

Constants, Structures, Handles, and Classes

Most of the API functions include arguments that accept a limited set of predefined constants. For example, to get information about the operating system's capabilities, you can use the GetSystemMetrics function:

Declare Function GetSystemMetrics Lib “user32” ( _
        ByVal Index As Long) As Long

Note that in the win32api.txt file, the GetSystemMetrics function is shown including an Alias clause:

Declare Function GetSystemMetrics Lib “user32” Alias “GetSystemMetrics” ( _
        ByVal nIndex As Long) As Long

The Alias clause is not required when the function name is the same as the alias, and is automatically removed when the function is copied into a code module.

The value that you pass in the Index argument tells the function which metric you want to be given, and must be one of a specific set of constants that the function knows about. The applicable constants are listed in the MSDN documentation, but their values are often not shown. Fortunately, the win32api.txt file contains most of the constants that you are likely to need. There are over 80 constants for GetSystemMetrics including SM_CXSCREEN and SM_CYSCREEN to retrieve the screen's dimensions:

Private Const SM_CXSCREEN As Long = 0
Private Const SM_CYSCREEN As Long = 1

Private Declare Function GetSystemMetrics Lib “user32” _
                (ByVal Index As Long) As Long

Public Sub ShowScreenDimensions()
   Dim X As Long
   Dim Y As Long

   X = GetSystemMetrics(SM_CXSCREEN)
   Y = GetSystemMetrics(SM_CYSCREEN)

   Call MsgBox(“Screen resolution is ” & X & “x” & Y)
End Sub

Many of the Windows API functions pass information using structures, which is the C term for a User-Defined Type (UDT). For example, the GetWindowRect function is used to return the size of a window, and is defined as:

Declare    Function GetWindowRect Lib “user32” (
  ByVal hwnd As Long, _
  ByRef lpRect As Rect) As Long

The lpRect parameter is a RECT structure that is filled in by the GetWindowRect function with the window's dimensions. The RECT structure is defined on MSDN as:

typedef    struct tagRECT {
   LONG left;
   LONG top;
   LONG right;
   LONG bottom;
} RECT;

This can be converted to a VBA user-defined type using the same datatype conversion shown in the previous section, giving:

Private Type Rect
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type

The UDT definitions for most of the common structures are also included in the win32api.txt file.

The first parameter of the GetWindowRect function is shown as ‘hwnd’, and represents a handle to a window. A handle is simply a pointer to an area of memory that contains information about the object being pointed to (in this case, a window). Handles are allocated dynamically by Windows and are unlikely to be the same between sessions. You cannot, therefore, hardcode the handle number in your code, but must use other API functions to give you the handle you need. For example, to obtain the dimensions of the Excel window, you need to get the Excel window's hwnd. The API function FindWindow gives it to you:

'API call to find a window
Private Declare Function FindWindow Lib “user32” _
  Alias “FindWindowA” (ByVal ClassName As String, _
  ByVal WindowName As String) As Long

This function looks through all the open windows until it finds one with the class name and caption that you ask for. In Excel 2002, the hWnd property has been added to the Application object, so we no longer need to use FindWindow for that case. All of the code examples in this chapter use FindWindow, to be compatible with previous versions of Excel.

There are many different types of windows in Windows applications, ranging from Excel's application window, to the windows used for dialog sheets, UserForms, ListBoxes, or buttons. Each type of window has a unique identifier, known as its class. Some common class names in Excel are:

The FindWindow function requires the appropriate class name and the window's caption to find the window.

Note that the class names for some of Excel's standard items have changed with every release of Excel. You therefore need to include version checking in your code to determine which class name to use:

Select Case Val(Application.Version)
  Case Is >= 11 'Use Excel 2003 class names
  Case Is >= 9  'Use Excel 2000/2002 class names
  Case Is >= 8 'Use Excel 97 class names
  Case Else           'Use Excel 5/95 class names
End Select

This gives us a forward-compatibility problem. It would be nice if we could write code with a reasonable amount of confidence that it will work in the next version of Excel, but we don't know what the class names are going to be. Fortunately, the class names didn't change between Excel 2000 and Excel 2003.

Putting these items together, you can use the following code to find the location and size of the Excel main window (in pixels):

Private Declare Function FindWindow Lib “user32” _
   Alias “FindWindowA” (ByVal ClassName As String, _
   ByVal WindowName As String) As Long
Declare Function GetWindowRect Lib “user32” ( _
  ByVal hWnd As Long, _
  Rect As Rect) As Long

Private Type Rect
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type

Public Sub ShowExcelWindowSize()
  Dim hWnd As Long, aRect As Rect
  hWnd = FindWindow(“XLMAIN”,  Application.Caption)
  Call GetWindowRect(hWnd, aRect)
  Call PrintRect(aRect)
End Sub

Private Sub PrintRect(ByRef aRect As Rect)
  Call MsgBox(“The Excel window has the following dimensions:“ &_
    vbCrLf & “ Left: “ & aRect.Left & _
    vbCrLf & “ Right: “ & aRect.Right & _
    vbCrLf & “ Top: “ & aRect.Top & _
    vbCrLf & “ Bottom: “ & aRect.Bottom & _
    vbCrLf & “ Width: “ & (aRect.Right - aRect.Left) & _
    vbCrLf & “ Height: “ & (aRect.Bottom - aRect.Top))
End Sub

Resize the Excel window to cover a portion of the screen and run the ShowExcelWindowSize routine. You should be given a message box showing the window's dimensions. Now, try it with Excel maximized – you may get negative values for the top and left. This is because the GetWindowRect function returns the size of the Excel window, measuring around the edge of its borders. When maximized, the borders are off the screen, but still part of the window.

What if Something Goes Wrong?

One of the hardest parts of working with the Windows API functions is identifying the cause of any errors. If an API call fails for any reason, it should return some indication of failure (usually a zero result from the function) and register the error with Windows. You should then be able to use the VBA function Err.LastDLLError to retrieve the error code and use the FormatMessage API function to retrieve the descriptive text for the error:

Private Const FORMAT_MESSAGE_FROM_SYSTEM As Long = &H1000

Private Declare Function FindWindow Lib “user32” _
  Alias “FindWindowA” (ByVal ClassName As String, _
  ByVal WindowName As String) As Long
Declare Function GetWindowRect Lib “user32” ( _
  ByVal hWnd As Long, _
  Rect As Rect) As Long

Private    Declare Function FormatMessage Lib “kernel32” _
  Alias    “FormatMessageA” (ByVal dwFlags As Long, _
  ByVal    Source As Long, ByVal MessageId As Long, _
  ByVal    LanguageId As Long, ByVal Buffer As String, _
  ByVal    Size As Long, ByVal Arguments As Long) As Long

Private Type Rect
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type

Private Sub PrintRect(ByRef aRect As Rect)
  Call MsgBox(“Dimensions:” & _
    vbCrLf & “ Left: “ & aRect.Left & _
    vbCrLf & “ Right: ” & aRect.Right & _
    vbCrLf & “ Top: “ & aRect.Top & _
    vbCrLf & “ Bottom: ” & aRect.Bottom & _
    vbCrLf & “ Width: ” & (aRect.Right - aRect.Left) & _
    vbCrLf & “ Height ” & (aRect.Bottom - aRect.Top))
End Sub

Sub ShowExcelWindowSize()
  Dim hWnd As Long
  Dim aRect As Rect

  hWnd = FindWindow(“XLMAIN”, Application.Caption)
  If hWnd = 0 Then
    Call MsgBox(LastDLLErrText(Err.LastDllError))
  Else
    Call GetWindowRect(hWnd, aRect)
    Call PrintRect(aRect)
  End If
End Sub

Function LastDLLErrText(ByVal ErrorCode As Long) As String
  Dim Buffer As String * 255
  Dim Result As Long

  Result = FormatMessage(FORMAT_MESSAGEFROM_SYSTEM, 0&, ErrorCode, _ 0, Buffer, 255, 0)

  LastDLLErrText = Left(Buffer, Result)
End Function

The full code for this example can be found in the module ‘Module3’ in the API Examples.xls workbook, available on the Wrox Web site at http://www.wrox.com.

Unfortunately, different versions of Windows initialize Err.LastDllError with different values. For example, if you change the class name in the preceding example to XLMAINTEST in the FindWindow function call, you may expect to get an error message of “Unable to find window”. This is the message you will get in Windows NT 4, but when using FindWindow under Windows 98, the error information is not populated and you get the standard message “The operation completed successfully”. In most cases, you do get some error information, as will be seen in the next section.

Wrapping API Calls in Class Modules

If you need to use lots of API calls in your application, your code can get very messy very quickly. Most developers prefer to encapsulate the API calls within class modules, which provides a number of benefits:

• The API declarations and calls are removed from your core application code.
• The class module can perform a number of initialization and clean-up tasks, improving your system's robustness.
• Many of the API functions take a large number of parameters, some of which may be identical for each repetition of the API function call. The class module need expose only those properties that need to be changed by your calling routine.
• Class modules can be stored as text files or in the Code Librarian (if you're using Office Developer), providing a self-contained set of functionality that is easy to reuse in future projects.

The following code is an example of a class module for working with temporary files, allowing the calling code to:

• Create a temporary file in the Windows default TEMP directory
• Create a temporary file in a user-specified directory
• Retrieve the path and file name of the temporary file
• Retrieve the text of any errors that may have occurred while creating the temporary file
• Delete the temporary file after use.

Create a class module called TempFile and copy in the following code (this class can also be found in the API Examples.xls file, on the Wrox Web site at http://www.wrox.com):

Option Explicit

Private Declare Function GetTempPath Lib “kernel32” _
  Alias “GetTempPathA” ( _
  ByVal BufferLength As Long, _
  ByVal Buffer As String) As Long

Private Declare Function GetTempFileName Lib “kernel32” _
  Alias “GetTempFileNameA” ( _
  ByVal Path As String, _
  ByVal PrefixString As String, _
  ByVal Unique As Long, _
  ByVal TempFileName As String) As Long

Private    Declare Function FormatMessage Lib “kernel32” _
  Alias    “FormatMessageA” ( _
  ByVal    Flags As Long, _
  ByVal    Source As Long, _
  ByVal    MessageId As Long, _
  ByVal    LanguageId As Long, _
  ByVal    Buffer As String, _
  ByVal    Size As Long, _
  ByVal    Arguments As Long) As Long

Const FORMAT_MESSAGE_FROM_SYSTEM As Long = &H1000

Dim TempPath As String
Dim TempFile As String
Dim ErrorMessage As String
Dim TidyUp As Boolean

One advantage of using a class module is that you can perform some operations when the class is initialized. In this case, you will identify the default Windows TEMP directory. The temporary file will be created in this directory, unless the calling code tells you otherwise:

Private Sub Class_Initialize()
  Dim Buffer As String * 255
  Dim Result As Long
  Result = GetTempPath(255, Buffer)

  If Result = 0 Then
    ErrorMessage = LastDLLErrText(Err.LastDllError)
  Else
    TempPath = Left(Buffer, Result)
  End If
End Sub

This is the routine to create the temporary file, returning its name (including the path). In its simplest use, the calling routine can just call this one method to create a temporary file:

Public Function CreateFile() As String
  Dim Buffer As String * 255
  Dim Result As Long

  Result = GetTempFileName(TempPath, “”, 0, Buffer)

  If Result = 0 Then
    ErrorMessage = LastDLLErrText(Err.LastDllError)
  Else
    TempFile = Left(Buffer, InStr(1, Buffer, Chr(0)) -    1)
    ErrorMessage = “OK”
    TidyUp = True
    CreateFile = TempFile
  End If
End Function

In a class module, you can expose a number of properties that allow the calling routine to retrieve and modify the temporary file creation. For example, you may want to enable the calling program to set which directory to use for the temporary file. You could extend this to make the property read-only after the file has been created, raising an error in that case. The use of Property procedures in class modules was described in more detail in Chapter 6:

Public Property Get Path() As String
  Path = Left(TempPath, Len(TempPath) - 1)
End Property

Public Property Let Path(ByVal NewPath As String)
  TempPath = NewPath
  If Right(TempPath, 1) <> “” Then
    TempPath = TempPath & “”
  End If
End Property

You can also give the calling routine read-only access to the temporary file's name and full name (that is, including the path):

Public Property Get Name() As String
  Name = Mid(TempFile, Len(TempPath) + 1)
End Property

Public Property Get FullName() As String
  FullName = TempFile
End Property

Give the calling program read-only access to the error messages:

Public Property Get ErrorText() As String
  ErrorText = ErrorMessage
End Property

You'll also allow the calling program to delete the temporary file after use:

Public Sub Delete()
  On Error Resume Next
  Kill TempFile
  TidyUp = False
End Sub

By default, you will delete the temporary file that you created when the class is destroyed. The calling application may not want you to, so provide some properties to control this:

Public Property Get TidyUpFiles() As Boolean
  TidyUpFiles = TidyUp
End Property

Public Property Let TidyUpFiles(ByVal IsNew As Boolean)
  TidyUp = IsNew
End Property

In the class's Terminate code, you'll delete the temporary file, unless told not to. This code is run when the instance of the class is destroyed. If declared within a procedure, this will be when the class variable goes out of scope at the end of the procedure. If declared at a module level, it will occur when the workbook is closed:

Private Sub Class_Terminate()
   If TidyUp Then Delete
End Sub

The same function you saw in the previous section is used to retrieve the text associated with a Windows API error code:

Private Function LastDLLErrText(ByVal ErrorCode As Long) As String
  Dim Buffer As String * 255
  Dim Result As Long

  Result = FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM, _
  0&, ErrorCode, 0, Buffer, 255, 0)

  LastDLLErrText = Left(Buffer, Result)
End Function

Once this class module is included in a project, the calling routine does not need to know anything about any of the API functions you're using:

Public Sub TestTempFile()
  Dim Object As New TempFile

  If Object.CreateFile = “” Then
    Call MsgBox(“An error occured while creating the temporary file:” & _
      vbCrLf & obTempFile.ErrorText)
  Else
    Call MsgBox(“Temporary file ” & Object.FullName & “ created”)
  End If
End Sub

The results on Windows XP (and should be close on other versions of Windows) are similar to:

Temporary file C: WINDOWS TEMP 5024.TMP created

Note that the temporary file is created during the call to CreateFile. When the procedure ends, the variable Object goes out of scope and hence is destroyed by VBA. The Terminate event in the class module ensures the temporary file is deleted – the calling procedure does not need to know about any clean-up routines. If CreateFile is called twice, only the last temporary file is deleted. A new instance of the class should be created for each temporary file required.

You can force an error by amending TestTempFile to specify a nonexistent directory for the temp file:

Public Sub TestTempFile()
  Dim Object As New TempFile
  Object.Path = “C: NoSuchPath”

  If Object.CreateFile = “” Then
    Call MsgBox(“An error occured while creating the temporary file:” &_
      Chr(10) & Object.ErrorText)
  Else
    Call MsgBox(“Temporary file “    & Object.FullName & “ created”)
  End If
End Sub

This time, you get a meaningful error message as shown in Figure 16-1:

Figure 16-1

Some Example Classes

This section provides a number of common API calls to include in your projects. Note that in each case the function and constant definitions must be put in the Declarations section at the top of a module.

A High-Resolution Timer Class

When testing your code, it can be helpful to time the various routines, in order to identify and eliminate any bottlenecks. VBA includes two functions that can be used as timers:

• The Now function returns the current time and has a resolution of about 1 second
• The Timer function returns the number of milliseconds since midnight, with a resolution of approximately 10 milliseconds

Neither of these are accurate enough to time VBA routines, unless the routine is repeated many times.

Most modern PCs include a high-resolution timer, which updates many thousands of times per second, accessible through API calls. You can wrap these calls in a class module to provide easy access to a high-resolution timer.

Class Module HighResTimer

Option Explicit

Private Declare Function QueryFrequency Lib “kernel32” _
  Alias “QueryPerformanceFrequency” ( _
  ByRef Frequency As Currency) As Long

Private Declare Function QueryCounter Lib “kernel32” _
  Alias “QueryPerformanceCounter” ( _
  ByRef PerformanceCount As Currency) As Long

Note that the win32api.txt file shows these definitions using the ‘LARGE_INTEGER’ data type, but they are defined as Currency in the preceding code. The LARGE_INTEGER is a 64-bit data type, usually made up of two Longs. The VBA Currency data type also uses 64-bits to store the number, so you can use it in place of a LARGE_INTEGER. The only differences are that the Currency data type is scaled down by a factor of 10,000 and that VBA can perform standard math operations with Currency variables:

Dim Frequency As Currency
Dim Overhead As Currency
Dim Started As Currency
Dim Stopped As Currency

The API call itself takes a small amount of time to complete. For accurate timings, you should take this delay into account. You find this delay and the counter's frequency in the class's Initialize routine:

Private Sub Class_Initialize()
  Dim Count1 As Currency
  Dim Count2 As Currency
  Call QueryFrequency(Frequency)
  Call QueryCounter(Count1)
  Call QueryCounter(Count2)
  Overhead = Count2 - Count1
End Sub

Public Sub StartTimer()
  QueryCounter Started
End Sub

Public Sub StopTimer()
  QueryCounter Stopped
End Sub

Public Property Get Elapsed() As Double
  Dim Timer As Currency

  If Stopped = 0 Then
    QueryCounter Timer
  Else
    Timer = Stopped
  End If

  If Frequency > 0 Then
    Elapsed = (Timer - Started - Overhead) / Frequency
  End If
End Property

When you calculate the elapsed time, both the timer and the frequency contain values that are a factor of 10,000 too small. As the numbers are divided, the factors cancel out to give a result in seconds.

The High-Resolution Timer class can be used in a calling routine like:

Sub TestHighResTimer()
  Dim I As Long
  Dim Object As New HighResTimer

  Object.StartTimer

  For I = 1 To 10000
  Next I

  Object.StopTimer

  Debug.Print “10000 iterations took ” & Object.Elapsed & “ seconds”
End Sub

Freeze a UserForm

When working with UserForms, the display may be updated whenever a change is made to the form, such as adding an item to a ListBox, or enabling/disabling controls. Application.ScreenUpdating has no effect on UserForms; this class provides a useful equivalent.

Class Module FreezeForm

Option Explicit

Private Declare Function FindWindow Lib “user32” _
  Alias “FindWindowA” ( _
  ByVal ClassName As String, _
  ByVal WindowName As String) As Long

Private Declare Function LockWindowUpdate Lib “user32” ( _
  ByVal hwndLock As Long) As Long

Public Sub Freeze(Form As UserForm)
  Dim hWnd As Long

  If Val(Application.Version) >= 9 Then
    hWnd = FindWindow(“ThunderDFrame”, Form.Caption)
  Else
    hWnd = FindWindow(“ThunderXFrame”, Form.Caption)
  End If
  If hWnd > 0 Then LockWindowUpdate hWnd
End Sub

Public Sub UnFreeze()
  LockWindowUpdate 0
End Sub

Private Sub Class_Terminate()
  UnFreeze
End Sub

To demonstrate this in action, create a new UserForm and add a listbox and a command button. Add the following code for the command button's Click event:

Private Sub CommandButton1_Click()
  Dim I As Integer
  For I = 1 To 1000
    ListBox1.AddItem “Item “ & I
    DoEvents
  Next I
End Sub

The DoEvents line forces the UserForm to redraw, to demonstrate the problem. In more complicated routines, the UserForm may redraw itself without using DoEvents. To prevent the redrawing, you can modify the routine to use the FreezeForm class as shown:

Private Sub CommandButton1_Click()
  Dim Freezer As New FreezeForm
  Freezer.Freeze Me

  Dim I As Integer
  For I = 1 To 1000
    ListBox1.AddItem “Item ” & I
    DoEvents
  Next I
End Sub

This is much easier than including several API calls in every function. The class's Terminate event ensures that the UserForm is unfrozen when the Freezer object variable goes out of scope. Freezing a UserForm in this way can result in a dramatic performance improvement. For example, the nonfrozen version takes approximately 3.5 seconds to fill the ListBox, while the frozen version of the routine takes approximately 1.2 seconds. This should be weighted against user interaction; they may think the computer has frozen if they see no activity for some time. Consider using Application.StatusBar to keep them informed of progress in that case.

A System Info Class

The classic use of a class module and API functions is to provide all the information about the Windows environment that you cannot get at using VBA. The following properties are typical components of such a SysInfo class.

Obtaining the Screen Resolution (in Pixels)

Option Explicit

Private Const SM_CYSCREEN As Long = 1 'Screen height
Private Const SM_CXSCREEN As Long = 0 'Screen width

Private Declare Function GetSystemMetrics Lib “user32” ( _
  ByVal Index As Long) As Long

Public Property Get ScreenHeight() As Long
  ScreenHeight = GetSystemMetrics(SM_CYSCREEN)
End Property

Public Property Get ScreenWidth() As Long
  ScreenWidth = GetSystemMetrics(SM_CXSCREEN)
End Property

Obtaining the Color Depth (in Bits)

Private    Declare    Function GetDC Lib “user32” ( _
  ByVal hwnd As    Long) As Long

Private Declare Function GetDeviceCaps Lib “Gdi32” ( _
  ByVal hDC As Long, _
  ByVal Index As Long) As Long

Private Declare Function ReleaseDC Lib “user32” ( _
  ByVal hwnd As Long, _
  ByVal hDC As Long) As Long

Private Const BITSPIXEL = 12

Public Property Get ColourDepth() As Integer
  Dim hDC As Long
  hDC = GetDC(0)
  ColourDepth = GetDeviceCaps(hDC, BITSPIXEL)
  Call ReleaseDC(0, hDC)
End Property

Obtaining the Width of a Pixel in UserForm Coordinates

Private Declare Function GetDC Lib “user32” ( _
  ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib “Gdi32” ( _
  ByVal hDC As Long, _
  ByVal Index As Long) As Long

Private Declare Function ReleaseDC Lib “user32” ( _
  ByVal hwnd As Long, _
  ByVal hDC As Long) As Long

Private Const LOGPIXELSX = 88

Public Property Get PointsPerPixel() As Double
  Dim hDC As Long

  hDC = GetDC(0)

  'A point is defined as 1/72 of an inch and LOGPIXELSX returns
  'the number of pixels per logical inch, so divide them to give
  'the width of a pixel in Excel's UserForm coordinates
  PointsPerPixel = 72 / GetDeviceCaps(hDC, LOGPIXELSX)

  Call ReleaseDC(0, hDC)
End Property

Reading the User's Login ID

Private Declare Function GetUserName Lib “advapi32.dll” _
  Alias    “GetUserNameA” ( _
  ByVal    Buffer As String, _
  ByRef    Size As Long) As Long

Public Property Get UserName() As String
  Dim Buffer As String * 255
  Dim Result As Long
  Dim Length As Long

  Length = 255

  Result = GetUserName(Buffer, Length)
  If Length > 0 Then UserName = Left(Buffer, Length - 1)
End Property

Reading the Computer's Name

Private Declare Function GetComputerName Lib “kernel32” _
  Alias “GetComputerNameA” ( _
  ByVal Buffer As String, _
  Size As Long) As Long

Public Property Get ComputerName() As String
  Dim Buffer As String * 255
  Dim Result As Long
  Dim Length As Long

  Length = 255
  Result = GetComputerName(Buffer, Length)
  If Length > 0 Then ComputerName = Left(Buffer, Length)
End Property

These can be tested by using the following routine (in a standard module):

Public Sub TestSysInfo()
  Dim Object As New SysInfo
  Debug.Print “Screen Height = ” & Object.ScreenHeight
  Debug.Print “Screen Width = ” & Object.ScreenWidth
  Debug.Print “Colour Depth = ” & Object.ColourDepth
  Debug.Print “One pixel = ” & Object.PointsPerPixel & “ points”
  Debug.Print “User name = ” & Object.UserName
  Debug.Print “Computer name = ” & Object.ComputerName
End Sub

Modifying UserForm Styles

UserForms in Excel do not provide any built-in mechanism for modifying their appearance. Our only choice is a simple popup dialog box with a caption and an x button to close the form, though we can choose to show it modally or non-modally.

Using API calls, we can modify the UserForm's window, to do any combination of:

• Switching between modal and non-modal while the form is showing
• Making the form resizable
• Showing or hiding the form's caption and title bar
• Showing a small title bar, like those on a floating toolbar
• Showing a custom icon on the form
• Showing an icon in the task bar for the form
• Removing the x button to close the form
• Adding standard maximize and/or minimize buttons

An example workbook demonstrating all these choices can be found on the Wrox Web site at http://www.wrox.com, with the key parts of the code explained next.

Windows Styles

The appearance and behavior of a window is primarily controlled by its style and extended style properties. These styles are both Long values, in which each bit of the value controls a specific aspect of the window's appearance, either on or off. We can change the window's appearance using the following process:

• Use FindWindow to get the UserForm's window handle
• Read its style using the GetWindowLong function
• Toggle one or more of the style bits
• Set the window to use this modified style using the SetWindowLong function
• For some changes, tell the window to redraw itself using the ShowWindow function

Some of the main constants for each bit of the basic window style are:

'Style to add a titlebar
Private Const WS_CAPTION As Long = &HC00000
'Style to add a system menu
Private Const WS_SYSMENU As Long = &H80000

'Style to add a sizable frame
Private Const WS_THICKFRAME As Long = &H40000

'Style to add a Minimize box on the title bar
Private Const WS_MINIMIZEBOX As Long = &H20000

'Style to add a Maximize box to the title bar
Private Const WS_MAXIMIZEBOX As Long = &H10000

'Cleared to show a task bar icon
Private Const WS_POPUP As Long = &H80000000

'Cleared to show a task bar icon
Private Const WS_VISIBLE As Long = &H10000000

While some of those for the extended window style are:

'Controls if the window has an icon
Private Const WS_EX_DLGMODALFRAME As Long = &H1

'Application Window: shown on taskbar
Private Const WS_EX_APPWINDOW As Long =    &H40000

'Tool Window: small titlebar
Private Const WS_EX_TOOLWINDOW As Long    = &H80

Note that this is only a subset of all the possible window style bits. See the MSDN documentation for Window Styles for the full list (http://msdn.microsoft.com/library/psdk/winui/windows_2v90.htm) and the win32api.txt file for their values.

The following example uses the above process to remove a UserForm's close button and can be found in the NoCloseButton.xls example in the code download:

Private Const WS_CAPTION As Long = &HC00000
Private Const WS_SYSMENU As Long = &H80000
Private Const WS_THICKFRAME As Long = &H40000
Private Const WS_MINIMIZEBOX As Long = &H20000
Private Const WS_MAXIMIZEBOX As Long = &H10000
Private Const WS_POPUP As Long = &H80000000
Private Const WS_VISIBLE As Long = &H10000000
Private Const WS_EX_DLGMODALFRAME As Long = &H1
Private Const WS_EX_APPWINDOW As Long = &H400 00
Private Const WS_EX_TOOLWINDOW As Long = &H80

Private Declare Function FindWindow Lib “user32” _
  Alias “FindWindowA” ( _
  ByVal ClassName As String, _
  ByVal WindowName As String) As Long

Private Declare Function GetWindowLong Lib “user32” _
  Alias    “GetWindowLongA” ( _
  ByVal    hWnd As Long, _
  ByVal    Index As Long) As Long

Private Declare Function SetWindowLong Lib “user32” _
  Alias “SetWindowLongA” ( _
  ByVal hWnd As Long, _
  ByVal Index As Long, _
  ByVal NewLong As Long) As Long

Const GWL_STYLE = -16

Private Sub UserForm_Initialize()
  Dim hWnd As Long
  Dim Style As Long

  If Val(Application.Version) >= 9 Then
    hWnd = FindWindow(“ThunderDFrame”, Me.Caption)
  Else
    hWnd = FindWindow(“ThunderXFrame”, Me.Caption)
  End If
  Style = GetWindowLong(hWnd, GWL_STYLE)
  Style = (Style And Not WS_SYSMENU)
  SetWindowLong hWnd, GWL_STYLE, Style
End Sub

The effect of the preceding code can be seen in Figure 16-2.

Figure 16-2

The FormChanger Class

As mentioned previously in this chapter, API calls are much easier to use when they are encapsulated within a class module. The FormChanger class included in the FormFun.xls file on the Wrox Web site (at http://www.wrox.com) repeats the preceding code snippet for all the windows style bits mentioned in the previous section, presenting them as the following properties of the class:

• Modal
• Sizeable
• ShowCaption
• SmallCaption
• ShowIcon
• IconPath (to show a custom icon)
• ShowCloseBtn
• ShowMaximizeBtn
• ShowMinimizeBtn
• ShowSysMenu
• ShowTaskBarIcon

To use the class on your own forms, copy the entire class module into your project and call it from your form's Activate event, as in the following example. This example can be found in the ToolbarForm.xls workbook at http://www.wrox.com:

Private Sub UserForm_Activate()
  Dim Object As FormChanger
  Set Object = New FormChanger
  Object.SmallCaption = True
  Object.Sizeable = True
  Set Object.Form = Me
End Sub

Resizable Userforms

In Office XP, Microsoft made the File Open and Save As dialogs resizable. They remember their position and size between sessions, greatly improving their usability. Using the same API calls shown in the previous section and a class module to do all the hard work, you can give your users the same experience when interacting with your UserForms.

One of the curiosities of the UserForm object is that it has a Resize event, but it doesn't have a property to specify whether or not it is resizable – in theory, the Resize event will never fire. As shown in the previous example, you can provide your own Sizeable property by toggling the WS_THICKFRAME window style bit; when you do this, the Userform_Resize event comes to life, triggered every time the user changes the size of the form (though not when they move it around the screen). You can respond to this event by changing the size and/or position of all the controls on the form, such that they make the best use of the UserForm's new size.

There are two approaches that can be used to change the size and/or position of all the controls on the form, absolute and relative.

Absolute Changes

Using an absolute approach, code has to be written to set the size and position of all the controls on the form, relative to the form's new dimensions and to each other. Consider a very simple form showing just a ListBox and an OK button (Figure 16-3):

Figure 16-3

The code to resize and reposition the two controls using absolute methods is:

Private Sub UserForm_Resize()
  Const Gap = 6
  On Error Resume Next
  CommandButton1.Left = (Me.InsideWidth - CommandButton1.Width) / 2
  CommandButton1.Top = Me.InsideHeight - Gap - CommandButton1.Height
  ListBox1.Width = Me.InsideWidth - Gap    * 4
  ListBox1.Height = CommandButton1.Top - Gap * 2
End Sub

It works, but has a few major problems:

• Specific code has to be written for every control that changes size and/or position, which can be a daunting task for more complex forms. See the resize code in the FormFun.xls for an example of this.
• The size and position of controls are often dependent on the size and position of other controls (such as the height of the ListBox being dependent on the top of the OK button in the example above).
• If you modify the appearance of the form by adding or moving controls, you have to make corresponding changes to the resize code. For example, to add a Cancel button alongside the OK button, you have to add code to handle the Cancel button's repositioning and also change the code for the OK button.
• There is no opportunity for code reuse.

Relative Changes

Using a relative approach, information is added to each control to specify by how much that control's size and position should change as the UserForm's size changes. In the same dialog box, the two controls have the following relative changes:

• The OK button should move down by the full change in the form's height (to keep it at the bottom)
• The OK button should move across by half the change in the form's width (to keep it in the middle)
• The List's height and width should change by the full change in the form's height and width

These statements can be encoded into a single string to state the percentage change for each control's Top, Left, Height, and Width properties, and stored against the control. A convenient place to store it is the control's Tag property, which allows the resizing behavior of the control to be set at design time. Using the letters T, L, H, and W for the four properties, and a decimal for the percentage change if not 100 percent, gives the following Tag properties for the simple form:

Tag=HW
Tag=HL0.5

When the UserForm_Resize event fires, the code can calculate the change in the form's height and width and iterate through all the controls adjusting their Top, Left, Height, and Width as specified by their Tags. The CFormResizer class to do this is shown next.

There are a number of benefits to this approach:

• The resize behavior of each control is set at design time, while the form is being viewed, just like all the other properties of the control
• The change in size and position of each control is independent of any other control
• Controls can be added, moved, or deleted without having to modify the Resize code or change other controls' resize behavior.
• The resize code can treat every control in exactly the same way, hence
• Every UserForm uses exactly the same Resize code, which can be encapsulated in a separate class module

The FormResizer Class

By encapsulating all the resize code in a separate class module, any UserForm can be made resizable by adding just six lines of code, to instantiate and call into the class, and setting the resize behavior for each control in its Tag property.

The FormResizer class provides the following functionality:

• Sets the form to be resizable
• Sets the initial size and position of the form, if it has been shown before
• Resizes and repositions all the controls on the form, according to their Tag resizing string
• Stores the form's size and position in the registry, for use when the same form is shown again
• Allows the calling code to specify a key name for storing the form dimensions in the registry
• Prevents a form being resized in either direction if none of the controls are set to respond to changes in height and/or width
• Stops resizing when any control is moved to the left or top edge of the form, or when any control is reduced to zero height or width

The code for the FormResizer class is shown next, with comments in the code to explain each section. It is available for download in the FormResizer.xls workbook at http://www.wrox.com:

Option Explicit

Private Declare Function FindWindow Lib “user32” _
  Alias “FindWindowA” ( _
  ByVal ClassName As String, _
  ByVal WindowName As String) As Long

Private Declare Function GetWindowLong Lib “user32” _
  Alias    “GetWindowLongA” ( _
  ByVal    hWnd As Long, _
  ByVal Index As Long) As Long

Private Declare Function SetWindowLong Lib “user32” _
  Alias “SetWindowLongA” ( _
  ByVal hWnd As Long, _
  ByVal Index As Long, _
  ByVal NewLong As Long) As Long

Private Const GWL_STYLE As Long = (-16)

Private Const WS_THICKFRAME As Long = &H40000

Dim FormField As Object
Dim FormHandle As Long
Dim Width As Double
Dim Height As Double
Dim RegistryKeyField As String

Private Sub Class_Initialize()
  RegistryKey = “Excel 2003 Programmers Reference”
End Sub

Public Property Let RegistryKey(ByVal Value As String)
  RegistryKeyField = Value
End Property

Public Property Get RegistryKey() As String
  RegistryKey = RegistryKeyField
End Property

Public Property Get Form() As Object
  Set Form = FormField
End Property

Public Property Set Form(Value As Object)
  Dim StringSizes As String
  Dim Sizes As Variant
  Dim Style As Long

  Set FormField = Value
  If Val(Application.Version) < 9 Then
    'Excel 97
    FormHandle = FindWindow(“ThunderXFrame”, FormField.Caption)
  Else
    ' Newer versions of Excel, including Excel 2003
    FormHandle = FindWindow(“ThunderDFrame”, FormField.Caption)
  End If

  Style = GetWindowLong(FormHandle, GWL_STYLE)
  Style = Style Or WS_THICKFRAME
  Call SetWindowLong(FormHandle, GWL_STYLE, Style)

  StringSizes = GetSetting(RegistryKey, “Forms”,  FormField.Name, “”)

  Width = FormField.Width
  Height = FormField.Height

  If StringSizes <> “” Then
    Sizes = Split(StringSizes, “;”)
    ReDim Preserve Sizes(0 To 3)
    FormField.Top = Val(Sizes(0))
    FormField.Left = Val(Sizes(1))
    FormField.Height = Val(Sizes(2))
    FormField.Width = Val(Sizes(3))
    FormField.StartUpPosition = 0
  End If

End Property

Public Sub FormResize()
  Dim WidthAdjustment As Double
  Dim HeightAdjustment As Double
  Dim SomeWidthChange As Boolean
  Dim SomeHeightChange As Boolean
  Dim Tag As String
  Dim Size As String
  Dim Control As MSForms.Control

  Static Resizing As Boolean

  If Resizing Then Exit Sub
  Resizing = True
  On Error GoTo Finally

  HeightAdjustment = Form.Height - Height
  WidthAdjustment = Form.Width - Width

  For Each Control In Form.Controls
    Tag = UCase(Control.Tag)
    If InStr(1, Tag, “T”,  vbBinaryCompare) Then
      If Control.Top + HeightAdjustment * ResizeFactor(Tag, “T”) <= 0 Then
        Form.Height = Height
      End If

      SomeHeightChange = True
    End If

    If InStr(1, Tag, “L”,  vbBinaryCompare) Then
      If Control.Left + WidthAdjustment * ResizeFactor(Tag, “L”) <= 0 Then
        Form.Width = Width
      End If

      SomeWidthChange = True
    End If

    If InStr(1, Tag, “H”,  vbBinaryCompare) Then
      If Control.Height + HeightAdjustment * ResizeFactor(Tag, “H”) <= 0 Then
        Form.Height = Height
      End If

      SomeHeightChange = True
    End If

    If InStr(1, Tag, “W”, vbBinaryCompare) Then
      If Control.Width + WidthAdjustment * ResizeFactor(Tag, “W”) <= 0 Then
        Form.Width = Width
      End If

      SomeWidthChange = True
    End If
  Next
  If Not SomeHeightChange Then Form.Height = Height
  If Not SomeWidthChange Then Form.Width = Width

  HeightAdjustment = Form.Height - Height
  WidthAdjustment = Form.Width - Width

  For Each Control In Form.Controls
    With Control
      Tag = UCase(.Tag)

      If InStr(1, Tag, “T”,  vbBinaryCompare) Then
        .Top = .Top + HeightAdjustment * ResizeFactor(Tag, “T”)
      End If

      If InStr(1, Tag, “L”,  vbBinaryCompare) Then
        .Left = .Left + WidthAdjustment * ResizeFactor(Tag, “L”)
      End If

      If InStr(1, Tag, “H”,  vbBinaryCompare) Then
        .Height = .Height + HeightAdjustment * ResizeFactor(Tag, “H”)
      End If

      If InStr(1, Tag, “W”,  vbBinaryCompare) Then
        .Width = .Width + WidthAdjustment * ResizeFactor(Tag, “W”)
      End If
    End With
  Next

  Width = Form.Width
  Height = Form.Height

  With Form
    Call SaveSetting(RegistryKey, “Forms”, .Name, Str(.Top) & “;” & _
      Str(.Left) & “;” & Str(.Height) & “;” & Str(.Width))
  End With

Finally:
  Resizing = False

End Sub

Private Function ResizeFactor(ByVal Tag As String, ByVal Change As String)
  Dim I As Integer
  Dim D As Double

  I = InStr(1, Tag, Change, vbBinaryCompare)

  If I > 0 Then
    D = Val(Mid$(Tag, I + 1))
    If D = 0 Then D = 1
  End If

  ResizeFactor = D
End Function

Using the FormResizer Class

The code to use the FormResizer class in a UserForm's code module is as follows:

Dim Resizer As FormResizer

Private Sub UserForm_Initialize()
  Set Resizer = New FormResizer
  Resizer.RegistryKey = “Excel 2003 Programmers Reference”
  Set Resizer.Form = Me
End Sub

Private Sub UserForm_Resize()
  Resizer.FormResize
End Sub

Private Sub btnOK_Click()
  Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  Resizer.FormResize
End Sub

There are a few points to remember when using this approach in your own UserForms:

• The resizer works by changing the control's Top, Left, Height, and Width properties in response to changes in the UserForm size, according to the control's resizing information.
• The control's resizing information is set in its Tag property, using the letters T, L, H, and/or W followed by a number specifying the resizing factor (if not 100 %).
• The resizing factors must be in US format, using a period as the decimal separator.
• If there are no controls that have T or H in their Tag strings, the form will not be allowed to resize vertically.
• If there are no controls that have L or W in their Tag strings, the form will not be allowed to resize horizontally.
• The smallest size for the form is set by the first control to be moved to the top or left edge, or to have a zero width or height.
• This can be used to set a minimum size for the form, by using a hidden label with a Tag of “HW”, where the size of the label equals the amount that the form can be reduced in size. If the label is set to zero height and width to start with, the UserForm can only be enlarged from its design-time size.

Other Examples

You are not forced to put all your API calls into class modules, though it is usually a good idea. This section demonstrates a few examples where the API calls would typically be used within a standard module.

Change Excel's Icon

When developing an application that takes over the entire Excel interface, you can use the following code to give Excel your own icon:

Declare Function FindWindow Lib “user32” _
  Alias “FindWindowA” ( _
  ByVal ClassName As String, _
  ByVal WindowName As String) As Long

Declare Function ExtractIcon Lib “shell32.dll” _
  Alias “ExtractIconA” ( _
  ByVal Instance As Long, _
  ByVal ExeFileName As String, _
  ByVal IconIndex As Long) As Long

Declare Function SendMessage Lib “user32” _
  Alias “SendMessageA” ( _
  ByVal hWnd As Long, _
  ByVal Message As Long, _
  ByVal wParam As Integer, _
  ByVal lParam As Long) As Long

Const WM_SETICON = &H80

Public Sub SetExcelIcon(ByVal IconPath As String)
  Dim A As Long
  Dim hWnd As Long
  Dim hIcon As Long

  hWnd = FindWindow(“XLMAIN”, Application.Caption)
  hIcon = ExtractIcon(0, IconPath, 0)

  '1 means invalid icon source
  If hIcon > 1 Then
    'Set the big (32×32) and small (16×16) icons
    Call SendMessage(hWnd, WM_SETICON, True, hIcon)
    Call SendMessage(hWnd, WM_SETICON, False, hIcon)
  End If
End Sub

Public Sub TestExcelIcon()
  Call SetExcelIcon(ThisWorkbook.Path + “myico.ico”)
End Sub

Play a .wav file

Excel does not include a built-in method of playing sounds. This is a simple API call to play a .wav file. The Flags argument can be used to play the sound asynchronously or in a continuous loop, though we use a value of zero in this example to play the sound once, synchronously:

Declare Function sndPlaySound Lib “winmm.dll” _
  Alias “sndPlaySoundA” ( _
  ByVal SoundName As String, _
  ByVal Flags As Long) As Long

Sub PlayWav(ByVal WavFileName As String)
  Call sndPlaySound(WavFileName, 0)
End Sub

Sub TestWav()
  Call PlayWav(ThisWorkbook.Path + “mywav.wav”)
End Sub

Summary

The functions defined in the Windows API provide a valuable and powerful extension to the VBA developer's tool set. The win32api.txt file provides the VBA definitions for most of the core functions. The definitions for the remaining functions can be converted from the C-style versions shown in the online MSDN library.

Class modules enable the user to encapsulate both the API definitions and their use into simple chunks of functionality that are easy to use and reuse in VBA applications. A number of example classes and routines have been provided in this chapter to get you started using the Windows API functions within your applications. These include:

• Creating a TEMP file
• A high-resolution timer
• Freezing a UserForm
• Getting system information
• Modifying a UserForm's appearance
• Making UserForms resizable, with a minimum of code in the form
• Changing Excel's icon
• Playing a .wav file