Callbacks in VBA using AddressOf

Most of the procedures you write are called by you, or by VBA on your behalf. A callback turns that around: you hand another piece of software the address of one of your own procedures and let it do the calling.

How to do this in VBA is with the AddressOf operator. The following is a working example: it logs, to the Immediate window, the title of every visible top-level window on your machine using the Windows API EnumWindows Function.

EnumWindows walks every top-level window that's currently open. It doesn't return a list … instead, it asks you for a Function and calls that Function once for each window it finds, passing you the window's handle each time. You must write that Function … the 'callback' … and tell EnumWindows where to find it. The 'where to find it' is an address in memory, and AddressOf (before the name of the procedure) is how to get it. Key things to note when working with AddressOf are:

• The procedure that AddressOf gets the address of must live in a standard Module … AddressOf will not compile against a procedure in a Class, UserForm or Document Module

• The procedure it is getting the address of must have exactly the signature the caller expects … the same parameter types, the same ByVal / ByRef, and the same return type … any variation is likely to result in the host application being force-closed

• AddressOf can only be used as an argument in a Windows API procedure call

• An unhandled error inside the callback will get raised back across the API boundary, which is asking for trouble … so always include error handling

The code

Add a standard Module and paste in the following. It comes in three parts: the Windows API declarations, the Sub you run and the callback.

First the Windows API declarations … EnumWindows is the core of these ... the one that enumerates the windows and calls your callback ... the other 3 (IsWindowVisible, GetWindowTextLength and GetWindowText) are just there to give the callback something to do!

Next, the Sub you actually run. It resets the counter, prints a header, and passes EnumWindows the address of the callback using AddressOf. The second argument (here just 0&) is an application-defined value that Windows passes straight through to the callback (more on that below) …

And the callback itself …

How it works

When you run ListVisibleWindows, EnumWindows starts walking the top-level windows and, for each one, calls EnumWindowsProc with that window's handle in the hWnd parameter. Inside the callback we:

•      Skip anything that isn't visible, using IsWindowVisible

•      Skip anything with no title (where GetWindowTextLength returns 0 … there can be a lot of these)

•      Otherwise read the title with GetWindowText into a pre-sized buffer, and print it

The return value is important … returning 0 stops the enumeration, anything else tells the code to continue. This implementation always returns 1 (including if there is an error) so that an error while processing one window won't stop the enumeration for subsequent windows.

What if you actually wanted to get an array or Collection of all of the top-level windows (handles, captions etc)? Then just add a Module-level array or Collection. ListVisibleWindows should initialise it. EnumWindowsProc adds items to it. Then ListVisibleWindows can return it (though likely renamed to indicate that it returns something, not just lists them!).

Passing state: the lParam

The second parameter of both EnumWindows and the callback is lParam. Whatever you pass as the second argument to EnumWindows is handed back to your callback. In C code, typically that would be a pointer to a struct (aka Type) holding all the data that the callback needs. In VBA it's possible to do the same with VarPtr and CopyMemory … though for most jobs (as in the code in this post) the Module-level approach is much simpler to implement and much simpler to read.

Wrapping up

AddressOf isn't something you'd use every day. But when you need Windows … or any DLL … to call back into your code then its invaluable, and once the EnumWindows pattern clicks, the many other 'Enum…' and hook / callback APIs all follow the same shape.

The fiddly part of all this is getting the Windows API declarations correct across 32- and 64-bit. You can use VBE_Extras to simplify this by inserting the declarations directly into your code. Additionally, VBE_Extras enhances the F1 "help" key to open up the relevant web page for over 12,000 Windows API Functions / Subs as well as a large number of Types, Enums and Consts.