How do I get the name of a table from a range variable?

Question

I dimmed the variable:

Dim mainTableRange As Range

Then gave it a value:

Set mainTableRange = Range("tLedgerData") ' tLedgerData is an Excel table.

Now I'm trying to get the name of the table (which is "tLedgerData") from the variable to reference columns in that table even if the table name changes.

I tried

mainTableRange.Name

and

mainTableRange.Name.Name

(See how do you get a range to return its name.) Both threw run-time error '1004': Application defined or object-defined error.

mainTableRange.Select selected all table data excluding the header and total rows.

*[...] to reference columns in that table even if the table name changes* - wait, if the table name changes, won't `Range("tLedgerData")` refer to nothing? — Mathieu Guindon, Dec 20 '16 at 20:16
Can you add an example of exactly what you're *actually* trying to accomplish, i.e. exactly what do you mean with "reference columns in that table"? — Mathieu Guindon, Dec 20 '16 at 20:22
run-time error '1004', perhaps you wrote the range name incorrectly, or the activesheet don't contain the named range. try to correct the name of the range, or try to add the sheet name before Range("tLedgerData"), or be sure that to activate the sheet wish contains the named range. — D. O., Dec 20 '16 at 20:32
To clarify, I was looking for a way to reference table columns to, for example, add a calculation or change the auto-filter settings, without having to constantly refer to the table and column by name, and possible have to change every reference if the table column name changed. @Mat's Mug your suggestion to use the `ListObject` nailed it! — ChrisB, Dec 21 '16 at 01:42

score 4 · Accepted Answer · answered Dec 20 '16 at 20:42

I think you're having an X-Y problem here: solving problem X when the solution is for problem Y.

[...] to reference columns in that table even if the table name changes

Have the table / ListObject alone on its own dedicated worksheet, and give the sheet a CodeName. That way you can do this:

Dim tbl As ListObject
Set tbl = LedgerDataSheet.ListObjects(1)

And now you have the almighty power of the ListObject API to do whatever it is that you want to do. For example, retrieve the column names:

Dim i As Long
For i = 1 To tbl.ListColumns.Count
    Debug.Print tbl.ListColumns(i).Name
Next

In other words, you don't need to care for the name of the table. What you want is to work with its ListObject. And since you never need to refer to it by name, the table's name is utterly irrelevant and the user can change it on a whim, your code won't even notice.

I agree. However, if you do want to continue by calling the table name do the following: `Dim mainTableRange As ListObject` and then `Set mainTableRange = Worksheets("Sheet1").ListObjects("tLedgerData")` — Automate This, Dec 20 '16 at 20:49

score 0 · Answer 2 · answered Dec 20 '16 at 21:30

I believe an Excel table and named-range are two different things which is why the .name.name doesn't work. A table is a ListObject and once you set a range equal to a table you should be able to continue to call that range without an error.

Curious, what is the reason why your table might change unexpectedly?

I wrote out some lines of code to show a couple things. You can create tables and reuse the range variables after the table name changes. You can also set AlternativeText for the table with some identifying string and use that to locate a particular table if you suspect the table name may change.

Option Explicit
Public TestTable As Range
Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, [$A$1:$C$4], , xlYes).name = "Table1"
    ActiveSheet.ListObjects("Table1").AlternativeText = "Table1"
End Sub
Sub SetTableRange()
    Set TestTable = Range("Table1")
End Sub
Sub SelectTable()
    TestTable.Select
End Sub
Sub RenameTable()
    ActiveSheet.ListObjects("Table1").name = "Table2"
    [A1].Select
End Sub
Sub SelectRenamedTable()
    TestTable.Select
End Sub
Sub ClearSelection()
    [A1].Select
End Sub
Sub FindTable1()
    Dim obje As ListObject
    For Each obje In ActiveSheet.ListObjects
        If obje.AlternativeText = "Table1" Then
        MsgBox "Found " & obje.AlternativeText & ". Its current name is: " & obje.name
        End If
    Next obje
End Sub
Sub ConvertTablesToRanges()
    ' I found this snippet in a forum post on mrexcel.com by pgc01 and modified
    Dim rList As Range
    On Error Resume Next
    With ActiveSheet.ListObjects("Table1")
        Set rList = .Range
        .Unlist                           ' convert the table back to a range
    End With
    With ActiveSheet.ListObjects("Table2")
        Set rList = .Range
        .Unlist                           ' convert the table back to a range
    End With
    On Error GoTo 0
    With rList
        .Interior.ColorIndex = xlColorIndexNone
        .Font.ColorIndex = xlColorIndexAutomatic
        .Borders.LineStyle = xlLineStyleNone
    End With
End Sub

How do I get the name of a table from a range variable?

2 Answers2