VBA Check If table Exists in Excel
VBA Check if table Exists in Excel. Let us check if a table exists on the worksheet. And also check if multiple tables are exist on the Sheet. We use ListObjects collection. In this tutorial we have explained multiple examples with explanation. We also shown example output screenshots. You can change table and sheet name as per your requirement. We also specified step by step instructions how to run VBA macro code at the end of the session.
Example to to Check If a table Exists on the Worksheet
Let us see the example to check if a table Exists on the Worksheet using VBA. The sheet name defined as ‘Table‘. And we use table name as ‘MyTable1‘. You can change these two as per your requirement.
Where ListObjects represents the collection.
'VBA Check if table Exists Sub VBAF1_Check_If_Table_Exists() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject 'Define Variable sTableName = "MyTable1" 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Loop through all tables in the worksheet For Each loTable In oSheetName.ListObjects 'Check specified table If loTable.Name = sTableName Then 'If exists MsgBox "Specified table is available.", vbInformation, "VBAF1" End If Next End Sub
Output: Here is the following output screenshot of above example macro VBA code.
Check Multiple Tables are exists on the Worksheet
Here is the another example to check if multiple tables are exists on the Worksheet using VBA.
'VBA Check if multiple tables Exists on the worksheet Sub VBAF1_Check_If_Multiple_Tables_Exists() 'Declare Variables Dim oSheetName As Worksheet Dim sTableName As String Dim loTable As ListObject Dim bCheck As Boolean 'Define WorkSheet object Set oSheetName = Sheets("Table") 'Loop through all table names For iCnt = 2 To 7 'Get Table Name sTableName = Sheets("Result").Range("A" & iCnt) bCheck = False 'Loop through all tables in the worksheet For Each loTable In oSheetName.ListObjects 'Check specified table If loTable.Name = sTableName Then 'If exists Sheets("Result").Range("B" & iCnt) = "Available" bCheck = True Exit For End If Next 'If table doesn't exists If bCheck = False Then Sheets("Result").Range("B" & iCnt) = " Not Available" End If Next End Sub
Output: Let us see the following output screenshot of above example macro VBA code.
Instructions to Use Example Macro Code
You can follow step by step instructions to run above macro example VBA code.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window in the visual basic editor(VBE)
- Run macro by clicking F5 or Run command
- The difference between before and after macro, see the above output screenshot.
Other Related References
Click on the following links for related reference articles. It helps for your reference.