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

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!
Kelvin
Kelvin4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Hi,

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin4Author Commented:
Thanks, that has given me a good starting point.
Hugely appreciative of rapid response!
Kelvin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.