This post considers several aspects about improving the speed of spreadsheets in both MS Excel 2003 and 2007. It has significant effects on animations in MS Excel.
In this first part, a “stopwatch” macro is described which, when incorporated in any of calculation loop, can measure the frequency your loop is executed.
In this first part of the tutorial the macro was used to estimate the number of single formula calculations (as an array of “rand()” functions) executed per second by small, medium and large spreadsheets benchmarked for both 2003 and 2007 versions of MS Excel.
About the speed of animated models in MS Excel – part #1
by George Lungu
– This is a presentation about speed benchmarking for Excel workbooks. Though it focuses mostly on
measuring the speed of loop based Excel animated models, some of the principles outlined here can be
applied to any standard Excel worksheet.
My personal take on MS Excel:
– For my models I prefer to use older versions of Excel (2003), yet I believe I can bring some value to
the “canonic” or “orthodox” Excel users by talking about the factors that affect spreadsheet speed.
– As an engineer I’ve always loathed expensive fashionable software, software controlled by command
line, software that crashes and locks up, software that has no built in graphical display interface,
software that has no real time parameter control during the simulation run (most are like this) and
software that cannot be stopped, saved and restarted later to simulate from the same point (I haven’t
met any that would do that yet).
– I also hate software that enforce a slow learning curve and it has too many options. As a conclusion I
hate almost any software sold as a black box, usually for a hefty price and often for an additional
periodic license fee.
– Besides the all aforementioned drawbacks I see in common packages,
what I hate the most is sluggish software. In my industry (electronics
design) it can take a hours and sometimes a whole week to model a
trivial, Mickey Mouse type of circuit. And this is done on software
costing well into six digits licensing fee per person, per year.
– Excel is nothing more than a glorified abacus or a slide ruler. The
animated and fully interactive models an average Joe can build in Excel
(with a certain personal time effort of course) can fit very particular
needs and avoid all the problems mentioned in the first paragraphs.
– I’ve been using spreadsheets to replace an hour of simulation time with a second of simulation
time, for almost no cost (Excel) and most often with a significant but acceptable drop in precision.
– Creating animated models in Excel allows the average Joe to focus on engineering and science
rather on syntax and this is because 99% or more of any model can be developed using built in
worksheet formulas.
Benchmarking Excel speed – the stopwatch macro “speed_test_1”:
– The speeds attained in the modern competitive swimming are well over any estimate made
just a few of decades ago.
– This is in because of better coaching helped by two important tools, the stop watch and the
video camera. A swimmer can now measure his performance more precisely and can also see
himself with his own eyes during practice. This is much more efficient in correcting his swimming
technique than listening to coaches alone.
– The first tool we need for improving speed in Excel is a stopwatch.
– Let’s look at the following VBA code =>>>>>
– It is essentially an infinite loop which prints
Dim p As Double
in cell B4 the value of the number of loop
Dim Measurement_Start As Double
iterations per second.
-The VBA time function “Timer” retrieves
Sub speed_test_1()
precisely the number of seconds since midnight.
p = 0
-The variable “Measurement_Start” is initia- Measurement_Start = Timer
lized at the start of the loop and during each Do
[B4] = p / (Timer – Measurement_Start + 0.001)
loop cycle the macro will print in cell [B4]
p = p + 1
the loop iteration speed which is the number
Loop
of loop cycles divided by the time passed since
End Sub
the start of the loop.
– In the formula for cell [B4] I arbitrarily added the 0.001 residue in the denominator in order to
avoid the dreaded division by zero during the first iteration of the macro
Speed results using the macro “speed_test_1()” – small window:
– We used a completely blank workbook with only two cells filled [A4:B4] and one button (text box)
to which we assigned the “speed_test_1()” macro. We have no formulas yet in the sheet.
– Start the macro by clicking the green button and let it run 30-60 seconds, then stop it by
holding the “Esc” key down (this is an integrative measurement so the longer it runs the more
precise the measurement).
– We did this measurement with a small size window to later show the speed loss associated with
the window size.
Excel 2003: Excel 2007:
Loop speed: 6389 iterations/sec Loop speed: 3762 iterations/sec
Lets repeat the measurement with the window expanded
– In this case, due to the lack of data there is minimum speed degradation when the window is
maximized.
Excel 2003: Excel 2007:
Loop speed: 6027 iterations/sec Loop speed: 3688 iterations/sec
Lets create an array of formulas within the worksheet:
– In order to test the calculation speed on a large
Sub clear_array()
number of formulas let’s introduce the following two
new macros in Module1. [A10:IV65536].Clear
– The first macro will clear the worksheet of any old End Sub
formulas and the second macro will create a 200 x
5000 array of random number generator formulas
Sub generate_array()
(1000,000 formulas), the result of this array being
[A11:GR5010].Formula = “=rand()”
updated during every cycle of the “speed_test_1()”
End Sub
macro.
– The speed results for calculating an array of 1000,000 numbers using the “rand()” built-in function
is shown below:
Excel 2003 – small window: Excel 2007 – small window:
Loop speed: 8.07 iterations/sec Loop speed: 11.38 iterations/sec
Excel 2003 – large window: Excel 2007 – large window:
Loop speed: 7.71 iterations/sec Loop speed: 9.79 iterations/sec
How does the speed scale with the number of formulas?
Sub generate_array_1K()
[A10:GR65536].Clear
– In this section, the previous test is being run on both, a reduced size [A11:GR15].Formula = “=rand()”
End Sub
window and a full screen window for the following series of formula sizes:
1000 formulas, 10,000, 100,000, 1000,000 and 10 million formulas.
Sub generate_array_4k()
To the right you can see the various macros used for generating the arrays
[A10:GR65536].Clear
of formulas. [A11:GR30].Formula = “=rand()”
End Sub
Medium
Small worksheets Large worksheets
worksheets
Sub generate_array_10k()
15 [A10:GR65536].Clear
Excel Computation Speed [A11:GR60].Formula = “=rand()”
End Sub
Hardware: Gateway SX2840-01 Sub generate_array_40k()
[A10:GR65536].Clear
Monitor resolution: 1280 x 1024
[A11:GR210].Formula = “=rand()”
End Sub
Sub generate_array_100k()
[A10:GR65536].Clear
2003
[A11:GR510].Formula = “=rand()”
2003 End Sub
Sub generate_array_400k()
[A10:GR65536].Clear
[A11:GR2010].Formula = “=rand()”
End Sub
Sub generate_array_1M()
[A10:GR65536].Clear
[A11:GR5010].Formula = “=rand()”
End Sub
Number of kilo-formulas in the worksheet
Sub generate_array_10M()
[A10:GR65536].Clear
1 10 100 1000 10000
[A11:GR50010].Formula = “=rand()”
End Sub
to be continued …
