Tables()

This returns a collection of all tables in a workbook, with the option to exclude tables on hidden worksheets.

I use tables exclusively. I didn't always. But I found them superior to all alternatives in almost all instances. And so, since about 2007, I stopped using individual cells. And since 2010, I stopped using cell references completely. I encourage those who haven't made the change to catch up.

Since I use tables so much, I found that I needed to either list all tables in a workbook or to determine if a table exists in a project before attempting to use it. This most common usage is like this:

If Exists(Tables(ActiveWorkbook), "MyTable", oLo) Then Err.Raise DspError, , _

"Problem:" & vbTab & "MyTable not found in " & ActiveWorkbook.Name & vbLf & _

"Solution:" & vbTab & "Click Load Tables to add MyTable"

Here is the function

Function Tables(Optional ByVal oWorkbook As Workbook = Nothing, _

Optional ByVal bExcludeHidden As Boolean = False) As Collection

' Description:Creates a collection of Tables

' Inputs: oWorkbook Table's Workbook

' bExcludeHidden Exclude tables on hidden worksheets

' Outputs me Success: Tables Collection

' Failure: Nothing

' Example: ? Exists(Tables(ActiveWorkbook), "Table1")

' Date Ini Modification

' 02/04/15 CWH Initial Development

' 01/23/16 CWH Changed Sheets to Worksheets

' 07/13/17 CWH Added bExcludeHidden parameter

' 02/01/19 CWH Added check for no Active workbook

' Declarations

Const cRoutine As String = "Tables"

Dim oCollection As New Collection

Dim oWks As Worksheet

Dim oLo As ListObject

' Error Handling Initialization

On Error GoTo ErrHandler

Set Tables = Nothing 'Assume not found

' Procedure

If oWorkbook Is Nothing Then Set oWorkbook = ActiveWorkbook

If Not oWorkbook Is Nothing Then

For Each oWks In oWorkbook.Worksheets

If Not bExcludeHidden Or oWks.Visible = xlSheetVisible Then

For Each oLo In oWks.ListObjects

oCollection.Add oLo, oLo.Name

Next

End If

Next

End If

Set Tables = oCollection

ErrHandler:

Select Case Err.Number

Case Is = NoError: 'Do nothing

Case Else:

Select Case DspErrMsg(cModule & "." & cRoutine)

Case Is = vbAbort: Stop: Resume 'Debug mode - Trace

Case Is = vbRetry: Resume 'Try again

Case Is = vbIgnore: 'End routine

End Select

End Select

End Function

Resources:
Exists()
DspErrMsg()