Microsoft Office Tutorials and References
In Depth Information
Using the Windows API
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 _
After declaring this function, you can call it from another procedure:
You can even use the function in a worksheet formula:
This example is available on the companion CD-ROM. The filename is windows
directory.xlsm , and the API function declaration is compatible with Excel 2007 and later.
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.
When you work with API calls, system crashes during testing aren’t uncommon, so save
your work often.
Detecting the Shift key
Here’s another example: Suppose that you’ve written a VBA macro that will be executed by
clicking a button on a worksheet. Furthermore, suppose that you want the macro to perform
differently if the user presses the Shift key when the button is clicked. VBA doesn’t provide a 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’re interested in.
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