Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Working with Windows

Everything that we see on the screen is either a window or is contained within a window, from the Windows desktop to the smallest popup tooltip. Consequently, if we want to modify something on the screen, we always start by locating its window. The windows are organized into a hierarchy, with the desktop at the root. The next level down includes the main windows for all open applications and numerous system-related windows. Each application then owns and maintains its own hierarchy of windows. Every window is identified by its window handle, commonly referred to as hWnd. By far the best tool for locating and examining windows is the Spy++ utility that is included with Visual Studio. Figure 9-2 shows the Spy++ display for the window hierarchy of a typical Excel session.

Figure 9-2. The Spy++ Display of the Excel Window Hierarchy

Window Classes

As well as showing the hierarchy, the Spy++ display shows three key attributes for each window: the handle (in hexadecimal), the caption and the class. Just like class modules, a window class defines a type of window. Some classes, such as the ComboBox class, are provided by the Windows operating system, but most are defined as part of an application. Each window class is usually associated with a specific part of an application, such as XLMAIN being Excel's main application window. Table 9-1 lists the window classes shown in the Spy++ hierarchy and their uses, plus some other window classes commonly encountered during Excel application development.

Finding Windows

The procedures shown in the sections that follow can be found in the MWindows module of the API Examples.xls workbook.

To work with a window, we first need to find its handle. In Excel 2002, the hWnd property was added to the Application object, giving us the handle of the main Excel application window. In previous versions and for all other top-level windows (that is, windows that are direct children of the desktop), we can use the FindWindow API call, which is defined as follows:

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

To use the FindWindow function, we need to supply a class name and/or a window caption. We can use the special constant vbNullString for either, which tells the function to match on any class or caption. The function searches through all the immediate children of the desktop window (known as top-level windows), looking for any that have the given class and/or caption that we specified. To find the main Excel window in versions prior to Excel 2002, we might use the following:

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

ANSI vs. Unicode and the Alias Clause

You might have noticed that the declaration for FindWindow contains an extra clause that we haven't used beforethe Alias clause. All Windows API functions that have textual parameters come in two flavors: Those that operate on ANSI strings have an A suffix, whereas those that operate on Unicode strings have a W suffix. So while all the documentation and searches on MSDN talk about FindWindow, the Windows DLLs do not actually contain a function of that namethey contain two functions called FindWindowA and FindWindowW. We use the Alias statement to provide the actual name (case sensitive) for the function contained in the DLL. In fact, as long as we provide the correct name in the Alias clause, we can give it any name we like:

Declare Function Foo Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
ApphWnd = Foo("XLMAIN", Application.Caption)

Although VBA stores strings internally as Unicode, it always converts them to ANSI when passing them to API functions. This is usually sufficient, and it is quite rare to find examples of VB or VBA calling the Unicode versions. In some cases, however, we need to support the full Unicode character set and can work around VBA's conversion behavior by calling the W version of the API function and using StrConv to do an extra ANSI-to-Unicode conversion within our API function calls:

Declare Function FindWindow Lib "user32" Alias "FindWindowW" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
ApphWnd = FindWindow(StrConv("XLMAIN", vbUnicode), _
StrConv(Application.Caption, vbUnicode))

Finding Related Windows

The problem with the (very common) usage of FindWindow to get the main Excel window handle is that if we have multiple instances of Excel open that have the same caption, there is no easy way to tell which one we get, so we might end up modifying the wrong instance! It is a common problem if the user typically doesn't have his workbook windows maximized, because all instances of Excel will then have the same caption of "Microsoft Excel."

A more robust and foolproof method is to use the FindWindowEx function to scan through all children of the desktop window, stopping when we find one that belongs to the same process as our current instance of Excel. FindWindowEx works in exactly the same way as FindWindow, but we provide the parent window handle and the handle of a child window to start searching after (or zero to start with the first). Listing 9-4 shows a specific ApphWnd function, which calls a generic FindOurWindow function, which uses the following API functions:

GetCurrentProcessID to retrieve the ID of the instance of Excel running the code

GetDesktopWindow to get the handle of the desktop window, that we pass to FindWindowEx to look through its children (because all application windows are children of the desktop)

FindWindowEx to find the next window that matches the given class and caption

GetWindowThreadProcessID to retrieve the ID of the instance of Excel that owns the window that FindWindowEx found

Listing 9-4. Foolproof Way to Find the Excel Main Window Handle

'Get the handle of the desktop window
Declare Function GetDesktopWindow Lib "user32" () As Long
'Find a child window with a given class name and caption
Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) _
As Long
'Get the process ID of this instance of Excel
Declare Function GetCurrentProcessId Lib "kernel32" () _
As Long
'Get the ID of the process that a window belongs to
Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal hWnd As Long, ByRef lpdwProcessId As Long) _
As Long
'Foolproof way to find the main Excel window handle
Function ApphWnd() As Long
'Excel 2002 and above have a property for the hWnd
If Val(Application.Version) >= 10 Then
ApphWnd = Application.hWnd
Else
ApphWnd = FindOurWindow("XLMAIN", Application.Caption)
End If
End Function
'Finds a top-level window of the given class and caption
'that belongs to this instance of Excel, by matching the
'process IDs
Function FindOurWindow( _
Optional sClass As String = vbNullString, _
Optional sCaption As String = vbNullString)
Dim hWndDesktop As Long
Dim hWnd As Long
Dim hProcThis As Long
Dim hProcWindow As Long
'Get the ID of this instance of Excel, to match to
hProcThis = GetCurrentProcessId
'All top-level windows are children of the desktop,
'so get that handle first
hWndDesktop = GetDesktopWindow
Do
'Find the next child window of the desktop that
'matches the given window class and/or caption.
'The first time in, hWnd will be zero, so we'll get
'the first matching window. Each call will pass the
'handle of the window we found the last time,
'thereby getting the next one (if any)
hWnd = FindWindowEx(hWndDesktop, hWnd, sClass, _
sCaption)
'Get the ID of the process that owns the window
GetWindowThreadProcessId hWnd, hProcWindow
'Loop until the window's process matches this process,
'or we didn't find a window
Loop Until hProcWindow = hProcThis Or hWnd = 0
'Return the handle we found
FindOurWindow = hWnd
End Function

The FindOurWindow function can also be used to safely find any of the top-level windows that Excel creates, such as userforms.

After we've found Excel's main window handle, we can use the FindWindowEx function to navigate through Excel's window hierarchy. Listing 9-5 shows a function to return the handle of a given Excel workbook's window. To get the window handle, we start at Excel's main window, find the desktop (class XLDESK) and then find the window (class EXCEL7) with the appropriate caption.

Listing 9-5. Function to Find a Workbook's Window Handle

Private Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) _
As Long
'Function to find the handle of a given workbook window
Function WorkbookWindowhWnd(wndWindow As Window) As Long
Dim hWndExcel As Long
Dim hWndDesk As Long
'Get the main Excel window
hWndExcel = ApphWnd
'Find the desktop
hWndDesk = FindWindowEx(hWndExcel, 0, _
"XLDESK", vbNullString)
'Find the workbook window
WorkbookWindowhWnd = FindWindowEx(hWndDesk, 0, _
"EXCEL7", wndWindow.Caption)
End Function

Windows Messages

At the lowest level, windows communicate with each other and with the operating system by sending simple messages. Every window has a main message-handling procedure (commonly called its wndproc) to which messages are sent. Every message consists of four elements: the handle of the window to which the message is being sent, a message ID and two numbers that provide extra information about the message (if required). Within each wndproc, there is a huge case statement that works out what to do for each message ID. For example, the system will send the WM_PAINT message to a window when it requires the window to redraw its contents. Figure 9-1, we can see that the EXCEL; window contains a combo box. This combo box is actually the Name drop-down to the left of the formula bar. Searching the MSDN library (using Google) with the search term "combo box messages" gives us a number of relevant hits. One of them takes us to [ msdn.microsoft.com/library/en-us/shellcc/platform/commctls/comboboxes/comboboxes.asp ]. Looking down the list of messages we find the CB_SETDROPPEDWIDTH message that we can use to change the width of the drop-down portion of the Name box. In Listing 9-6, we use the SendMessage function to make the Name drop-down 200 pixels wide, enabling us to see the full text of lengthy defined names.

Listing 9-6. Changing the Width of the Name Drop-Down List

Private Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) _
As Long
Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, Byval lParam As Long) _
As Long
'Not included in win32api.txt, but found in winuser.h
Private Const CB_SETDROPPEDWIDTH As Long = &H160&
'Make the Name dropdown list 200 pixels wide
Sub SetNameDropdownWidth()
Dim hWndExcel As Long
Dim hWndFormulaBar As Long
Dim hWndNameCombo As Long
'Get the main Excel window
hWndExcel = ApphWnd
'Get the handle for the formula bar window
hWndFormulaBar = FindWindowEx(hWndExcel, 0, _
"EXCEL;", vbNullString)
'Get the handle for the Name combobox
hWndNameCombo = FindWindowEx(hWndFormulaBar, 0, _
"combobox", vbNullString)
'Set the dropdown list to be 200 pixels wide
SendMessage hWndNameCombo, CB_SETDROPPEDWIDTH, 200, 0
End Sub

Changing the Window Icon

When creating a dictator application, the intent is usually to make it look as though it is a normal Windows application and not necessarily running within Excel. Two of the giveaways are the application and worksheet icons. These can be changed to our own icons using API functions. We first use the ExtractIcon function to get a handle to an icon from a file, then send that icon handle to the window in a WM_SETICON message, as shown in Listing 9-7. The SetIcon routine is given a window handle and the path to an icon file, so it can be used to set either the application's icon or a workbook window's icon. For best use, the icon file should contain both 32x32 and 16x16 pixel versions of the icon image. Note that when setting the workbook window's icon, Excel doesn't refresh the image to the left of the menu bar until a window is maximized or minimized/restored, so you may need to toggle the WindowState to force the update.

Listing 9-7. Setting a Window's Icon

Private Declare Function ExtractIcon Lib "shell32.dll" _
Alias "ExtractIconA" _
(ByVal hInst As Long, _
ByVal lpszExeFileName As String, _
ByVal nIconIndex As Long) As Long
Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, Byval lParam As Long) _
As Long
Private Const WM_SETICON As Long = &H80
'Set a window's icon
Sub SetIcon(ByVal hWnd As Long, ByVal sIcon As String)
Dim hIcon As Long
'Get the icon handle
hIcon = ExtractIcon(0, sIcon, 0)
'Set the big (32x32) and small (16x16) icons
SendMessage hWnd, WM_SETICON, 1, hIcon
SendMessage hWnd, WM_SETICON, 0, hIcon
End Sub

Changing Windows Styles

If you look at all the windows on your screen, you might notice that they all look a little different. Some have a title bar, some have minimize and maximize buttons, some have an [x] to close them, some have a 3D look, some are resizable, some are a fixed size and so on. All of these things are individual attributes of the window and are stored as part of the window's data structure. They're all on/off flags stored as bits in two Long numbers. We can use the GetWindowLong function to retrieve a window's style settings, switch individual bits on or off and write them back using SetWindowLong. Modifying windows styles in this way is most often done for userforms and is covered in Chapter 10 Userform Design and Best Practices.