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

Overview - Visual Basic (VBA) Monte Carlo simulations in Excel

ShareThis

Now that we've demonstrated the fundamentals of Monte Carlo simulations using the worksheet-only approach, we'll move on to more advanced techniques using Visual Basic for Applications (VBA).

VBA enables several advantages over the worksheet-only approach:

  • VBA can enable faster processing for large numbers of iterations (in the millions)

  • VBA can reduce demand on system resources for complex models

  • VBA can reduce file size, as models that store large amounts of data in worksheets (such as a million iterations of a worksheet-based simulation) easily consume many megabytes of data, and more easily introduce file errors.

An obvious disadvantage of the VBA approach is that it requires knowledge of the VBA programming language.  Another disadvantage is that interactions between the VBA code and the worksheet can be slow (VBA is fastest when it's running without worksheet interaction), so we will demonstrate some techniques for minimizing interaction with the worksheet.

In this section of the tutorial, we will explore three different approaches for using VBA in Excel to build Monte Carlo simulations:

  • "Pure VBA", using VBA for the entire simulation with minimal interaction with the worksheet

  • "Hybrid" approach, using a combination of worksheet and VBA code, interacting with one another to varying degrees

  • "Minimal VBA", building most of the model in the worksheet and using VBA only minimally.

On the next page we'll give an overview of the three approaches, with the pros and cons of each.

 

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.