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 nIndex As Long) As Long

The value that you pass in the nIndex 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, with their corresponding values in many cases. The API Viewer also contains most of the constants that you are likely to need. There are more than 80 constants for GetSystemMetrics, including SM_CXSCREEN and SM_CYSCREEN to retrieve the screen's dimensions:

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

Private Declare Function GetSystemMetrics Lib "user32" _ (ByVal nIndex As Long) As Long

Sub ShowScreenDimensions()

Dim lScreenX As Long, lScreenY As Long

'Get the screen's dimensions lScreenX = GetSystemMetrics(SM_CXSCREEN)

lScreenY = GetSystemMetrics(SM_CYSCREEN)

MsgBox "Screen resolution is " & lScreenX & "x" & lScreenY 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, _

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 (at library/en-us/gdi/rectangl_6cqa.asp)as:


struct tagRECT {










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


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

The first parameter of the GetWindowRect function is shown as hwnd, and it 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, hard code 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 a window, you need to get the window's hwnd. The API function FindWindow gives it to you:

'API call to find a window

Public Declare Function FindWindow Lib "user32" _ Alias "FindWindowA" ( _ ByVal lpClassName As String, _ ByVal lpWindowName 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. The Hwnd property for Excel's main window was added to the Application object in Excel

2002, so you only need to use FindWindow for the main Excel window if you want to be compatible with Excel 2000 or earlier, or if you want to find the window handle for any other type of window (such as UserForms). All of the code examples in this chapter use FindWindow, to be compatible with as many versions of Excel as possible.

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, and buttons. Each type of window has a unique identifier, known as its class. Some common class names in Excel are outlined in the following table.


Class Name

Excel's main window


Excel desktop


Excel worksheet


Excel UserForm

ThunderDFrame (since Excel 2000) ThunderRT6DFrame (since Excel 2000, when running as a COM Add-In) ThunderXFrame (in Excel 97)

Excel status bar


Excel chart window (prior to Excel 2007)


The FindWindow function uses this 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 (but very few between Excel 2000 and 2007). You therefore need to include version checking in your code to determine which class name to use:

Select Case Val(Application.Version)

Case Is >= 9 'Use Excel 2000/2002/2003/2007 class names Case Is >= 8 'Use Excel 97 class names Case Else 'Use Excel 5/95 class names

End Select

This results in a potential forward-compatibility problem: You don't know what the class names are going to be in future versions. Fortunately, Microsoft tries to retain compatibility as much as possible and has kept the same class names in Excel 2007 as prior versions. One of the more important changes is the loss of the EXCELE window. That class was officially used for the Chart Window in previous versions, but was often hijacked as a convenient way of locating a cell's on-screen position (by creating a chart at that cell and reading the position of the EXCELE window).

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

'UDT to hold window dimensions Type RECT

Left As Long

Top As Long Right As Long Bottom As Long End Type

'API function to locate a window Declare Function FindWindow Lib "user32" _ Alias "FindWindowA" ( _ ByVal lpClassName As String, _ ByVal lpWindowName As String) As Long

'API function to retrieve a window's dimensions Declare Function GetWindowRect Lib "user32" ( _ ByVal hWnd As Long, _ lpRect As RECT) As Long

Sub ShowExcelWindowSize()

Dim hWnd As Long, uRect As RECT

'Get the handle on Excel's main window

'Could also use hWnd = Application.Hwnd in Excel 2002+

hWnd = FindWindow("XLMAIN", Application.Caption)

'Get the window's dimensions into the RECT structure GetWindowRect hWnd, uRect

'Display the result

MsgBox "The Excel window has the following dimensions:" & _ vbCrLf & " Left: " & uRect.Left & _ vbCrLf & " Right: " & uRect.Right & _ vbCrLf & " Top: " & uRect.Top & _ vbCrLf & " Bottom: " & uRect.Bottom & _ vbCrLf & " Width: " & (uRect.Right - uRect.Left) & _ vbCrLf & " Height: " & (uRect.Bottom - uRect.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.

0 0

Post a comment