|
Thus far the examples we've given using VBA have returned only summary results
to the worksheet. In some cases it's beneficial to have an entire dataset
- ie, the results of each individual iteration - stored in the worksheet.
This can be useful when you want to perform statistical analyses on the dataset,
or analyze other factors on an iteration-by-iteration basis. The net
effect is a worksheet table of static data, much like we achieved through the
"Paste Values" illustration earlier in the
tutorial.
In these cases it's rather straightforward to write code using techniques we've
already covered that will cause the VBA function to write data to the worksheet
on an iteration-by-iteration basis. The FastSlow example shows two
different approaches to this from a coding perspective, and shows compellingly
why the "fast" method is better than the "slow" one.
Study the following code excerpt from the FastSlow example. This loop
drives the model's iterations.
The "slow" user option writes the output
of each iteration directly to the worksheet one iteration at a time (the "Else"
block above).
The "fast" option stores the data from
each iteration in the FastSlowData array rather than printing it directly to the
work sheet iteration-by-iteration. Once the main iterations have
completed, the "fast" option then prints the entire array to the worksheet in a
single stroke:
On my ThinkPad X301 laptop, the "Fast"
option for 1000 iterations takes less than 1/10 of a second, while the "Slow"
option takes more than a minute to execute. This illustrates clearly the
cost (in execution time) of frequent interactions with the worksheet.
Holding data in arrays during program execution, then dumping large amounts of
data from those arrays all at once can greatly enhance speed of execution.
| 
