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

Three approaches to Excel VBA Monte Carlo simulations

ShareThis

Three broad categories of VBA approaches to Monte Carlo simulation with Excel are described below, along with some of the tradeoffs inherent in each approach.

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

  Pure VBA Hybrid Minimal VBA
Description
  • Entire model built in VBA code; little or no interaction with worksheet
  • Model combines VBA code with worksheet data and/or functions
  • Model constructed primarily in worksheet, using VBA only to recalculate the worksheet and write data table
Pros
  • Fast execution
  • Minimal demand on system resources
  • Enables small file size
  • Leverages the strengths of both "extreme" approaches
  • Easiest to design and construct
  • Easier to edit the model
  • Greatest flexibility in modifying the model (adding new variables, changing the independent drivers of each variable, etc.)
Cons
  • Requires most extensive knowledge of VBA
  • Most time consuming to construct
  • Possible slower execution speed due to interactions between code and worksheet, though workarounds exist
  • Slowest execution speed
  • Large file size if retaining data after running the sim

This tutorial provides examples of each of the three basic approaches.  First, however, let's cover a couple of syntax differences between worksheet functions and VBA functions.

 

 

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.