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

Hybrid approach example: Commute Time VBA

ShareThis

So far we've looked at a few simple "hybrid approach" examples, using VBA code interacting with worksheet input and output.  Each of these examples has been based on a single modeled variable - the flip of a coin, the weather, etc.  Now we'll go a small step forward and give an example of a model involving multiple random variables, all calculated in the VBA code.

For this example, we'll return to the "commute time" problem we first modeled with the worksheet-only approach.  To recap the problem, we're modeling how long it takes to get to work given severable variables.  Our journey has three segments:

  • Drive 2 miles on a highway, with 90% probability you will be able to average 65 MPH the whole way, but with a 10% probability that a traffic jam will result in average speed of 20 MPH.

  • Come to an intersection with a traffic light that is red for 90 seconds, then green for 30 seconds.

  • Travel 2 more miles on a surface street.  70% of the time you travel at 30 MPH.  10% of the time you average 20 MPH, 10% of the time you average 40 MPH, and 10% of the time there's a traffic jam that takes you 30 minutes to travel these two miles.

The Inputs section of the worksheet, shown below, lets us set the iterations and screen update interval as usual.  Then it allows us to set the values of each segment of the commute separately.  The VBA code will use these inputs to calculate the drive time for each segment separately during each iteration.

 Let's take a look at the VBA code.

 

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.