How to check that a label is a valid table name?

[JenniferMurphy]

Is there VBA code that I can use to check whether a text string, such as "Table12", is the name of a valid table in the calling sheet?

This is what I am currently doing. Is there a better way?

Dim loTable As ListObject
  On Error GoTo BadTableName
  Set loTable = Range(rnTable).ListObject
  GoTo TableNameOK
BadTableName:
  Call ErrMsg("Invalid table name (" & rnTable & ")", MyName)
  Exit Sub
TableNameOK:

Thanks

 

[RoryA]

You could simply loop through the listobjects on the sheet and check each one's name. There's nothing particularly wrong with your current approach , though I'd probably make it its own function rather than using Goto statements.

 

[JenniferMurphy]

You could simply loop through the listobjects on the sheet and check each one's name. There's nothing particularly wrong with your current approach , though I'd probably make it its own function rather than using Goto statements.

Yeah, I don't like Go To statements, either.

If I use a function, can I pass the listobject (loTable) as a By Ref parameter and load it in the function?

Thanks

 

[RoryA]

Normally, you'd return the listobject (or nothing) as the result of the function and assign that to your loTable variable:

Set loTable = GetTableByName("table name", ws)
If loTable is nothing then exit sub

for example.

 

[JenniferMurphy]

Normally, you'd return the listobject (or nothing) as the result of the function and assign that to your loTable variable:

Set loTable = GetTableByName("table name", ws)
If loTable is nothing then exit sub

for example.

Ok, thanks