Understanding and Using Windows API Calls for Excel Programming
This chapter is from the book
This chapter is from the book
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:
Declare Function GetSystemMetrics Lib "user32" _
(ByVal nIndex As Long) As Long
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.
Listing 9-1 Encapsulating the GetSystemMetrics API Function and Related Constants
'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
){kind=small}
Figure
9-1 By Using the Enumeration, the Relevant Constants Appear in the
Intellisense Drop-Down