power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Outputting raw data to the worksheet from a VBA sim

ShareThis

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.

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.