VBA ActiveWorkbook / RefersToLocal - 1004 User Defined or Object Error

Question

When executing the next line, a 1004 runtime error (Certain program or object definition error) is called

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

I see nothing wrong with my code, and the table name seems to be correct. This code is inside the class module, and I am not a vba expert, so I don’t know if this introduces any problems with scope, etc.

Answer 1

If your object (MyTableName) is an Excel data table, use the range used by the ListObject, for example

 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 add value :)

If you just have a table name and don’t know which sheet the table is in, 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 a Range object can easily access any table on any sheet:

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

Answer 4

For those who are against similar errors, try this single-line font in the nearest window:

For ii = 1 to ThisWorkbook.Names.Count : ? ii & " " & ThisWorkbook.Names.Item(ii).Name : next

and from the results you may find that the sheet name is part of the name ie 'My Second Sheet'!Print_Area' - this full name should be used as an index for .Names :

? ThisWorkbook.Names.Item("'BlahBlah'!Print_Area").RefersTo