Insulate for Winter

When your VBA method (macro) needs to get data from a section of the workbook, there are many ways to achieve this, and most of them will perform quite well. But what happens when somebody tweaks the spreadsheet? When a user adds sub-totals or a new column to the worksheet, will this require a macro fix?

Developers who would rather avoid this type of distraction, (preferring instead to invent new and interesting applications,) attempt to Insulate their Code from the Interface.

In the case of Excel applications, the interface refers to everything that Excel is - the columns and rows which users tend to move around and delete, the Excel command set: filters, sorts, etc.

One strategy is to declare a Range variable and assign it to an known position - the data table's first column heading is a good one. (Use a named range, which will follow the actual cell around.) Your table scan happens relative to this anchor point; made easy using the Offset method:

dim rng as Range
dim tbl as Range
dim intRow as Integer
dim AmtCol as Integer

Set rng = Range("data_corner")
Set tbl = rng.CurrentRegion
AmountCol = Range("AmountCol").Column - rng.Column

For intRow = 1 to 20
   If rng.Offset(intRow,AmountCol) > 99.99 Then
     ... something happens!

To make this really bullet-proof, don't hard-code any assumptions about the table layout. Gold-level protection requires naming a Range on the worksheet for each column heading you need to work with (the AmountCol in the above example). Now the users can move the individual columns, insert new ones, or relocate the whole table, and your code continues to work.

Named ranges not feasible? Then you should have a routine to search for the actual column headings and cache those positions in variables before working on the table. This is reasonably reliable, until the headings get changed.

The quick and dirty method is to assume the positions of the columns will never change, set up constants for those so the code is at least readable, and hope for the best.

DNF to the fool who hard-codes an actual cell address in their VBA code!