Commute Time 1 VBA code

ShareThis

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.