Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding Format and Organization - VBA in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding Format and Organization - VBA in Excel
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 "TinyTim" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Organization Chart in Excel 2003 | Excel Discussion (Misc queries) | |||
Tree organization in excel | Excel Discussion (Misc queries) | |||
where can I get organization chart add-in for Excel 2000? | Excel Discussion (Misc queries) | |||
Excel Data Re-Organization | Excel Discussion (Misc queries) | |||
How do i set up an organization chart in Excel? | Charts and Charting in Excel |