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