I had a program that stored values in various spreadsheet cells. Since my workbook has several worksheets, when I write a formula and referring to the value held in any one cell of any one spreadsheet, the formula becomes a very long expression with no mnemonics as to the meaning of the cell's value. To make the code easier to read, I've started using Global Variables to provide mnemonic names to the cell's value. So far, so good.
Because the global variable's cell is 'hard coded', if a user inserts, or deletes, a line above the global variable's cell, the formula will adjust which cell it looks to for the value, but the global variable's cell will not adjust and the formula will no longer work.
Because my workbook will be used by hundreds of people, I want to make it as 'bullet proof' as possible - to include (if possible) enabling formulas that use cells that hold the value of global variables to continue working if someone inserts or deletes a line above it. I can think of two ways to accomplish this:
- Figure out a way to use the global variable directly in a formula, instead of having the global variable use a cell and the formula use the same cell, or
- When someone inserts or deletes a line above the cell holding the value of the global variable, have the code change which cell holds the value of the global variable in the same way the cell in a formula will change.
Does anyone know the code to make this happen (either in one of the ways I described, or in another way that you come up with)?
Bookmarks