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

Hybrid Approach to VBA Monte Carlo models:
Combining worksheet and VBA functionality

ShareThis

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.

 

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.