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?