VBA selecting table without specifying name?

[mwl_y]

I have a sheet with just one table. As of right now, the table is named "Main", but it will get changed later (working with queries).
Here is a test function that checks for a certain set of values within two columns of the table:

Function testFunc(ByVal a As String, ByVal b As String) As Boolean
Dim tb
Dim i As Long
tb = ActiveSheet.ListObjects("Main").DataBodyRange.Resize(, 3)
For i = 1 To UBound(tb, 1)        'for each row in table,
    If tb(i, 2) = a Then        'check col 2 for a
        If tb(i, 3) = b Then    'check col 3 for b
            testFunc = True
            Exit Function
        End If
    End If 
Next       
End Function

Is there a way to modify my code so that I don't have to include the table name? I don't want to have to go to my VBA code and change every instance of the name if I were to change the table name again in the future.

Note: The sub which calls the above function will activate the sheet that "Main" is on before calling the function.

 

[smozgur]

You can refer to the list object by using its index number. Since there is only one table in the worksheet, the following should work:

tb = ActiveSheet.ListObjects(1).DataBodyRange.Resize(, 3)

 

[mwl_y]

You can refer to the list object by using its index number. Since there is only one table in the worksheet, the following should work:

tb = ActiveSheet.ListObjects(1).DataBodyRange.Resize(, 3)

Oh wow, that's perfect! Didn't realize we could use an index number.
Is the index number specific to the sheet as well? For example, if I used ActiveSheet.ListObjects(1) on a different active sheet, would it reference the first table on that sheet, or would it try to access the same table and throw an error?

 

[smozgur]

The ListObject object's parent is a Worksheet object. Therefore, each worksheet has its own ListObject object collection that could be accessed by the index number starting from 1.

For example, if I used ActiveSheet.ListObjects(1) on a different active sheet, would it reference the first table on that sheet, or would it try to access the same table and throw an error?

It will refer to the first table on that sheet - (the active sheet in this case).

Finally, you can also use Sheet1.ListObjects(1) or Worksheets("Sheet1").ListObjects(1) to access to the first table in a specific worksheet.

 

[mwl_y]

The ListObject object's parent is a Worksheet object. Therefore, each worksheet has its own ListObject object collection that could be accessed by the index number starting from 1.

It will refer to the first table on that sheet - (the active sheet in this case).

Finally, you can also use Sheet1.ListObjects(1) or Worksheets("Sheet1").ListObjects(1) to access to the first table in a specific worksheet.

This is great info. Thank you so much for your help!

 

[smozgur]

This is great info. Thank you so much for your help!

You're welcome.
Thanks for the feedback.