vba error handling in loop

Question

New to vba, trying an 'on error goto' but, I keep getting errors 'index out of range'.

I just want to make a combo box that is populated by the names of worksheets which contain a querytable.

    For Each oSheet In ActiveWorkbook.Sheets
        On Error GoTo NextSheet:
         Set qry = oSheet.ListObjects(1).QueryTable
         oCmbBox.AddItem oSheet.Name

NextSheet:
    Next oSheet

I'm not sure whether the problem is related to nesting the On Error GoTo inside a loop, or how to avoid using the loop.

Answer 1

What about?

If oSheet.QueryTables.Count > 0 Then
  oCmbBox.AddItem oSheet.Name
End If 

Or

If oSheet.ListObjects.Count > 0 Then
    '// Source type 3 = xlSrcQuery
    If oSheet.ListObjects(1).SourceType = 3 Then
         oCmbBox.AddItem oSheet.Name
    End IF
End IF

Answer 2

Actualy the Gabin Smith's answer needs to be changed a bit to work, because you can't resume with without an error.

Sub MyFunc()
...
    For Each oSheet In ActiveWorkbook.Sheets
        On Error GoTo errHandler:
        Set qry = oSheet.ListObjects(1).QueryTable
        oCmbBox.AddItem oSheet.name

    ...
NextSheet:
    Next oSheet

...
Exit Sub

errHandler:
Resume NextSheet        
End Sub

Answer 3

I that can help you, I have the following function in my "library". Since it's a mix of functions I wrote and functions I found on the net, I am not very sure where that one comes from.

  Function GetTabList(Optional NameSpec As String = "*", _
              Optional wkb As Workbook = Nothing) As Variant
  '   Returns an array of tabnames that match NameSpec
  '   If no matching tabs are found, it returns False

      Dim TabArray() As Variant
      Dim t As Worksheet
      Dim i As Integer

      On Error GoTo NoFilesFound
      If wkb Is Nothing Then Set wkb = ActiveWorkbook
      ReDim TabArray(1 To wkb.Worksheets.Count)
      i = 0
      '   Loop until no more matching tabs are found
      For Each t In wkb.Worksheets
          If UCase(t.Name) Like UCase(NameSpec) Then
              i = i + 1
              TabArray(i) = t.Name
          End If
      Next t
      ReDim Preserve TabArray(1 To i)
      GetTabList = TabArray
      Exit Function

      '   Error handler
  NoFilesFound:
      GetTabList = False
  End Function