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.
Table 9-1 Excel Window Classes and Their Uses
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:
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
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
'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
.
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
[email protected]
.
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
[email protected]
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
[email protected]
.
Supplemental Privacy Statement for California Residents
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.
)
Figure
9-2
){kind=link}