VBA programming strategy

[ernieL]

I have always written my VBA code using absolute cell references. A problem arises when I find it necessary to add a row or column after the program has been finished. The spreadsheet updates but the code behind it does not. This requires much rewriting of the code. There must be a better way, perhaps using relative references or names? I have not tried either of these since I lack some kind of guidance or example. Can any of you point me in the right direction?
Regards,
ernieL

 

[phantom1975]

Been there, done that. There are a few ways to work around this. One of the things that I do from time to time is hide the first 100 rows of the worksheet I am using before I start my project. I can then use the hidden rows to embed lists, headers, whatever I might need to facilitate other things. For example, let's say that you use row 1 to put headers in for your columns of data. For example, cell D1 will have "Employee names" in it. Fast forward a bunch of work on the worksheet later. The header that was in D1 is now in L1 because of columns that have been moved. I can write code to search through row 1 and find the header I am looking for to identify the column that I need. The code will never need to be rewritten because it dynamically searches for the header rather than depend on a specific cell.

Another way to do this is to use constants. For example:

const EmployeeName as String = "D"

Range(EmployeeName & "150").Value = "Milton"

Now if you ever need to redefine that column, you just change the value in the constant expression.

I'm sure that there are a few other ways to do this, but these are the two I tend to use most often. I hope that this helps.

 

[shg]

Named ranges are a great way for VBA to interface with a worksheet, and they automatically adapt to insertions and deletions. Instead of referring to cells by address, you can reference them by name, e.g.,

Range("Input").ClearContents

See http://www.contextures.com/xlNames01.html.