Four ListObject QueryTable Tests – Each Better Than the Last

Four ListObject QueryTable Tests – Each Better Than the Last

I started with this devil-may-care bit of code:

Sub Zero()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject

For Each ws In ThisWorkbook.Worksheets
    For Each lo In ws.ListObjects
        On Error Resume Next
        lo.QueryTable.Refresh
        On Error GoTo 0
    Next lo
Next ws
End Sub

Just wrap the QueryTable.Refresh in an On Error pair and don’t sweat it, that was my stance, at least for a day or two. If the ListObject had a QueryTable it would get refreshed. If it didn’t, it wouldn’t.

As the time got closer to hand it off to other people – people who might be bummed if their data didn’t refresh for reasons I hadn’t anticipated – I took a more prudent approach. I wrote some code to check if the ListObject actually had a QueryTable. This allows me to isolate the “ListOject with no QueryTable” error from all the others that might fly in under the radar.

I ended up with a simple function that’s now in my code library. But before we get to that, I’ll show you three lesser ListObject QueryTable tests, from bad to better:

#1 – Testing with Err.Number

In case you’re not familiar with On Error statements, I should clarify that On Error Resume Next let’s your code run willy-nilly through any and all errors. The madness only ends when an On Error Goto 0 statement is encountered. On Error Goto 0 also resets Err.Number to 0.

Sub One()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
Dim qt As Excel.QueryTable

For Each ws In ThisWorkbook.Worksheets
    For Each lo In ws.ListObjects
        On Error Resume Next
        Set qt = lo.QueryTable
        If Err.Number = 0 Then
            qt.Refresh
        End If
        On Error GoTo 0
    Next lo
Next ws
End Sub

I would never do this (not even in a really old Google Groups answer, I hope). It doesn’t fix the basic problem. The refresh is still happening with On Error set to Resume Next. It’s even worse if you have Else clauses. You could blunder through them as well before getting back to On Error Go To 0. The only way I can see it working is with another On Error Go To 0 right inside the IF clause before the refresh, and that’s just ugly.

#2 – Using an ErrorNum variable

Sub Two()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
Dim qt As Excel.QueryTable
Dim ErrorNum As Long

For Each ws In ThisWorkbook.Worksheets
    For Each lo In ws.ListObjects
        On Error Resume Next
        Set qt = lo.QueryTable
        ErrorNum = Err.Number
        On Error GoTo 0
        If ErrorNum = 0 Then
            qt.Refresh
        End If
    Next lo
Next ws
End Sub

This approach fixes the problem in the previous routine by immediately setting an ErrorNum variable to Err.Number’s value. This tightens up the On Error Resume Next scope so it’s only active during the test. Pretty good, and for tests that don’t involve objects I’d probably stop there.

#3 – You’ve got an object variable, just use that!

Sub Three()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
Dim qt As Excel.QueryTable

For Each ws In ThisWorkbook.Worksheets
    For Each lo In ws.ListObjects
        Set qt = Nothing 'Don't forget this!
         On Error Resume Next
        Set qt = lo.QueryTable
        On Error GoTo 0
        If Not qt Is Nothing Then
            qt.Refresh
        End If
    Next lo
Next ws
End Sub

Since we’re trying to set qt to something, let’s just test if it’s not nothing. This has the same advantage as the previous one: On Error statements bracket just the one line of your test, preventing stealth errors. The big gotcha is you’ve got to remember to set qt to Nothing before you try to set it to something. Otherwise, if the previous ListObject had a QueryTable, and this one doesn’t, the Resume Next will happily ignore the error and leave qt set to the previous one. That’s confusing, and potentially tragic.

#4 – The right way

Sub Four()
Dim ws As Excel.Worksheet
Dim lo As Excel.ListObject
Dim qt As Excel.QueryTable

For Each ws In ThisWorkbook.Worksheets
    For Each lo In ws.ListObjects
        Set qt = GetListObjectQueryTable(lo)
        If Not qt Is Nothing Then
            qt.Refresh
        End If
    Next lo
Next ws
End Sub

Function GetListObjectQueryTable(lo As Excel.ListObject) As Excel.QueryTable
On Error Resume Next
Set GetListObjectQueryTable = lo.QueryTable
End Function

Here I’ve moved the test into a function and put it in my utility module along with tests for workbook state, folder existence and other such mundanities. I know it works, I don’t have On Errors in the main module, and I only need Resume Next in the function, cause there’s not a heckuva lot of room for resuming.

Tangential miscellany

Here’s a pithy Jeff Weir rant on testing for ActiveCell.PivotTable versus ActiveCell.Listobject

This post deals with Excel-2007-and-on ListObject.QueryTables. In earlier versions QueryTables belonged to the worksheet they were on. In this Stack Overflow answer Dick (DDOE) Kusleika posts a function to find any QueryTable by name.

A thing I should know, but maybe you can tell me

What’s the difference between ListObject.Refresh and QueryTable.Refresh?

5 thoughts on “Four ListObject QueryTable Tests – Each Better Than the Last

  1. Tushar once told me to move all On Error Resume Next blocks to their own procedure. Good advice. I should really follow it more often.

    You could also use qt.Refresh rather than lo.QueryTable.Refresh, but for no benefit that I could imagine.

    • Thanks for pointing that out. I’m going to fix those.

      It is interesting advice, and I think it’s probably the right thing to do. But in some cases it seems clearer to leave the test in the middle of the code, rather than squirreling it away in yet another tiny function… Or at least not worth the bother… or… damn I suppose he’s right.

  2. Pingback: Daily Dose of Excel » Blog Archive » Learning from my Errors

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>