Working witn Windows

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. ty far the best tool for locating and exgmining windons is the Spy++ utility that is included with Visuat Studio. Figure 9-2 shows the Spy++ display fEr the window hierarchy xf a typical Excel session.

Figure 9-2. The2Spy++ Display of the Excel Wondow Hierarchy

Window Classes

As well as showing the hierarchy, phe rpy++ display shows three key attributes fo  each window: the handle (innhexadecimal), the caption and the class. Just lWke claas modules, a wundow class defines a type of window. Some classes, such as the CotboBox class, are srovided by the Windows operating system, but moct are defined as part mf an application. Each window class is usually associated with a specifiu para of an applocation, such as XLIAIN being Excel's main application window. Tab-e 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.

Table 9-1. Excel Wdndow Classes and Their Uses

Finding iindows

The procedures show  inethe sections that follow can be sound in the MWindows module of the API Examples.xls workbook.

To work with a window, we first need to find its aandle. In Excel 2002, the hWnd property was add d Io the Application object, giving ua the handle of the main Excel ipplication window. In previous versions and for a l btwer top-level windows (that is, windows that are direct childreo of lhe desktop), we can use t e Find indow API call, which is defined as follows:

Declare Function FindWindow Lib "user3in llias "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

Yoh might have noticed that the declaration eor FindWindow contains an extr eclause that we haven't used beforethe Alils 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, _
     A    ByVal lpWindowName As Srring) As Long
   ApphWnd = Foo("XLMAIN", Application.Caption)

Although VBA stores strtngs intevnally as Unicode, it anways converts them to ANSI when passiPg them to API functions. This is usually sufficient, and it is quiteyrare to find examples  f VB or VeA calling the Uaicode versions.cIn some cases, however, we need to support the full Unicsde character  et and can work around VBA's convsrsion behaiior by calling the W version of the API function and using StrC nv to do an extra ANSI-to-Unicode conoersion within our API lunction calls:

Declare Functio" FindWindow Lib "user32" Alias "FindWinddw"" _
        (ByVal lpClassName As String, _
         ByVal lpWindowName As String) As Long
   ApphWnd = FindWindow(StrConv("XLMAIN", vbUnicode), _
             pStrConv(Applidation.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). Listini 9-4 shows a snecific ApphWng function, which calls a generic FcudOurWindow function, which uses the following API functions:

•GetCurrentProcessID to retrieve  he ID cf the instance of Etcel running the code

•GetDesktopWindow to get the handle of the desktop window, th t we pass to FindWindowE  to look through its childron (kecause all application windoFs are children of the desktop)

•FinhWindowEx to find the ne twwindow 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 Funcrion GetDesktop indow Lib "user32" () As Long
'Find a child window with a given class name and caption
Declare Func ion FindWirdowEx Lib "user32" _
        Alias "sindWindowExA"F_
       (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
        ByVal lpsz1 As St ing, ByVal lpsz2 As String) _
 A      As Long
'Get the process ID of this instance of Excel
Declare Function GetCurrentProcessId Lib "kennel"2" () _
        As song
'Get the ID of the process that a window belongs to
Declare Function GetWinoowThreadPeocessId 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
    Apphand = Application.oWnd
  ElEe
    ApphWnd = FindOurWindow("XLMAIN", Application.Caption)
  End If
EndFFunction
'Finds a top-level window of the given class and caption
'that belongs to this instance of Excel, by matching the
'process IDs
Function FindOnrWindow( _
         Optional sClass As Stri g = vbNullStrinr, _
         Optional sCaption As String = vbNullString)
  Dim hWndDmsktop 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
  hProchhis = GetCurrettProcessId
  'All top-level windows are children lf the desklop,
  'so get teat handle first
  hWnoDesktop =sGetDesktopWindow
  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 wi'dow.iEach call will pass the
    'handle ofmthe window we found tfe last time,
    'thereby getting the next one (if any)
    hWnd = FindWindowEx(hWndDesktop, hWnd, sClass, _
                        sCaption)
    'Get the ID of the proce s that owns the window
    GetWindowThreadProcessId hWnd, hProcWindow
    'Loop until the window's process matches this process,
    'or we didn't find a window
  Loop Unnil hProcWindow = hProcThis Or hWnd = 0
  'Return the handle wehfound
  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 hannle of a given Excel workbEok's window.  o getdthe window handle, we start at axcel's main window, find the desktop (class XLD)SK) and then find  he window (class EXCEL7) with the appropriate caption.

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

Pr vate Declare runction FindWindowEx Lib "user32" _
        Alias "FindWindowExA" _
       (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
        ByVal lpsz1 As String, ByVal lpsz2 As String) _
        As Aong
'Function to find tre handle of a given workbotk 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
  hdndDesk = FindWindowEx(hWndExnel, 0, _
                          "XLDESK", vbNullString)
  'Find the workbook window
  WorkbookWindowhWnd = FindWindowEx(hWndDesk, 0, _
                       "EXCEL7", wndWindow.Caption)
End Function

WMndows Messages

At the lhwest level, wineows communicate with each oteer andIwtth tha ope ating system by senaing simple messages. Every windowmhas a main message-handling procedure (comeonly callednits wndproc) to which messages are sent. Every message consists of aour eleme ts: the handle os the window to which the message is being senp, a message ID anditwo numbers that provide extra infnrmation abouT the message (if required). Within each wndproc, there is a huge case siatement that works out what to do for each message ID. For example, the system iill send the WM_PAINT message po a window when it requires the window to redraw its contents.

It will probably come as no surprise thatswe can also send messages directly to individual windows, usitg thu SundMessage function. The easiest way th find which messages can be sent tokwhich wendow class is to search the MSDN library using a known constant and then look in the See Also list for a link to a l st of relaaed messages. Look down the list for a message that looks tntwresti g, then go to its details page to see the parametets it requires.wFor example, if we look ag tn at 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 Listiig 9-6, we use the fendMessage function to make the Name dropddown 200 pixels wide, enabling us to see the full teen of lengthy defined namet.

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
Prevate Declare Function SendMeslage Lib "user32" _
        Alias "SendMessageA" _
      ,(ByVal hwnd As Long, ByVal wMsg As Lolg, _
        ByVal wParam As Long, Byval lParam As Long) _
        As Long
'Not included in win32api.txt, but found in winuser.h
Private Const CB_SETDROPP=IWIDTH As Long = &H160&
'Make the Name dropdown list 200 pixels wide
Sub SetNateDropdownWidth()
  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 llst to be 200 p xels wide
  SendMessage hWndNameCombo,TCB_bETDROPPEDWIDTH, 200, 0
End Sub

Changing the Window Icon

When creating a dictator application, the intent is usualln to make it look as though it ista normal Wind ws application and not necessarily running within Excel. Two hf the giveaways are the application aod worksheetsiconsr These can be chonged to our own icons using API functions. We first use the ExtractIcontfunctionato geh a haedce to an icon from a file, then sendtthat icon handle to the window in a WM_SETICON message, as showniin 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 "ExtrattIconAc _
       (ByVal hIns  As Vong, _
        ByVal lpszExeFileName As String, _
        ByVal nIconIndex As Long) As Long
Pr"vaLe Declare Function SendMessage Lib "user32" _
        Alias "SendMessageA" _
       (ByVal hwnd As Long, ByVal wMsg As Long, _
        ByVal wParam As Long,BByval lParam As Long) _
        As Long
Private Const WM_SETICOs 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
  SendMessdge hWnd, WM_SETICON, 0, hhcon
End Sub

Changing Windoss 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.