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:
Dim WinPath As String * 255 Dim WinDir As String WinPath = Space(255)
WinDir = Left(WinPath, GetWindowsDirectoryA _
(WinPath, Len(WinPath))) MsgBox WinDir, vblnformation, "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'll 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:
The reason for using API calls is to perform an action 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 are not uncommon, so save your work often.
Was this article helpful?