When developing Excel-based applications, you can get most things done by using the Excel object model. After reading this sample book chapter, you will be comfortable about including API calls in your applications, understand how they work, and be able to modify them to suit your needs.

This chapter is from the book

In the Programming with the Windows API chapter of our Excel 2002
VBA Programmers Reference, we approached the subject of using Windows
API calls by explaining how to locate the definitions for various functions
on the MSDN Web site and translate those functions for use in VBA. The idea
was to enable readers to browse through the API documentation and use anything
of interest they found.

In reality, extremely few people use Windows API calls in that manner; indeed,
trying to include previously unexplored API calls in our Excel applications
is very likely to result in a maintenance problem, because it's doubtful
that another developer will understand what we were trying to do. Instead,
most of us go to Google and search the Web or the newsgroups for the answer
to a problem and find that the solution requires the use of API calls. (Searching
Google for "Excel Windows API" results in more than 200,000 Web pages
and 19,000 newsgroup posts.) We copy the solution into our application and
hope it works, usually without really understanding what it does. This chapter
shines a light on many of those solutions, explaining how they work, what they
use the API calls for, and how they can be modified to better fit our applications.
Along the way, we fill in some of the conceptual framework of common Windows
API techniques and terminology.

By the end of the chapter, you will be comfortable about including API calls
in your applications, understand how they work, accept their use in the example
applications we develop in this book and be able to modify them to suit your
needs.

Overview

When developing Excel-based applications, we can get most things done by
using the Excel object model. Occasionally, though, we need some information or
feature that Excel doesn't provide. In those cases, we can usually go
directly to the files that comprise the Windows operating system to find what
we're looking for. The first step in doing that is to tell VBA the function
exists, where to find it, what arguments it takes and what data type it returns.
This is done using the Declare statement, such as that for GetSystemMetrics:

This statement tells the VBA interpreter that there is a function called
GetSystemMetrics located in the file user32.exe (or user32.dll, it'll check
both) that takes one argument of a Long value and returns a Long value. Once
defined, we can call GetSystemMetrics in exactly the same way as if it is the
VBA function:

Function GetSystemMetrics(ByVal nIndex As Long) As Long
End Function

The Declare statements can be used in any type of code module, can be Public
or Private (just like standard procedures), but must always be placed in the
Declarations section at the top of the module.

Finding Documentation

All of the functions in the Windows API are fully documented in the
Windows Development/Platform SDK section of the MSDN library on the
Microsoft Web site, at
http://msdn.microsoft.com/library,
although the terminology used and the code samples tend to be targeted at the
C++ developer. A Google search will usually locate documentation more
appropriate for the Visual Basic and VBA developer, but is unlikely to be as
complete as MSDN. If you're using API calls found on a Web site, the Web
page will hopefully explain what they do, but it is a good idea to always check
the official documentation for the functions to see whether any limitations or
other remarks may affect your usage.

Unfortunately, the MSDN library's search engine is significantly worse
than using Google to search the MSDN site. We find that Google always gives us
more relevant pages than MSDN's search engine. To use Google to search
MSDN, browse to
http://www.google.com and click
the Advanced Search link. Type in the search criteria and then in the Domain
edit box type msdn.microsoft.com to restrict the search to MSDN.

Finding Declarations

It is not uncommon to encounter code snippets on the Internet that include
incorrect declarations for API functionssuch as declaring an
argument's data type as Integer or Boolean when it should be Long. Although
using the declaration included in the snippet will probably work (hopefully the
author tested it), it might not work for the full range of possible arguments
that the function accepts and in rare cases may cause memory corruption and data
loss. The official VBA-friendly declarations for many of the more commonly used
API functions can be found in the win32api.txt file, which is included with a
viewer in the Developer Editions of Office 972002, Visual Basic 6 and is
available for download from
http://support.microsoft.com/?kbid=178020.
You'll notice from the download page that the file hasn't been updated
for some time. It therefore doesn't include the declarations and constants
added in recent versions of Windows. If you're using one of those newer
declarations, you'll have to trust the Web page author, examine a number of
Web pages to check that they all use the same declaration or create your own
VBA-friendly declaration by following the steps we described in the Excel
2002 VBA Programmers Reference.

Finding the Values of Constants

Most API functions are passed constants to modify their behavior or specify
the type of value to return. For example, the GetSystemMetrics function shown
previously accepts a parameter to specify which metric we want, such as
SM_CXSCREEN to get the width of the screen in pixels or SM_CYSCREEN to get the
height. All of the appropriate constants are shown on the MSDN page for that
declaration. For example, the GetSystemMetrics function is documented at
http://msdn.microsoft.com/library/en-us/sysinfo/base/getsystemmetrics.asp
and shows more than 70 valid constants.

Although many of the constants are included in the win32api.txt file
mentioned earlier, it does not include constants added for recent versions of
Windows. The best way to find these values is by downloading and installing the
core Platform SDK from
http://www.microsoft.com/msdownload/platformsdk/sdkupdate/.
This includes all the C++ header files that were used to build the DLLs, in a
subdirectory called \include. The files in this directory can be searched
using normal Windows file searching to find the file that contains the constant
we're interested in. For example, searching for SM_CXSCREEN gives the file
winuser.h. Opening that file and searching within it gives the following
lines:

#define SM_CXSCREEN 0
#define SM_CYSCREEN 1

These constants can then be included in your VBA module by declaring them as
Long variables with the values shown:

Const SM_CXSCREEN As Long = 0
Const SM_CYSCREEN As Long = 1

Sometimes, the values will be shown in hexadecimal form, such as 0x8000,
which can be converted to VBA by replacing the 0x with &h and
adding a further & on the end, such that

#define KF_UP 0x8000

becomes

Const KF_UP As Long = &h8000&

Understanding Handles

Within VBA, we're used to setting a variable to reference an object
using code like

Set wkbBackDrop = Workbooks("Backdrop.xls")

and releasing that reference by setting the variable to Nothing (or letting
VBA do that for us when it goes out of scope at the end of the procedure). Under
the covers, the thing that we see as the Backdrop.xls workbook is just an area
of memory containing data structured in a specific way that only Excel
understands. When we set the variable equal to that object, it is just given the
memory location of that data structure. The Windows operating system works in a
very similar way, but at a much more granular level; almost everything within
Windows is maintained as a small data structure somewhere. If we want to work
with the item that is represented by that structure (such as a window), we need
to get a reference to it and pass that reference to the appropriate API
function. These references are known as handles and are just ID
numbers that Windows uses to identify the data structure. Variables used to
store handles are usually given the prefix h and are declared As Long.

When we ask for the handle to an item, some functionssuch as
FindWindowgive us the handle to a shared data structure; there is only one
data structure for each window, so every call to FindWindow with the same
parameters will return the same handle. In these cases, we can just discard the
handle when we're finished with it. In most situations, however, Windows
allocates an area of memory, creates a new data structure for us to use and
returns the handle to that structure. In these cases, we must tidy up
after ourselves, by explicitly telling Windows that we've finished using
the handle (and by implication, the memory used to store the data structure that
the handle points to). If we fail to tidy up correctly, each call to our routine
will use another bit of memory until Windows crashesthis is known as a
memory leak. The most common cause of memory leaks is forgetting to
include tidy-up code within a routine's error handler. The MSDN
documentation will tell you whether you need to release the handle and which
function to call to do it.

Encapsulating API Calls

GetSystemMetrics is one of the few API calls that can easily be used in
isolationit has a meaningful name, takes a single parameter, returns a
simple result and doesn't require any preparation or cleanup. So long as
you can remember what SM_CXSCREEN is asking for, it's extremely easy to
call this function; GetSystemMetrics(SM_CXSCREEN) gives us the width of
the screen in pixels.

In general practice, however, it is a very good idea to wrap your API calls
inside their own VBA functions and to place those functions in modules dedicated
to specific areas of the Windows API, for the following reasons:

The VBA routine can include some validity checks before trying to call
the API function. Passing invalid data to API functions will often result in a
crash.

Most of the textual API functions require string variables to be defined
and passed in, which are then populated by the API function. Using a VBA routine
hides that complexity.

Many API functions accept parameters that we don't need to use. A
VBA routine can expose only the parameters that are applicable to our
application.

Few API functions can be used in isolation; most require extra
preparatory and clean up calls. Using a VBA routine hides that
complexity.

The API declarations themselves can be declared Private to the module in
which they're contained, so they can be hidden from use by other developers
who may not understand how to use them; their functionality can then be exposed
through more friendly VBA routines.

Some API functions, such as the encryption or Internet functions, require
an initial set of preparatory calls to open resources, a number of routines that
use those resources and a final set of routines to close the resources and tidy
up. Such routines are ideally encapsulated in a class module, with the
Class_Initialize and Class_Terminate procedures used to ensure the resources are
opened and closed correctly.

By using dedicated modules for specific areas of the Windows API, we can
easily copy the routines between applications, in the knowledge that they are
self-contained.

When you start to include lots of API calls in your application, it quickly
becomes difficult to keep track of which constants belong to which functions. We
can make the constants much easier to manage if we encapsulate them in an
enumeration and use that enumeration for our VBA function's parameter, as
shown in Listing 9-1. By doing this, the applicable constants are shown in the
Intellisense list when the VBA function is used, as shown in Figure
9-1. The
ability to define enumerations was added in Excel 2000.

'Declare all the API-specific items Private to the module
Private Declare Function GetSystemMetrics Lib "user32" _
(ByVal nIndex As Long) As Long
Private Const SM_CXSCREEN As Long = 0
Private Const SM_CYSCREEN As Long = 1
'Wrap the API constants in a public enumeration,
'so they appear in the Intellisense dropdown
Public Enum SystemMetricsConstants
smScreenWidth = SM_CXSCREEN
smScreenHeight = SM_CYSCREEN
End Enum
'Wrapper for the GetSystemMetrics API function,
'using the SystemMetricsConstants enumeration
Public Function SystemMetrics( _
ByVal uIndex As SystemMetricsConstants) As Long
SystemMetrics = GetSystemMetrics(uIndex)
End Function

Figure
9-1 By Using the Enumeration, the Relevant Constants Appear in the
Intellisense Drop-Down