BEST-PRACTICE way(s) of future-proofing the cell references within vba code for worksheets whose column order will change with time?

Kelvin4 used Ask the Experts™
I have an Excel 2016 piece with > ten worksheets with 10-30 columns each of 100-1000 rows.
Each row refers to a separate PRIMARY item; each column within one row presents property values of that one item.

In these worksheets, some columns are visible and exhibit data (or shapes) to the user, AND are used for data analysis via vba. Other columns are hidden and used for data analysis. Data analysis certainly involves computations of data from both visible and hidden columns. However, ALL visible data in worksheets is READ-ONLY. The user only accesses data values indirectly, via userForms, ribbon controls, etc.

Problem: As the piece develops in later versions, I will probably need to (a) insert new columns, (b) progressively change the ORDER of many visible columns, so users can follow the table intuitively. But changing column order is not compatible with vba procedures that reference cells/ranges by row and column index numbers.

My first thought was:
1. to declare a lngVariable to each column, and enter each lngVariable name at the top of the respective column.
2. when the piece is opened, or when a ws is activated, I would loop through the lngVariable names at the top of each sheet.column, and set the value of each lngVariable to the column index value in which that variable was located.

My second thought was:
3. To present the data that users need to see in a separate worksheet. But as PRIMARY items (ie rows) can be deleted or re-ordered, this is not a trivial solution.

What is the BEST-PRACTICE way of future-proofing the cell references within vba code for worksheets whose column order will evoke with time?

Many thanks!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Within VBA, you have 3 way to reference cells:
- With coordinates, ex :Range(x, y)
- With address, ex Range("A1")
- With name, ex Range("myName")

The 3rd way is probably what you are looking at, define named ranges in your workbook, then inserting or moving columns shouldn't matter as named ranges will be updated automatically.


Thanks, that has given me a good starting point.
Hugely appreciative of rapid response!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial