Financial Simulation Modeling in Excel

VBA AND OTHER LANGUAGES: DIFFERENT PROGRAMS FOR DIFFERENT USES

One component of this text that differentiates it from other simulation texts is the use of Visual Basic for Applications (VBA), which is an object-oriented language appropriate for working with Excel for smaller, simpler macros. VBA's main advantages are in its simplicity and its ability to use worksheet and application objects to manipulate the output, which is usually (but not always) also an Excel worksheet or object. For shorter, ad hoc projects and when flexibility is valued over computational horsepower, VBA is generally as easy to work with as other languages, and the ability to have calculations done in the Excel worksheet and then read by the code can save a massive amount of programming time.

However, VBA is considered by many to be insufficiently powerful for Monte Carlo simulations due to its speed and persistent questions about random number generation. In general, we believe that random number generation in the 2010 version of Excel does not have the same problems seen in previous versions of Excel. But the speed question is still an issue for modelers working in real time.

As a result, a large proportion of Monte Carlo–related models are done in C++, a general-purpose programming language. C++ programs can be built to handle extremely large amounts of data and calculations and can be integrated effectively into Excel workbooks. The main issues with C++ are the amount of time it takes to program and most ...