|
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.
| 
