[excel] VBA: Counting rows in a table (list object)

I am trying to write some VBA in Excel that can take the name of a table (list object) as a parameter and return the number of rows.

The following works, but isn't allowing me to pass in a string with the table name.

MsgBox ([MyTable].Rows.Count)

The following gives the error:

Object required

v_MyTable = "MyTable"
MsgBox (v_MyTable.Rows.Count)

The following gives the error:

Object variable or With block variable not set

v_MyTable_b = "[" & "MyTable" & "]"
MsgBox(v_MyTable_b.Rows.Count)

I also tried working with ListObjects, which I am new to. I get the error:

Object doesn't support this property or method

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("MyTable")
MsgBox(tbl.Rows.Count)

Thanks for any help!

You can use:

Sub returnname(ByVal TableName As String)

MsgBox (Range("Table15").Rows.count)

End Sub

and call the function as below

Sub called()

returnname "Table15"

End Sub

You can use this:

    Range("MyTable[#Data]").Rows.Count

You have to distinguish between a table which has either one row of data or no data, as the previous code will return "1" for both cases. Use this to test for an empty table:

    If WorksheetFunction.CountA(Range("MyTable[#Data]"))