RESEARCH ARTICLES | RISK + CRYSTAL BALL + ANALYTICS

Leveraging the power of Crystal Ball using Excel VBA

Author: Eric Torkia, MASc/Wednesday, July 16, 2008/Categories: Monte-Carlo Modeling, Analytics Articles

Rate this article:
No rating

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

Alt

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.

 

Print

Number of views (141)/Comments (0)

Please login or register to post comments.