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

Commute Time 1 VBA worksheet output

ShareThis

The output for our Commute Time 1 VBA example consists of:

  • Iterations Processed (D44), with a % calculation (worksheet-based) to the right (E44)

  • A cumulative histogram chart

  • A summary data table (C57 - F93) which receives the output of the VBA array

This provides an example of combining worksheet functions with VBA code in an integrated way.  If we know, for instance, that our desired final product is a cumulative histogram, we can build our VBA function in such a way that the output lends itself easily to that endpoint.

The code for this example executes relatively quickly.  However, if we wish to change the model - for instance, changing the way in which the drive time for a particular segment is calculated, or adding a fourth variable (or segment) - this would entail editing VBA code.  This can be more cumbersome than making changes to a worksheet-based model, and may run a higher risk of introducing errors.

Our next example will highlight a different method of modeling the same problem with a hybrid approach, relying more heavily on the worksheet than on 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.