Advice on optimizing spreadsheet
Thanks for the code, RB.
I did implement it in two versions of my model - one in which I had
explicitly copied the If statements down 10000 rows, and the other in
which I was writing the columns in the VBA subroutine itself.
The first spreadsheet was about 3.5 MB in size, and the second was about
2.5 MB. The contents include two graphs, one work sheet with about 20
rows and the main worksheet with 3 columns of 10000 rows where each row
represents the results for 1 simulation.
The results of the timing were as follows:
Explicit IF statements in the worksheet - 30094 milliseconds
Range statements in the VBA subroutine - 31047 milliseconds
When I repeated the process for 2000 simulations:
Explicit IF statements in the worksheet - 6344 milliseconds
Range statements in the VBA subroutine - 6797 milliseconds
The difference in the speed is almost negligible, since I am writing
only one column in the first case (the pre-defined formulas calculate
the other two), whereas in the second I am writing all three columns.
|