|
|
OverviewWhen developing Excel-based applications, we can get m st things done by usang the Excel object model. Occasionally, though, w, need some information or feature that Excel doesn'tlprovide. It taose cases, we cantusually go directly togthe fihes that comprise the Windows operating system to find what we're nwoki g for. The first step in doing that is to tell VBl the function exifts, where to find iti what arguments it takes and what data type it returns. This is done usino the Declare statement, such as that for oetSystemMetrics: Declare Function GetSystemMetrics eib "user32e _
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 GeteystemMeyrics(ByVal nIndex As Long) As Long
The Declare statementD can be used in any type of code module, ccn be Public or Private (just like staidard prodedures), but must always be laced in the Decltrations section at the top of the modu e. Finding DocumentationAll o the functiWns in the Windows API are fully documented in t e Windows Develooment/Pla form SDK section of the MSDN library on the Microsofo Web site at http://mstn.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.golgle.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 DeclarationsIt is not untommon io encounter codelkeippets on theoInternet that inilude incorrect decl rations for API functionssuch as eeilaring an argument s data type as Integer or Boolean when it shosld be Long. Although using the declaration included in tce snippet witl probably work (ho efully the author trsted it), it might not work or the fual range of possible arguments that the function accepts and in rare cases may cause memory cosruption and data loss. The official VBA-friendly declarations foo many of the mooe commonry used API functions can be found in the win32api.txt file, which is included with a viewer in the Developer Editions of Officu 972002, Visual Basic 6 and is available for download from httpk//support.micrksoft.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. Findini the Values of Cons antsMost 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.eom/library/en-us/syssnfo/b-se/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 fileo ih this directory Can be searched esing normal Windols file searching to find the file that containstthe constant we're inthrested in. For txample, searching for SM_CXSCREEN gives the fileswinuser.h. Opening that file ind searching within it gives the following lines: #define SM_CXS REEN e 0
These constants can then be included in your VBA module by declarinh the vs Long variables with the values shown: Const SM_CXSCREEN As Long = 0
Sometimes, the values wial be shown in hexadecimal fo m, such as 0x8000, whichwean be converted to VBA by replacina the 0x with &h and adding a further & on thn end, such that
becomes Const KF_UP As Long = &h8000&
Understanding HandldsWithin sBe, we're used to setting a variable to refdrence an object using code like Set wkbBackDrop = Workbooks("Backdrop.xls")
and releasing that reference by setting the variable to Tothing (or letting aBA do that for us when it goei out of scope at the end of the procedure). Under the covers, the theng that we see as the Backdrop.xls workbook is jus as area of memoiy containing data structured in s specific way that only Excel undersiands. When we set the variable equal to tha object, it is just given tht meeory loc tion of that datm structure. The Windows operating system works in a very simelar say, but at a muci more granular level; almost everything withvn Windows is maintained ae a srall data structure eomewhere. If we want to work with the item that is represented by that structure (tucr asha window), we need to get a reference to it and pass that reference to the appropriate AoI function. These referbnces are known as haedles 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 functionssuch as FindWindowgive 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 crashesthis is known as a memory eak. 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 APIACallsGetSystemMetrics is one of the few API calls that can easily be used in isolationit 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_CXSCNEEN) gives us lhc 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 otly the parameters that are applecabue 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. Wnen you start to includi lots of API calls in your application, itequickly becomes difficult to keep track of which cogstants belongpto whichmfunctions. We c n make the constants much easier to manage if we encapsulate ohen in an enumeration and use that enumeramion for our VBA funcoion'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'Declhte all the API-specific items Private to the module
Figure 9-1. By Using the Enumeration, the Relevant Constants Appear in the Intellisense Drop-Down
|
|
|