Applies to: @RISK For Excel 6.2 and Up

 

How can I hold the @RISK functions constant while my macro runs?

If your VBA macro triggers Excel recalculations while a simulation is running, @RISK distributions may resample unless you tell @RISK to hold them constant.

1. To do this, open Simulation Settings
2. Go to the Macros tab
3. Set “If Excel Recalculations Occur during Macros, Distributions Return” to “Fixed Samples”.
4. Click OK and save the workbook.

@RISK will remember this setting with the model.

With Fixed Samples selected, each @RISK distribution returns the same value every time it is called within a single iteration, as long as your macro does not change the distribution’s parameters.

• If your macro changes any arguments of a distribution, @RISK will generate a new sample for that distribution.

If you prefer not to write VBA, you can use the point-and-click option in Simulation Settings. This option is available starting in @RISK 7.5.

1. On the Macros tab, choose “Excel Tool” instead of “VBA Macros”.
   1. This allows you to run Excel Goal Seek, Excel Solver, or Evolver during each iteration.
   2. For Solver or Evolver, the model must already be set up
   3. For Goal Seek, you can define the settings directly on the Macros tab.

Be aware that Smart Sensitivity Analysis is not available when macros run, because @RISK cannot trace precedents through macro execution.

• If you want tornado graphs or other sensitivity results, disable Smart Sensitivity Analysis on the Sampling tab of Simulation Settings.
  • When it is disabled, some sensitivity results may include inputs that do not actually affect the output, so you should manually filter those out.

 

Attachments:

@RISK using VBA Macros

@RISK using Excel Tools