Why Use VBA to Call the Windows API?

Microsoft® Visual Basic® for Applications (VBA) is a powerful tool for building Microsoft® Windows® applications. With VBA, however, you have control over only a small portion of the operating system — the part that is available through the functions and objects exposed directly to VBA in your application. The Windows API includes functions to control the smallest aspects of the operating system. You can extend and fine-tune your VBA applications by calling functions in the Windows API from VBA.

For example, VBA provides functions that read from and write to a portion of the registry reserved for VBA applications. You can use these functions — GetSetting, GetAllSettings, SaveSetting, and DeleteSetting — to maintain information about your application between sessions. However, these functions permit you to work with only one subkey in the registry, namely the \HKEY_CURRENT_USER\Software\VB and VBA Program Settings subkey. If you want to store or to retrieve information from any other part of the registry, you must use the Windows API.

Because the Windows API provides such granular control over the operating system, you might want to call several functions to accomplish a single task. There are several different API functions for manipulating the registry, which you use in conjunction with each other. For example, to assign a new value to a registry subkey, you can use the RegOpenKeyEx function, which opens an existing subkey for reading or writing values. Then, you can use the RegSetValueEx function to set the new value for that subkey, followed by the RegCloseKey function to close the subkey.

The API functions for manipulating the Windows Clipboard also might be useful to you as a VBA programmer. VBA does not provide an object for working with the Clipboard, but you can create your own by wrapping the Clipboard API functions in a class module to create a simple, reusable object representing the Windows Clipboard. You might want to use several functions together to accomplish a single task. For example, the OpenClipboard function opens the Clipboard for examination and prevents other applications from modifying the Clipboard content. The GetClipboardData function returns data that is saved to the Clipboard, and the CloseClipboard function closes the Clipboard, making it again available to other applications.

Note   Each of the Microsoft® Office XP applications provides a Clipboard toolbar that you can use to store up to 12 items. In addition, you can work with the items stored by the Clipboard toolbar programmatically through the command bar object model. The last item copied to the Clipboard toolbar is the one stored in the Windows Clipboard.

See Also

API Basics | What Is an API? | API Resources | Accessing Functions in a DLL | Anatomy of a Declare Statement | Constants and User-Defined Types | Understanding Handles