This article provides a highlevel overview of how and why to integrate VBA in Crystal Ball spreadsheet models. Given that Oracle Crystal Ball runs on Excel, it is worth exploring how VBA can create behaviors that are NOT natural to Excel!
What’s so great about Excel VBA when you Model?
I have been using spreadsheets to model stuff (even primitively) for the better part of 20 years… From my very beginnings with Visicalc using an old Apple IIe my Dad gave me to Lotus 123 on a PC my Dad gave me to Excel on a computer I bought myself. Before Excel VBA, Lotus 1-2-3 had a very cool tool known as HAL (Human Access Language) (See NY Times, 1986) that enabled you to sequence a large number of tasks using human language.
Nearly 20 years hence, we have Excel VBA (Visual Basic for Applications) to enable to build complete and robust macros. Given that Oracle Crystal Ball runs on Excel, it is worth exploring how VBA can create behaviors that are NOT natural to Excel.
Understanding the Crystal Ball Macro Framework
|
|
Macro Name
|
Runs...
|
|
|
CBBeforeSimulation
|
Immediately after the Start Simulation command or Single-Step command
|
|
CBBeforeTrial
|
Before random numbers have been placed in assumption cells
|
|
CBAfterRecalc
|
After Excel has recalculated the model but before a trial value has been retrieved from the forecast cells
|
|
CBAfterTrial
|
After the forecast trial values have been retrieved and entered into the forecast charts
|
|
CBAfterSimulation
|
When the simulation is complete or stopped with a Stop command, by single-stepping (after each step), or for any other reason
|
Figure 1: Crystal Ball Macro Framework (Oracle Corporation, 2007)
Each time you run a simulation, you define a number of trials to be executed. Each trial follows the following macro sequence. Essentially, Crystal Ball will look at the Excel model’s macro code (VBA) to see if any of these macro functions are present and execute it in the order define in the model above. I.e.:
Public Function CBBeforeSimulation() As Integer
'Your code is here
End Function
Most of the code necessary to incorporate CB macro call is in the User Manual or available in the developer kit (available in the Crystal Ball Resource Disk)
Why use VBA with Crystal Ball?
One of the most basic things VBA will do for your CB models is making them standard and reusable. It does so because VBA enables you to manipulate data and values to conduct the simulation. I.e. Say you are building a model to optimize production costs along several plants, it might be that you need to run a sub-simulation for a plant usage cost for your model to be accurate. Alternatively, you could feed raw production data into your model and have CB fit the data before running the simulation.
Other useful applications of VBA and Crystal Ball include:
- Simulating complex situations, including running totals and queuing models
- Distribution fitting with raw data
- Creating custom functions
- Creating custom reports
- Ribbon buttons (Excel 2007)
- Automating tedious and repetitive data extraction from data bases or the internet
- Integrating Crystal Ball into various office applications, i.e. MS Access or Project
Using CB with VBA - The possibilities are endless.
If you have any questions on how to incorporate CB into your models using VBA, please call us or post a question in our Forum Section.