|
Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins &
More! |
|
|
|
|
|
|
Three approaches to Excel VBA Monte Carlo simulations
| |
| |
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. |
