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

Commute Time 1 VBA code


The VBA code behind our CommuteTime1 example follows a simple outline:

  • For each iteration,

    • Calculate drive time for each segment

    • Calculate total drive time

    • Round the result to nearest minute and store in a "summary data" array

    • If this iteration is a screen update interval, write the data to the worksheet and pause briefly

  • After iterations are finished, output the final data to the worksheet

In line 3 below we declare an array that will capture only the summarized, cumulative data, which will be copied to the worksheet at each refresh interval.

Once the three separate driving segments have been calculated for each iteration, we wrap up the function by adding the drive time for the three segments together and rounding the result to the nearest minute (line 46); updating our summary array with the result of the current iteration (line 47); updating the worksheet with the current status if the current iteration is a specified update interval (lines 50 - 52); and pausing the program for 1/10 second to allow time for the graph on the worksheet to refresh (lines 54 - 59). 


As a final step in line 64, we write the array contents to the worksheet one more time.  This is necessary in case our refresh interval does not divide evenly into the number of iterations - otherwise line 52 would be sufficient.

Next we'll look at how the output appears on the worksheet.


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.