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

Limiting the number of iterations through VBA code


When your simulation model prints the entire iteration dataset to your worksheet as in the previous two examples (FastSlow and LotsaData), you'll want to limit the number of possible iterations to the space available for output. 

Since Excel 2003 and earlier allow a maximum of 65,536 rows per worksheet, I generally set the limit of iterations per column or panel of data output to 50,000 when using older versions of Excel. 

In Excel 2007, the row limit per worksheet was increased to 1,048,576, so when using this version and later I typically use 1,000,000 iterations as the limit per column or panel of data output.  (These limits apply only when outputting the full data to the worksheet; otherwise, as seen is some of the earlier examples, several million iterations executing in a matter of seconds is not uncommon.)  Using the multiple panels of output method illustrated in the LotsaData example can enable higher numbers of total iterations despite the row limitations, but it is still important to make sure the total number of iterations chosen by the user does not exceed the capabilities of the program.

Thus I often include at the beginning of my function a code block that checks the number of requested iterations, and resets it to the maximum allowable if the requested number is higher than that.  The following code block from the LotsaData example illustrates:

In this case, we determined that 4 million iterations was the rounded upper limit of the program, as follows:

  • We wanted compatibility with older versions of Excel, so 50,000 rows of output per panel was the limit.

  • Each panel would contain three columns: one for the Counter, another for the Random Number, and another to leave a column of white space between data sets.  Since Excel 2003 allows a maximum of 256 columns, we could have a maximum of 256 / 3 = 85 panels of input.

  • 85 panels x 50,000 rows per panel allows us a total of 4,266,666 iterations.  We rounded this down to 4 million for simplicity.

Note that we overwrite the worksheet values in lines 7 - 8, in addition to updating the variables in the VBA code in lines 9 - 10.  We also notify the user via a message box (line 11) that the number of iterations has been reset. 

All of the examples in this tutorial are designed for compatibility with older version of Excel and are therefore limited to 50,000 rows of data output and 256 columns.


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.