A "CodeName" for an Excel table column for use in VBA

Question

I'd like to be able to retrieve values from an Excel table by row number and column name (for code readability and robustness).

In formulas, I can use Structured References with column header text and get a value from the table like this:

=INDIRECT(ADDRESS(<absolute_line_number>;COLUMN(<table_name>[<column_name>])))

This is robust for formulas because if the user renames the column, all Structured References to it in formulas will be automatically updated.

Not so for VBA code though.

For worksheets, it's possible to define Worksheet.CodeName for use in VBA code that will stay the same if the user renames the visible sheet name. No such property exists for an Excel table AFAICS.

The best idea I currently have is to make table headers 1-cell Named Ranges. Then I can get a value from a table in VBA like this:

<sheet_codename>.Cells(<line_number>,Range("<range_name>").Column)

This, however, bothers me because Named Ranges are disconnected from the table. E.g. if I rearrange tables on the sheet, the ranges will remain in the old place.

Is there a better option? "Better" means specifically:

Survives renaming and/or rearranging columns in the table, moving the table at least within the sheet

Tables are automatically assessable via a named range upon creation. You can find the header name and return any row below — urdearboy, Mar 10 '19 at 01:51
@urdearboy How would I "find the header name" if it can be renamed? — ivan_pozdeev, Mar 10 '19 at 02:48
Wow sorry I completely overlooked that part of your question - my bad — urdearboy, Mar 10 '19 at 02:52
@urdearboy If you yourself would like to know an answer, you can upvote the question to draw more attention to it. — ivan_pozdeev, Mar 10 '19 at 08:14

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Comment

A comment in each header cell of a ListObject remains there, if the column is renamed or rearranged.

If(!) you can hide all comments by Application.DisplayCommentIndicator = xlNoIndicator (all comments neither recognisable by red triangle nor visible during mouseover), this may be a workaround:

Private Sub RecognizeColumnsOfListObject()
    Dim lo As ListObject
    Dim lc As ListColumn
    For Each lo In ActiveSheet.ListObjects
        For Each lc In lo.ListColumns
            Debug.Print lc.Index    ' not unique, always 1, 2, 3, ...
            Debug.Print lc.Name     ' not unique, changeable
            If Not lc.Range.Cells(1).Comment Is Nothing Then
                Debug.Print lc.Range.Cells(1).Comment.text ' unique
            End If
        Next lc
    Next lo
End Sub

Named Range

If I give each header cell of a ListObject a name, it moves with the column if I rearrange the ListObject. As its Name.Value or Name.RefersTo begins with =<ListObjectName>... I get the absolute address by this:

Dim n As Name
With <sheet_codename>
    For Each n In .Names
        Debug.Print .Range(Mid(n.RefersTo, 1)).Address
    Next n
End With

Comment is a very ugly workaround. It's very prominent and annoying (pops up), and hiding it is extraneous startup logic and jeopardizes a useful Excel feature. — ivan_pozdeev, Mar 11 '19 at 03:16

score 0 · Accepted Answer · answered Mar 11 '19 at 03:41

On closer inspection, there's nothing wrong with making table headers Named Ranges. That's because such Names get assigned to a Structured Reference rather than raw cell address, so they will move around together with the column!

On the downside, this name is not printed in the address field (at least, in Office 2007) when selecting the header which is rather inconvenient ('cuz I can't quickly look up the name I should type into the code to get this column).

A "CodeName" for an Excel table column for use in VBA

2 Answers2

Comment

Named Range