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

Commute Time 2 example - VBA code

ShareThis

Though the input worksheets between the two Commute Time VBA examples look remarkably similar, under the hood the two are vastly different.

Below is the entire code for the Commute Time 2 model.  Lines 1 - 8 and 12 - 31 are identical to the Commute Time 1 code.  In the current model, however, lines 10 and 11 replace the entire range of lines 7 - 46 of the previous example.

This model works by triggering a worksheet recalculation in line 10 (the same as pressing F9 in a worksheet), then reading the new simulated drive time from the worksheet (line 11), and updating the array (line 12) to keep track of the cumulative histogram data.



Since the output for this example looks identical to the previous example we have omitted it here.

Next we'll introduce VBA user forms for interacting with a model, and measure actual execution speed of two different approaches to the same calculation.

 

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.