Home > Articles > Home & Office Computing > Microsoft Applications

  • Print
  • + Share This
This chapter is from the book

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.2Figure 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.

Table 9-1 Excel Window Classes and Their Uses

Window Class

Usage

XLMAIN

The main Excel application window.

EXCEL;

The left half of the formula bar, including the Name drop-down.

ComboBox

A standard Windows combo box (in this case, it's the Name drop-down).

EXCEL<

The edit box section of the formula bar.

EXCEL2

The four command bar docking areas (top, left, right and bottom).

MsoCommandBar

A command bar.

XLDESK

The Excel desktop.

EXCEL7

A workbook window. In this example, Book1 has two windows open.

EXCELE

A window used to provide in-sheet editing of embedded charts.

EXCEL4

The status bar.


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 before—the 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 name—they 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.

It will probably come as no surprise that we can also send messages directly to individual windows, using the SendMessage function. The easiest way to find which messages can be sent to which window class is to search the MSDN library using a known constant and then look in the See Also list for a link to a list of related messages. Look down the list for a message that looks interesting, then go to its details page to see the parameters it requires. For example, if we look again 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 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.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020