The Hybrid approach to building Monte Carlo simulations allows great flexibility
in model design and modification, and can achieve most of the key benefits from
either of the more extreme approaches (VBA Only and Minimal VBA).
The Hybrid Approach can blend the VBA functions and worksheet model in a variety
of ways. At the most basic level, bordering on the VBA Only approach, the
Hybrid approach can use the worksheet for inputting key variables into the VBA
code and recording output. The VBA code is still doing the heavy lifting.
At the other end of the spectrum, the Hybrid approach can contain most of the
key building blocks of the model and perform the calculations for each of the
independent model variables, using VBA to capture the independent variables, run
the iterations, summarize results, and write the summarized results back to the
worksheet. We will provide examples at both ends of this spectrum.
At the first end of the spectrum we'll give an example that is a minor
enhancement of the Simple Coin Toss model we just covered. Here, cell D15 allows us to
specify how many iterations we want to run. This is a useful input to
include in any Monte Carlo model, since it allows us to run just a few
iterations at a time during construction and debugging. This also allows
us to experiment easily with how many iterations are enough to yield high
consistency of results for any given model. After the code executes the
specified number of iterations, it writes the results to the spreadsheet in
cells D23 and D24.
Now we'll take a look at the VBA code behind this model.