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

Commute Time 2 - an alternative hybrid approach

ShareThis

Here we introduce an alternate version of the Commute Time problem using the hybrid approach.  However, in this case we rely upon the worksheet to calculate the drive time, using VBA only to iterate the model and summarize the data into the same array as in the previous example.  This approach borders on the "minimal VBA" approach, differing only in that this model uses VBA to summarize results rather than outputting the raw data for each iteration.

Note that the input screen below appears nearly identical to that of the Commute Time 1 VBA example.  The primary difference is in rows 24, 30, 41, and 43 where we used the worksheet formulas developed in the worksheet-only version of the same problem to calculate drive time in the worksheet rather than in the VBA code.

One other subtle difference is that we have set the number of iterations to 100 rather than 50,000.  This is because the near-minimal VBA approach used in this version is vastly slower in executing.  If using this method for a real simulation where thousands or millions of iterations are required, be prepared to let the computer run overnight (or longer).

The trade-off for slow execution is flexibility.  As you can see, modifying this model to incorporate a fourth segment of the drive would be fairly straightforward and could be achieved by working strictly with the worksheet; no modification to the VBA code would be required.

Let's take a look at the code to see how it works.

 

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.