By Ken Getz and Michael Kaplan
Programmers using the Windows API from VBA have always faced a handicap when compared with C programmers: VBA has never supported function pointers. Why should we care? We should care because many Windows API functions require you to tell Windows the address of a function to call, to satisfy information the API function needs. C programmers have always been able to pass the address of a function around as a long integer value, but VBA programmers haven't been able to do this.
The idea is that your code calls a specific Windows API function, passing to it the address of a procedure in your own project that you want that API function to call. This allows you to use an entire class of Windows API functions that are otherwise unusable from VBA. The API function you call might, in turn, call your callback procedure each time if finds an object of the correct type, or at specified intervals, or at some specific time as it's doing its work. Your VBA procedure will need to receive exactly the same type and number of parameters as the Windows API function expects to send to it. Windows is going to call your procedure, passing a set number and type of parameters, whether or not you've designed your VBA procedure to accept them correctly. Of course, the penalty for an incorrect design is instant death, i.e. a general protection fault (or GPF).
Timing and Enumerating
As a simple example, let's look at the Windows SetTimer function. This function allows you to specify a time interval, and the address of a function to call after the time interval has elapsed. Therefore, you could specify an interval of 1000 milliseconds (one second), and provide a function that updates a digital clock display with the current time every second.
Although VB5 and Access each provide a similar mechanism (VB5 through its Timer control, and Access with its Timer event for each form), other VBA environments provide no such support. Even VB5 and Access require a form to which to attach the functionality. If you need to execute code at regular intervals in an application that doesn't support a built-in timer, or you don't want to use a form for this purpose in VB5 or Access, you need some mechanism such as the SetTimer API function.
But how can you supply the SetTimer API function with the address of the function you want it to call? In VB5, you can use the AddressOf operator to pass the address of your function to SetTimer, but AddressOf isn't supported in any other VBA host. This article will provide an alternate mechanism, available to all VBA hosts, making this technique possible. (Note: Although AddressOf isn't supported in a VBA host besides VB5, the AddressOf keyword is still reserved in all VBA hosts. Therefore, VBA won't allow you to use AddressOf as the name of a module or procedure.)
As another example, there's a class of Windows API functions that enumerate things. That is, given a place to start, and the type of object to enumerate, the function will visit each object of the specified type and will call a user-supplied function for each object. If you want to retrieve a list of installed fonts, installed printers, or open windows, you'd use an API function that requires a callback function. Windows visits each object in turn, calling your function for each object it finds that matches your request. That function normally sends information about the found object back to the calling function, allowing your application to do something with the list of objects.
This article provides an example that demonstrates the use of both these API functions. The sample form, frmCallbacks (see FIGURE 1) uses the EnumWindows API function to provide a list of top-level windows. It uses its callback function, CallBackFunc, to display the list of windows in the list box on frmCallbacks. In addition, this form uses the SetTimer API function, with its callback function, TimerProc, to display a digital clock on the form. Rather than using the AddressOf operator (as you would in VB5), the examples use the user-defined AddrOf function.
What are the limitations of using callback functions?
If you break the first rule, your API function simply won't be able to find the callback function. If you break the second rule, all bets are off: your code will cause a GPF, or do nothing.
Warning! Use the AddrOf function presented in this article at your own risk. It has worked for us, and we've tried it in several scenarios in Access 97 and in Excel 97. Like any other undocumented feature, however, this one isn't guaranteed to work in all situations. It most likely will not work in future versions of VBA, although we can safely assume that a simple replacement with the AddressOf operator in a future VBA version will offer the same functionality.
Many GPFs Later
Because Office (and other VBA hosts) share most of the same code with VB5, and because AddressOf is a reserved keyword in all flavors of VBA 5.0, we were convinced that VBA had to be able to find the address of a public function somehow. We spent many hours digging through the VBA DLLs and finally figured out the series of steps it takes to force VBA to cough up the address of a public procedure in memory. For a discussion of this excavation, see the sidebar "Where No One's Gone Before". To work with the samples provided with this article, however, all you need to know is how to call the AddrOf function, and what it returns (see end of article for download details).
The AddrOf function, provided in the basAddrOf module, uses functions exported from VBA332.DLL to retrieve the address of a public procedure. To call it, pass a string containing the name of the function for which you need a memory address. It returns the address as a long integer you can send to API functions that require one. For example, you might call the SetTimer API function like this:
Dim lngID As Long
lngID = SetTimer(0, 0, 1000, AddrOf("TimerProc"))
The SetTimer API function requires four parameters. The first two contain optional parameters that aren't required, so we're sending 0 for both. The third parameter indicates the number of milliseconds to wait before calling the callback function, and the fourth parameter contains the address of the function you'd like to have called regularly, each time the interval expires. After calling the code shown above, Windows will call TimerProc every 1000 milliseconds. (Just for the record: SetTimer returns a long integer that identifies the timer it started, and the timer ticks off at continuous regular intervals. You'll need this return value to kill the timer.)
Using Timers in VBA Applications
As mentioned earlier, other than Access and Visual Basic, VBA hosts don't normally supply a means of running a procedure repeatedly at a specified interval. If you want to do something as simple as display a digital clock on a form, you're out of luck. Using the Windows API, however, it's simple. The SetTimer API function does the job, and it's declared like this:
Private Declare Function SetTimer Lib "User32" ( _
ByVal hWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
The first parameter allows you to specify the window handle of a window to associate with the timer. If you don't need to associate a window with the timer (and we don't), you can send 0 for this value. If you don't specify a window handle, Windows will ignore the second parameter (an identifier for this timer), and so you can send 0 for this parameter, as well. The third parameter, uElapse, indicates the number of milliseconds you'd like to wait before running the procedure you specify. The final parameter contains the address of the procedure to call every uElapse milliseconds. As you can probably guess, you'll use the AddrOf function to get the address of that function.
The SetTimer function returns a long integer identifying the specific timer (you can call SetTimer many times, starting multiple timers, each running a specified procedure at regular intervals). Make sure you store this value somewhere; you'll need it later to stop Windows from running your procedure. Use the KillTimer API function to stop the timer.
In the sample form's Initialize event procedure, you'll find the following:
lngID = SetTimer(0, 0, 1000, AddrOf("TimerProc"))
If lngID = 0 Then
MsgBox "Unable to initialize a new timer!"
End If
This code instructs Windows to call the TimerProc procedure once every 1000 milliseconds. Note that we've placed the return value from SetTimer into the lngID variable. If lngID contains 0, you know that Windows was unable to create a new timer. (It's very unlikely this will occur; Windows 95/NT provide a virtually infinite number of timers.)
This code, in the form's Terminate event procedure, kills the timer as the form unloads:
If lngID <> 0 Then
Call KillTimer(0, lngID)
End If
Warning: If you end your project by clicking the Reset button in the VBA IDE, the form's Terminate event won't fire, and the timer will keep running. This will cause undesirable results, so make sure you terminate this test application by closing the form.
So what's in the TimerProc procedure? This procedure's formal declaration is specified by the Windows API, and it must match the following:
' hWnd: Handle of window associated with this timer.
' lngMsg: The WM_TIMER message (275, or &H113)
' lngID: The timer's identifier (the same value
' returned by SetTimer).
' lngTime: Milliseconds since Windows started (the
' same value returned by GetTickCount.
Public Sub TimerProc(ByVal hWnd As Long,
ByVal lngMsg As Long, ByVal lngID As Long, _
ByVal lngTime As Long)
' Do whatever you want done every <n> milliseconds.
Call frmCallbacks.ShowTime
End Sub
The timer callback function must accept four parameters, all explicitly by value, as described in the code. Once inside the procedure, you can perform whatever action you need. (You should, of course, think twice about performing a blocking action, e.g. displaying a message box, inside the timer callback procedure.) In this example, we're calling a public procedure in the sample form that updates the display of the current time. This simple procedure from frmCallBacks is shown below:
Public Sub ShowTime()
lblTime.Caption = Time
End Sub
Because Windows sends the callback function the identifier of the timer, it's possible you could have a single procedure react to multiple timers. Windows also sends in the current time (in milliseconds), and you can use that information in taking the actions you need from the callback procedure. Note that just because you've asked for the timer procedure to be called every 1000 milliseconds doesn't mean that Windows will slavishly meet your needs. If Windows has more important things to do, your timer might not get called at exact intervals. If this worries you, ask to be called more frequently.
Retrieving a List of Open Windows
The EnumWindows API function troops through all the existing windows, and for each window, will call a function you've provided. The API declaration looks like this:
Private Declare Function EnumWindows Lib "User32" ( _
ByVal lpEnumFunc As Long, ByVal lngParam As Long) As Long
In this case, lpEnumFunc is a long integer that contains the memory address of a function to call, and lngParam is a value you can send as a parameter to that function, for each window. Windows expects you to pass it the address of a function set up like CallBackFunc shown here:
Function CallBackFunc(ByVal hWnd As Long, _
ByVal lngParam As Long) As Long
' Returning anything except True causes the enumeration
' to stop. Do whatever you like with hWnd and lngParam.
CallBackFunc = True
End Function
In this case, it's crucial that your function receives two long integers by value; Windows is going to send those two values, so your function must be set up to retrieve them. Windows passes your function the window handle of the window it's currently visiting; your function can do whatever it wants with that value. (The example function adds the window caption to a list box containing the names of all the open windows with captions.) The second parameter, lngParam, contains the value you passed as the second parameter in the call to EnumWindows. In our example, we use it to indicate whether to show all windows, or only visible ones. The decision was made before the call to EnumWindows; each time CallBackFunc gets called by Windows, it passes the window handle of the current window, and an integer indicating whether we intended to skip invisible windows.
The problem, then, is how to provide the API call with the address of the callback function. This is where AddrOf comes to the rescue. To call EnumWindows, you might use code like this:
Call EnumWindows(AddrOf("CallBackFunc"), 0)
Once you've made this call, EnumWindows walks the list of existing windows, and for each window, calls CallBackFunc, passing it both the window handle of the window it's found, and the second parameter you passed to EnumWindows.
The example form, frmCallbacks, uses EnumWindows to display a list of all windows, or a list of windows that are visible. If you select to show the list of visible windows, then you can also click the Flash It button, and highlight the selected window. (If you're showing all windows, the button is disabled; there's no point in attempting to flash the caption of a hidden window.) When you first open the form, and whenever you click the check box, the form runs the following code:
Sub ResetList()
Dim fVisible As Boolean
fVisible = (chkVisible = vbChecked)
cmdFlashIt.Enabled = fVisible
With lstWindows
.Visible = False
.Clear
Call EnumWindows(AddrOf("CallBackFunc"),CLng(fVisible))
.ListIndex = 0
.Visible = True
End With
End Sub
As you can see, the procedure calls the EnumWindows function, passing the address of the callback function, along with the value of the fVisible flag.
As it works through all the top-level windows, Windows calls the CallBackFunc procedure once for each window:
Function CallBackFunc(ByVal hWnd As Long, _
ByVal lngParam As Long) As Long
Dim strCaption As String
' This expression is True unless lngParam is True (you're
' looking only for visible windows) and the current
' window isn't visible. Check out the Imp operator in
' online help for more information.
If CBool(lngParam) Imp IsWindowVisible(hWnd) Then
' Retrieve the window's caption (if it has one), and
' add it to the list of windows.
strCaption = GetWindowCaption(hWnd)
If Len(strCaption) > 0 Then
' Call a user-defined method of the form to add an
' item to the list box.
frmCallbacks.AddItem strCaption, hWnd
End If
End If
' Returning anything besides True causes the enumeration
' to stop.
CallBackFunc = True
End Function
Windows sends this function the window handle of the current window, as well as the parameter sent to the EnumWindows function in the ResetList procedure. CallBackFunc first uses the IsWindowVisible API function to see if the current window is visible. If the window should be shown in the list box (based on its visibility and the flag sent from the main form), CallBackFunc then calls the GetWindowText API function to retrieve the caption of the window. If the window has a caption, CallBackFunc adds the caption to the list box on the main form.
Warning: Unlike almost any other VBA procedure, you must be very careful with the declaration of a function used as a callback for a Windows API function. The EnumWindows function expects its callback to accept two values, not two addresses, so it's up to you to declare its parameters with the ByVal keyword. Missing this important step will give Windows yet another reason to crash your application.
It's Not for Everyone
Certainly, these techniques won't appeal to all Office and VBA developers. If you're programming in VB5, of course, you'll simply use the AddressOf operator to retrieve the address of a function for use with the Windows API. On the other hand, if you need to use a callback function, or simply need the address of a VBA function for use with the Windows API, there's no replacing this technique. As in the two simple examples shown here, there's no way to solve certain classes of programming problems without some method of providing a function address.
As mentioned previously, the usual warnings apply. As with any undocumented behavior, you can't expect Microsoft to support your use of this technique. In addition, chances are negligible that these entry points into VBA332.DLL will exist in any future release of VBA. (You can be reasonably sure, however, that future versions of VBA will all support AddressOf directly.)
Finally, be extremely careful when using this technique during testing. You're working "without a net" when using function addresses, and VBA is likely to crash if you provide an incorrect signature for your callback function. Be extremely careful with the ByVal keyword, making sure you match the definitions required by the Windows API. Using callbacks can be extremely rewarding, allowing you to solve problems you can't touch with any other method, but they can be treacherous if approached lightly.
Where No One’s Gone BeforeIn VB5, using the AddressOf operator doesn’t require any extra work or research — it’s built right into the type library. In other VBA hosts, however, it’s not that simple. Although AddressOf is a reserved word in VBA (i.e. can’t use it as a variable or procedure name), you can’t use it to obtain a function pointer. Determined to solve the problem of finding a way to use this internal functionality from Office 97 and other VBA hosts — and armed with a limited set of tools — we found the exploration to be a labor of frustration. (Our trial-and-error technique of attempting to call undocumented exported DLL functions resulted in over 100 invalid page faults and over 50 “bad DLL calling convention” errors.)
We started by using the DUMPBIN.EXE utility that comes with Visual Studio. Among other things, it allows you to list the names of all procedures exported from a DLL. FIGURE A shows the command window running this utility.
Our attempt to puzzle out the exported functions in VBA332.DLL was eased somewhat by the names of the functions. The function at the heart of it all (and the one that got us started) is TipGetLpfnOfFunctionID (aliased as GetAddr in our code). The giveaway was the prefix: “lpfn” is the C++-type prefix that refers to a long pointer to a function. Exactly what we were seeking! Finding this function quickly led to finding another function that refers to function IDs, TipGetFunctionID (aliased as GetFuncID in our code). Through trial-and-error and the ability to debug VB5 applications inside the C++ debugger, we determined that each of these functions expects three long integer values as parameters. By watching the stack and how it changed as each function was called, we figured out the following function declarations (the parameter names are our own; we don’t know what the original developers intended them to be):
Private Declare Function GetAddr Lib "Vba332.dll" _
Alias "TipGetLpfnOfFunctionID" (ByVal hProject As Long, _
ByVal strFunctionID As String, _
ByRef lpfn As Long) As Long
Private Declare Function GetFuncID Lib "vba332.dll" _
Alias "TipGetFunctionID" (ByVal hProject As Long, _
ByVal strFunctionName As String, _
ByRef strFunctionID As String) As LongThe function expects three long integers, yet we’re sending a string to each, as the second parameter. Why? From Windows API’s point of view, a string passed using ByVal sends the address of that string, a long integer. How about the function ID, also sent as a string? Again, we needed to send the address of a block of memory containing information about the function. That is, the function ID appears to be some sort of binary data, and the functions expect to receive the address of that data. Generally, a byte array would be the preferred storage for this value, but VBA doesn’t provide the means to retrieve the address of the array. (The alternative is to store the data in a string, but VBA modifies binary data stored in strings; it converts the data back and forth from Unicode to ANSI.) The simplest solution, however, seems to work: simply pass a string. Doing so passes the address of the string buffer, which solved our problem.
Both of these functions appear to use a COM HRESULT value for the return values. This means that they return 0 for success, or a non-zero for an error. By running the functions from a VB5 test application and watching the stack inside the C++ debugger, it was clear we were close to figuring it all out.
In fact, from here, the only remaining puzzle was the long integer used as the first parameter for each of the functions. Our guess was that this is some sort of handle to the project (because of this guess, we’ve used hProject as the parameter name). It was clear this value didn’t change from call to call inside of a session, yet after a “compile all” in Access or VB, and in between sessions, the value changed. After conjecturing that this value was indeed some sort of pointer to the running project, we were once again stuck. There seemed to be no way to retrieve a pointer to a VBA project, except through the VBA extensibility library. The extensibility library is meant for applications that interact with the VBE IDE, not for retrieving information about the running application, so that wasn’t to be a reasonable solution. Finally, in yet another pass through the output from DUMPBIN, we stumbled on a function named EbGetExecutingProj. This function fills in its single ByRef parameter with the same value as the mysterious number that showed up in the first parameter of the other two functions. Its declaration is:
Private Declare Function GetCurrentVBAProject _
Lib "vba332.dll" Alias "EbGetExecutingProj" ( _
hProject As Long) As LongGiven all the function declarations, then, the exact chain of events that will get you a function pointer is as follows (we unearthed and discussed them in reverse order):
To use the AddrOf function, shown in FIGURE B, call AddrOf, passing it a string containing the name of the function whose address you need. Not all functions will work, however. The function whose name you pass in must be a public function or Sub procedure, in a standard module. In addition, the function must be in the same project as the AddrOf function.
Download source code for this article here.
Ken Getz is a Senior Consultant with MCW Technologies, a Microsoft Solution Provider focusing on Visual Basic and the Office and BackOffice suites. He is also co-author of VBA Developer's Handbook and Access 97 Developer's Handbook, both from SYBEX.
Michael Kaplan is the owner and lead developer of Trigeminal Software, Inc., a consulting firm that focuses on all kinds of unusual interconnectivity solutions in Microsoft Access, SQL Server, Visual Basic, and Office. A regular contributor to several Microsoft Access publications, he has contributed to several books on Access development. You can reach him at michka@trigeminal.com.