When a VBA solution falls short of your needs, there’s probably a Windows API function that can handle the job. Susan Harkins lists 10 handy APIs and explains how you can put them to work.
Office developers rely heavily on VBA. But even though it’s versatile and robust, it doesn’t do everything. Sometimes, a VBA solution is convoluted or difficult to implement. When that happens, turn to the Windows Application Programming Interface (API). You’ll find thousands of useful functions. Although Office developers should find the APIs in this article useful, they’re not just for Office. You can use them in most any Windows-based application. (All of these tips are specific to 32-bit systems.)
The VBA functions provided in this article aren’t real-world ready. They are simple calls to the API, so you can see how the pieces work together. However, a few could easily move into your code library as is. Execute the VBA procedures from the Immediate window, passing the necessary arguments, to see the results. Once you know how the VBA procedures call the API functions and what to expect in return, you can modify the procedures as necessary and use them in your own projects.
Note: This article is also available for download as a PDF, along with a module containing the code examples discussed here.
The Sleep function suspends execution for a specified period. It places the running code into an inactive state for the number of milliseconds passed to the function. Simply declare the function and then call it as shown from a VBA procedure:
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Sleep milliseconds
If you need to know who’s logged into an Access database, use GetUserName. There are other ways to do this without calling an API, but this API is so simple, why would you bother writing your own code? GetUserName retrieves the name of the current system or the current user logged into the network. Declare it and call it, passing the appropriate information, as follows:
Private Declare Function GetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function apicGetUserName() As String 'Call to apiGetUserName returns current user. Dim lngResponse As Long Dim strUserName As String * 32 lngResponse = GetUserName(strUserName, 32) apicGetUserName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1) End Function
This next function, GetComputerName, is similar to GetUserName except it retrieves the system’s name. Declare it and call it, as follows:
Private Declare Function GetComputerName Lib "kernel32" Alias _ "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function apicGetComputerName() As String 'Call to apiGetUserName returns current user. Dim lngResponse As Long Dim strUserName As String * 32 lngResponse = GetComputerName(strUserName, 32) apicGetComputerName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1) End Function
This API function brings the specified window to the top. If the window is a top-level window, the function activates it. If the window is a child window, the function activates the associated top-level parent window. Simply pass the appropriate window handle. If the function fails, it returns 0; if it’s successful, it will return a nonzero value. Use the following declaration:
Private Declare Function BringWindowToTop Lib "user32" _
(ByVal lngHWnd As Long) As Long
When calling the function, pass the window handle value as a Long variable.
BringWindowToTop requires a handle value. You’ll need FindWindow, another API, for that. This one can be a bit frustrating because it requires specialized information, and if you don’t get it just right, the function won’t work. Specifically, you need the window’s class or name, as shown in the simple call below. This function returns the handle for the Word document, December2010.docx.
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Function apicFindWindow(strClassName As String, strWindowName As String) 'Get window handle. Dim lngWnd As Long apicFindWindow = FindWindow(strClassName, strWindowName) End Function
The class names for the three main Office applications are as follows:
- Microsoft Access — OMain
- Microsoft Excel — XLMAIN
- Microsoft Word — OpusApp
The window name (lpWindowName) is usually the window’s caption; see the window’s title bar. If you’re lucky, passing the object in the following form will work:
For instance, you might try the following statement to return the handle of an Excel userform named ufrmEmployees:
Unfortunately, it doesn’t always work out that way. If the function returns 0, it didn’t work — a window handle is never 0. If you omit one of the parameters (both aren’t necessary), pass vbNullString.
If you have trouble getting the lpWindowName property, grab a copy of AutoIt. This tool returns the exact window name for open windows. Thanks to Stuart McLachlan of Lexacorp for recommending this neat tool!
Have you ever needed to know what’s installed before running a specific Office application? FindExecutable can retrieve that information. This function requires the name of an existing data file and a working directory. The function returns the name and path of the application that Windows would launch if you double-clicked the data file. If the function fails, it returns a value of 32 or less. Declare it and use the following VBA function to call FindExecutable:
Private Declare Function FindExecutable Lib "shell32.dll" Alias _ "FindExecutableA" (ByVal lpFile As String, ByVallpDirectory As String, _ ByVal lpResult As String) As Long Function apicFindExecutable(strDataFile As String, strDir As String) As String 'Resturns exectuable for passed data file. Dim lgnApp As Long Dim strApp As String strApp = Space(260) lngApp = FindExecutable(strDataFile, strDir, strApp) If lngApp > 32 Then apicFindExecutable = strApp Else apicFindExecutable = "No matching application." End If End Function
You must pass the entire path via strDataFile, not just the name of the data file. The function will return the path to the executable needed to run the data file.
When working with more than one Office application, you’ll probably need to consider windows. Fortunately, there are plenty of API functions for working with them. GetActiveWindow retrieves the window handle for the currently active window — the new window you last clicked. If there is no active window associated with the thread, the return value is NULL. Declare it and call it as follows:
Private Declare Function GetActiveWindow Lib "user32" () As Long Function apicGetActiveWindow() 'Return window handle of active window. Dim lngWindow As Long apicGetActiveWindow = GetActiveWindow() End Function
You can quickly learn whether a specific form is still the active window using the following expression if the host application supports a handle property (Access does):
GetActiveWindow() = | <> form.hWND
GetTempPath returns the path to the system’s temporary folder. It requires two parameters: the length of a string to contain the pathname and the string itself. This function returns the length of the pathname measured in bytes or 0 if the function fails. Declare it and call it as follows:
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _ (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long Public Function apicGetTempPath() As String 'Returns path to system's temporary folder. Dim strPath As String * 512 Dim lgnPath As Long lgnPath = GetTempPath(512, strPath) apicGetTempPath = Left(strPath, InStr(1, strPath, vbNullChar)) End Function
This function often works as a companion to GetTempPath. You might not use it often, but when you need it, you’ll find it handy. GetTempFileName creates a name for a temporary file. This function has four parameters: a string for the path for the file, a string prefix used to begin a unique filename, a unique number to construct the temporary name, and a string used to return the filename. Both the path and prefix strings are required and can’t be empty. The function returns the unique number used to create the file name or 0, if there’s an error. Declare it and call it as follows:
Declare Function GetTempFileName Lib "kernel32" Alias "GetTempFileNameA"
(ByVal lpszPath As String, ByVal lpPrefixString As String, ByVal wUnique As
Long, ByVal lpTempFileName As String) As Long Public Function apicGetTempFileName(str As String) As String 'Retrun a temporary file name. Dim strPath As String * 512 Dim strName As String * 576 Dim lngRet As Long lngRet = GetTempPath(512, strPath) If (lngRet > 0 And lngRet < 512) Then lngRet = GetTempFileName(strPath, str, 0, strName) If lngRet <> 0 Then apicGetTempFileName = Left(strName, _ InStr(strName, vbNullChar)) End If End If End Function
The above function combines GetTempPath and GetTempFileName to return a temporary filename. Simply pass a prefix and the function generates a unique name using the prefix.
This function retrieves a handle to the desktop window, which covers the entire screen. All other windows are drawn on top of the desktop window. This is one of the easier functions to implement, as there are no parameters. You’ll seldom use it alone; rather, you’ll combine it with other API functions. For instance, you might combine it with others so you can temporarily drop files onto the desktop or enumerate through all the open windows on the desktop. Declare it and call it as follows:
Private Declare Function GetDesktopWindow Lib "user32" () As Long Public Function apicGetDesktopWindow() 'Retrieves the handle for the desktop. apicGetDesktopWindow = GetDesktopWindow End Function
After retrieving a window’s handle using FindWindow, you might want to manipulate the same window using ShowWindow. There are usually native methods you should use instead, but the capability exists — and sometimes, the native solutions just don’t provide what you want. Declare it and call it as follows:
Private Declare Function ShowWindow Lib "user32" _ (ByVal hwnd As Long, ByVal nCmdSHow As Long) As Long Function apicShowWindow(strClassName As String, strWindowName As String, lngState As Long) 'Get window handle. Dim lngWnd As Long Dim intRet As Integer lngWnd = FindWindow(strClassName, strWindowName) apicShowWindow = ShowWindow(lngWnd, lngState) End Function
The VBA function calls both FindWindow and ShowWindow. The third argument, lngState passes one of the constants shown inTable A.
|SW_FORCEMINIMIZE||11||Minimizes a window.|
|SW_HIDE||0||Hides the window and activates another window.|
|SW_MAXIMIZE||3||Maximizes a window.|
|SW_MINIMIZE||6||Minimizes the specified window and activates the next top-level window.|
|SW_RESTORE||9||Activates and displays the window.|
|SW_SHOW||5||Activates the window.|
|SW_SHOWMAXIMIZED||3||Activates the window and displays it as a maximized window.|
|SW_SHOWMINIMIZED||2||Activates the window and displays it as a minimized window.|
|SW_SHOWMINNOACTIVE||7||Displays the window as a minimized window (without activating the window).|
|SW_SHOWNA||8||Displays the window in its current size and position (without activating the window).|
|SW_SHOWNOACTIVATE||4||Displays a window in its most recent size and position (without activating the window).|
|SW_SHOWNORMAL||1||Activates and displays a window.|