VBA ActiveWorkbook / RefersToLocal - 1004 Application defined or object defined error

Question

When executing the following line, the 1004 runtime error (Application defined or object defined error) is thrown

rangeString = ActiveWorkbook.Names.Item("MyTableName").RefersToLocal

I cant see anything wrong with my code, and the table name appears to be correct. This code is inside a class module and I am not a vba expert, so I dont know if that introduces any issues with scope etc.

Answer 1

If your object (MyTableName) is an Excel data table, use the Range used by the ListObject, e.g.

Sub test()
    Dim L As ListObject

    Set L = ActiveSheet.ListObjects("MyTableName")
    ' at this point you can inspect the properties of L in the debugger

    ' retrieve the local address of tue underlying range
    Debug.Print L.Range.AddressLocal

End Sub

Answer 2

Please do not select this as an answer. This is only for value add :)

If you just have the name of the table and are not sure which sheet the table is in then you can try this

Sub Sample()
    Dim oSh As Worksheet
    Dim oLo As ListObject

    For Each oSh In ThisWorkbook.Worksheets
        For Each oLo In oSh.ListObjects
            If oLo.Name = "MyTableName" Then
                Debug.Print "=" & oSh.Name & "!" & oLo.Range.Address
                Exit For
            End If
        Next
    Next
End Sub

Answer 3

I think Range Object can access any table at any sheet just fine:

Debug.Print "=" & Range("MyTableName").Parent.Name & "!" & Range("MyTableName").Address