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

Answer 1

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).