Analytics Articles

Sequential Simulations using Crystal Ball VBA

Eric Torkia, MASc

Share:

Print

Rate article:

4.0
Rate this article:
4.0

If your objective is to feed one simulation with the results of another, VBA is the way to go. Working on a client assignment, we took the results from a first simulation to configure the parameters of the second. Of course VBA is not the only way, their is also the manual approach.

If all your simulations are in the same workbook, one of the first things that you need to manage is how you will isolate your numbers. For this reason I personally like working with straight values rather than formulas. In crystal ball you can auto-extract most parameters (e.g. mean, median, kurtosis, percentiles, etc.)  from a forecast right into your worksheet.

Alternatively, you can use the CB.GetForeData, CB.GetForePercent, CB.GetForeStat formulas to get the information you need from the first simulation and use copy/paste special - values. In either case we are working with values.

What would happen if we used the formulas only? When we would run the second simulation, the parameters would change as the simulation would run, thus potentially skewing the results.

Ok, so what are the best practices to get this up and running?

  1. Define range names for the cells that contain the number of Trials for both Sim 1 and Sim 2
  2. Define range names for all the assumptions and forecasts data that you want to move around - both source and target ranges
  3. Run your first simulation and copy the values to the range/worksheet containing the second simulation. Keep in mind that this step is what freezes your second simulations inputs. This you can code with VBA (as I did below) or you can copy and paste the values only before running your second simlation
  4. The code below allows to set the trials in the worksheet itself without having to change the run preferences each time you want to run a simulation
  5. Do not forget to set the visibility of the forecasts you want and those you do not as they will appear when you launch the simulation
  6. Use form objects such as buttons to launch the code

Sub RunSimulation()
' Obtain inputs for Simulation 2 by running the simulation 1
'Set Trials for 1st sim
    Dim Trial
    Trials = Range("Trials_Sim1").Value
    MsgBox "Number of Trials for Simulation 1: " & Trials

 

'Setup Crystal Ball by resetting the simulation, setting Extreme Speed in the Run Prefs, run trials f while suppressing charts
    CB.ResetND
    CB.RunPrefsND cbRunMode, cbRunExtremeSpeed
    CB.Simulation Trials, , True, False, False, "Running Probabilistic Reserve Analysis", True

'Obtain final forecast by running the second simulation
    CB.ResetND
    copy_parameters
    Trials = Range("Trials_Sim2").Value
    MsgBox "Number of Trials for Simulation 2 Analysis: " & Trials
    CB.RunPrefsND cbRunMode, cbRunExtremeSpeed
    CB.Simulation Trials, , False, False, False, "Running Sim 2 Analysis", True

End Sub

Sub copy_parameters()
'This routine is to copy the probability parameters for the second simulation

Range("Sim1_Result_1").Copy
Application.Goto Reference:="Sim2_Input_1"
Range("Sim2_Input_1").Select
ActiveSheet.Paste
Range("Sim1_Result_2").Copy
Application.Goto Reference:="Sim1_Result_2"
Range("Sim2_Input_2").Select
ActiveSheet.Paste

 End sub

Hopefully, with this script template you will be able to get a sequential set of sim,ulations going in no time.... if you have any questions on how to apply this in your projects, please don't hesitate to drop me a line at [email protected]

Comments

Collapse Expand Comments (0)
You don't have permission to post comments.

Introduction to Monte Carlo Analysis (Part 10 / 13)

In past blogs, I have waxed eloquent about two traditional methods of performing Tolerance Analysis, the Worst Case Analysis and the Root Sum Squares. With the advent of ever-more-powerful processors and the increasing importance engineering organizations place on transfer functions, the next logical step is to use these resources and predict system variation with Monte Carlo Analysis.

Root Sum Squares Explained Graphically (Part 8 / 13)

Sep 21 2010
9
0

A few posts ago, I explained the nature of transfer functions and response surfaces and how they impact variational studies when non-linearities are concerned. Now that we have the context of the RSS equations in hand, let us examine the behavior of transfer functions more thoroughly.

Tolerance Analysis using Root Sum Squares Approach, continued (Part 7 / 13)

Sep 01 2010
62
0

As stated before, the first derivative of the transfer function with respect to a particular input quantifies how sensitive the output is to that input. However, it is important to recognize that Sensitivity does not equal Sensitivity Contribution. To assign a percentage variation contribution from any one input, one must look towards the RSS output variance (σY2) equation:

Tolerance Analysis using Root Sum Squares Approach (Part 6 / 13)

Aug 30 2010
60
0

Root Sum Squares (RSS) approach to Tolerance Analysis has solid a foundation in capturing the effects of variation. In the days of the golden abacus, there were no super-fast processors willing to calculate the multiple output possibilities in a matter of seconds (as can be done with Monte Carlo simulators on our laptops). It has its merits and faults but is generally a good approach to predicting output variation when the responses are fairly linear and input variation approaches normality. That is the case for plenty of Tolerance Analysis dimensional responses so we will utilize this method on our non-linear case of the one-way clutch.

Transfer Functions & Response Surfaces in Tolerance Analysis (Part 5 / 13)

Aug 23 2010
12
0

Transfer Functions (or Response Equations) are useful to understand the "wherefores" of your system outputs. The danger with a good many is that they are not accurate. ("All models are wrong, some are useful.") Thankfully, the very nature of Tolerance Analysis variables (dimensions) makes the models considered here concrete and accurate enough. We can tinker with their input values (both nominals and variance) and determine what quality levels may be achieved with our system when judged against spec limits. That is some powerful stuff!

Probability Distributions in Tolerance Analysis (Part 4 / 13)

With uncertainty and risk lurking around every corner, it is incumbent on us to account for it in our forward business projections, whether those predictions are financially-based or engineering-centric. For the design engineer, he may be expressing dimensional variance in terms of a tolerance around his nominal dimensions. But what does this mean? Does a simple range between upper and lower values accurately describe the variation?

Tolerance Analysis using Worst Case Approach, continued (Part 3 / 13)

Aug 16 2010
74
0

In my last couple of posts, I provided an introduction into the topic of Tolerance Analysis, relaying its importance in doing upfront homework before making physical products. I demonstrated the WCA method for calculating extreme gap value possibilities. Implicit in the underlying calculations was a transfer function (or mathematical relationship) between the system inputs and the output, between the independent variables and the dependent variable. In order to describe the other two methods of allocating tolerances, it is necessary to define and understand the underlying transfer functions.

Tolerance Analysis using Worst Case Approach (Part 2 / 13)

Aug 12 2010
265
0

As stated in my last post, there are three common approaches to performing Tolerance Analysis. Let us describe the simplest of the three, the Worst Case Analysis (WCA) approach. An engineering-centric term in the Tolerance Analysis world would be Tolerance Stacks, usually meaning in a one-dimensional sense. The explanation begins with probably the most overworked example found in dusty tomes (my apologies in advance).

(I would like acknowledge James Ministrelli, DFSS Master Black Belt and GD&T Guru Extraordinaire, for his help & advice in these posts. Thanks, Jim!)

RESEARCH ARTICLES | RISK + CRYSTAL BALL + ANALYTICS