Using the Windows API


Using the Windows API

VBA can borrow methods from other files that have nothing to do with Excel or VBA - for example, the Dynamic Link Library (DLL) files that Windows and other software use. As a result, you can do things with VBA that would otherwise be outside the language's scope.

The Windows Application Programming Interface (API) is a set of functions available to Windows programmers. When you call a Windows function from VBA, you're accessing the Windows API. Many of the Windows resources used by Windows programmers are available in DLLs, which store programs and functions and are linked at runtime rather than at compile time.

Excel itself uses several DLLs, for example. The code in many of these DLLs could have been compiled right into the excel.exe executable file, but the designers chose to store it in DLLs, which are loaded only when needed. This technique makes Excel's main executable file smaller. In addition, it is a more efficient use of memory because the library is loaded only when it's needed.

DLLs are also used to share code. For example, most Windows programs use dialog boxes to open and save files. Windows comes with a DLL that has the code to generate several standard dialog boxes. Programmers thus can call this DLL rather than write their own routines.

If you're a C programmer, you can produce your own DLLs and use them from VBA. In addition, Microsoft's Visual Basic language (but not VBA) also has the capability to create DLL files that can be called from Excel.

Windows API examples

Before you can use a Windows API function, you must declare the function at the top of your code module. If the code module is for a UserForm , Sheet , or ThisWorkbook , you must declare the API function as Private .

An API function must be declared precisely. The declaration statement tells VBA:

  • Which API function you're using

  • In which library the API function is located

  • The API function's arguments

After you declare an API function, you can use it in your VBA code.

Determining the Windows directory

Following is an example of an API function declaration:

 Declare Function GetWindowsDirectoryA Lib "kernel32" _   (ByVal lpBuffer As String, ByVal nSize As Long) As Long 

This function, which has two arguments, returns the name of the directory in which Windows is installed (something that is not normally possible using VBA). After calling the function, the Windows directory is contained in lpBuffer , and the length of the directory string is contained in nSize .

After inserting the Declare statement at the top of your module, you can access the function by calling the GetWindowsDirectoryA function. The following is an example of calling the function and displaying the result in a message box:

 Sub ShowWindowsDir()     Dim WinPath As String * 255     Dim WinDir As String     WinPath = Space(255)     WinDir = Left(WinPath, GetWindowsDirectoryA _        (WinPath, Len(WinPath)))     MsgBox WinDir, vbInformation, "Windows Directory" End Sub 

Executing the ShowWindowsDir procedure displays a message box with the Windows directory.

Often, you'll want to create a wrapper for API functions. In other words, you create your own function that uses the API function. This greatly simplifies using the API function. Here's an example of a wrapper VBA function:

 Function WindowsDir() As String '   Returns the Windows directory     Dim WinPath As String * 255     WinPath = Space(255)     WindowsDir = Left(WinPath, GetWindowsDirectoryA _         (WinPath, Len(WinPath))) End Function 

After declaring this function, you can call it from another procedure:

 MsgBox WindowsDir() 

You can even use the function in a worksheet formula:

 =WindowsDir() 
CD-ROM  

This example is available on the companion CD-ROM. The filename is image from book  windows directory.xlsm .

The reason for using API calls is to perform actions that would otherwise be impossible (or at least very difficult). If your application needs to find the path of the Windows directory, you could search all day and not find a function in Excel or VBA to do the trick. But knowing how to access the Windows API may solve your problem.

Caution  

When you work with API calls, system crashes during testing are not uncommon, so save your work often.

Detecting the Shift key

Here's another example: Suppose you've written a VBA macro that will be executed from button on a worksheet. Furthermore, suppose you want the macro to perform differently if the user presses the Shift key when the button is clicked. Normally, there is no way to detect whether the Shift key is pressed. But you can use the GetKeyState API function to find out. The GetKeyState function tells you whether a particular key is pressed. It takes a single argument, nVirtKey , which represents the code for the key that you are interested in.

CROSS-REFERENCE  

Chapter 11 has several additional examples of using Windows API functions.

The following code demonstrates how to detect whether the Shift key is pressed when the Button_Click event handler procedure is executed. Notice that I define a constant for the Shift key (using a hexadecimal value) and then use this constant as the argument for GetKeyState . If GetKeyState returns a value less than zero, it means that the Shift key was pressed; otherwise, the Shift key was not pressed.

 Declare Function GetKeyState Lib "user32" _   (ByVal nVirtKey As Long) As Integer Sub Button_Click()     Const VK_SHIFT As Integer = &H10     If GetKeyState(VK_SHIFT) < 0 Then         MsgBox "Shift is pressed"     Else         MsgBox "Shift is not pressed"     End If End Sub 
CD-ROM  

A workbook named image from book  key press.xlsm on the companion CD-ROM demonstrates how to detect the following keys (as well as any combinations): Ctrl, Shift, and Alt.

Learning more about API functions

Working with the Windows API functions can be tricky. Many programming reference books list the declarations for common API calls and often provide examples. Usually, you can simply copy the declarations and use the functions without really understanding the details. In reality (at least the reality that I've seen), most Excel programmers take a cook-book approach to API functions. The Internet has hundreds of examples that can be copied and pasted and that work quite reliably.

CD-ROM  

The companion CD-ROM includes a file named image from book  win32api.txt , which is a text file that contains Windows API declarations and constants. You can open this file with a text editor and copy the appropriate declarations to a VBA module.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net