Monday, June 20, 2005
Rule 13: VBA Formulas versus Worksheet formulas
Quite often there is a choice. Write a formula in the worksheet, or produce some VBA code. How does one make the decision?
If there is conditional logic, then the first port of call is a VBA function. Its very difficult to read conditional logic in a worksheet formula, and they also tend to be quite long.
If the partial results are meaningful, then put these on the spreadsheet and break the formulas down in their component parts.
For example, if profit and loss is made up of different parts, cash, mark to market, transaction costs, fees etc, then a column for each of the parts makes sense. Then they can be totaled in the profit and loss column. If necessary, the other columns can be hidden. It makes debugging and analysis easier.
Putting the formula into VBA also gives a name to what the formula intends to achieve. ie. =B2+C2+D2 doesn't convey much meaning. =Cash(A2) + MarkToMarket(A2) + TCosts(A2) + Fees(A2) makes a lot more sense, even if you have to dig around for the details.
Putting the formulas into VBA also enables their definition to be in one place and one place only. ie. Code it once, not cut and paste the formula everywhere. The code can then be moved out to an XLA an centralised.
If there is conditional logic, then the first port of call is a VBA function. Its very difficult to read conditional logic in a worksheet formula, and they also tend to be quite long.
If the partial results are meaningful, then put these on the spreadsheet and break the formulas down in their component parts.
For example, if profit and loss is made up of different parts, cash, mark to market, transaction costs, fees etc, then a column for each of the parts makes sense. Then they can be totaled in the profit and loss column. If necessary, the other columns can be hidden. It makes debugging and analysis easier.
Putting the formula into VBA also gives a name to what the formula intends to achieve. ie. =B2+C2+D2 doesn't convey much meaning. =Cash(A2) + MarkToMarket(A2) + TCosts(A2) + Fees(A2) makes a lot more sense, even if you have to dig around for the details.
Putting the formulas into VBA also enables their definition to be in one place and one place only. ie. Code it once, not cut and paste the formula everywhere. The code can then be moved out to an XLA an centralised.
Subscribe to Posts [Atom]