CODEWITHSUNDEEP
vbaexcelrange
ChrisB
ChrisB

Reputation: 3225

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

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.

Upvotes: 1

Views: 1905

Answers (2)

Lucas Kellner
Lucas Kellner

Reputation: 441

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

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

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.

Upvotes: 4

Related Questions