Coding Format and Organization - VBA in Excel

  • Thread starter Thread starter TinyTim
  • Start date Start date
T

TinyTim

I wrote an Excel macro for departmental use. It used 8 input columns
(A - H) and 6 result columns (I - N). Everything worked fine for a
while. Then the inevitable 'change' came up. It was to resequence
the columns and add a couple of new columns for input. I had been
using the following format type of code in my macro.

Range("I16") = (Range("E16") / Range("F16")) * 100
Range("J16") = Range("I16") * Range("G16")
Set rStrt = Worksheets("tMain").Range("a6")
dSalary = rStrt.Offset(wIdx, 1)
lAmount = rStrt.Offset(wIdx, 2)

The user wants a new column prior to column 'F'. I now have to go
into the code and change all references to column F and beyond in all
moves and calculations.

Should I have defined constants and used the 'offset' format like:

CONST CURSALARY as Long = 5 'Column E
Range("I16") = rStrt.Offset(curRow, CURSALARY) / Range("F16")) *
100


What's the best way to avoid this problem? How do the PRO's do it?

Thanks,

tt
 
One excellent approach is to used named ranges as anchor points. Then when
your anchors get moved you dont have to change your code.

so your VBA range reference would be of the form Range("CurSalary"). You can
use .Resize , .Offset , .End(XLup) etc with this as required.

But yes it is also good practice to define constants separately.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Back
Top