Programming Excel with VBA and .NET

Trap QueryTable Events

Performing web queries in the background can seem a little strange—particularly if they are set to refresh periodically. Most Excel actions are synchronous, and it might surprise a user to see Excel pause for a second, update some cells, and then continue on as if nothing happened. This can become a big problem if the source of the web query changes and causes the web query to fail—the user will see an error message periodically and may not know what to do or how to fix it (Figure 24-7).

Figure 24-7. Failed web queries may display errors asynchronously

To handle errors from asynchronous web queries, you must hook in to the QueryTable events. You have to declare a QueryTable object variable using the WithEvents keyword in order to trap its events. WithEvents can be used in only a class module or an Excel object module (such as the code module for a worksheet or workbook).

For example, to handle asynchronous events for a QueryTable in the wsWebQuery worksheet module, follow these steps:

1. Display the code window for the worksheet by double-clicking on wsWebQuery in the Visual Studio Project Explorer.

2. Add the following declaration to the worksheet’s code module at the class level (outside of a procedure definition):

       Dim WithEvents qt As QueryTable

3. Select the qt object in the object list at the top of the code window, and then select AfterRefresh from the event list to ...