Microsoft Office Tutorials and References
In Depth Information
Using the Windows API
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:
h Which API function you’re using
h In which library the API function is located
h The API function’s arguments
After you declare an API function, you can use it in your VBA code.
Determining the Windows directory
This section contains an example of an API function that displays the name of the Windows
directory — something that’s not possible using standard VBA statements. This code won’t work
with Excel 2007.
Here’s the API function declaration:
Declare PtrSafe 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. 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 _
MsgBox WinDir, vbInformation, “Windows Directory”
Executing the ShowWindowsDir procedure displays a message box with the Windows directory.