Macros Settings

The Macros settings tab (Figure 1 and 2, below) allows for the configuration to either run macros during a simulation or to run an Excel Tool during each iteration of a simulation. There are two layouts of the Macros tab, each with its own set of options - one for VBA Macros (Figure 1) and another for Excel Tools (Figure 2).

To switch between the two views of the Macros tab, select the appropriate option - 'VBA Macros' or 'Excel Tool'.

The options include:

  • VBA Macros - Run a custom macro written in VBA (Visual Basic for Applications, Microsoft Office's programming language).
  • Excel Tool - Launch an Excel tool such as Goal Seek or Evolver.

  • Please note: It is not possible to run both a VBA Macro and an Excel Tool during the same simulation run.

VBA Macros

Figure 1 - Simulation Settings - Macros Tab, VBA Macros Settings

This option enables macros to be executed during an @RISK simulation, including options to run macros before, during, or after iterations and/or simulations. Running macros during a simulation allows for calculations that can only be performed through the use of macros, such as optimizations, iterative or "looping" calculations, or calculations that require data from an external source. Additionally, an Excel macro can includ other @RISK distribution functions that will be sampled during the execution of the macro.

The options include:

  • Run an Excel Macro
    • Before Each Simulation - The selected macro will run before each simulation begins.

    • Please note: if a simulation run is configured to include multiple simulations, the selected macro will run before each simulation in the run.

    • Before Each Iteration's Recalc - The selected macro runs before @RISK places new sample values from distribution functions into the model, and before any other calculations or Excel functions using the sample values are recalculated.
    • After Each Iteration's Recalc - The selected macro runs after @RISK performs sampling and worksheet recalculation, but before @RISK stores values for Outputs.
    • After Each Simulation - The selected macro will run after each simulation ends.

    • Please note: if a simulation run is configured to include multiple simulations, the selected macro will run after each simulation in the run.

  • If Excel Recalculations Occur during Macros, Distributions Return - this option controls how @RISK distribution functions handle Excel recalculations that occur inside macros.
    • Different Samples Each Recalculation - @RISK functions will return different samples during each recalculation within the macro.
    • Fixed Samples - @RISK functions will return the sample that was drawn in the iteration that the macro is run.

To run a macro during a simulation, check the box for step in which the macro should run. Enter the fully qualified name of the macro, including the full address with filename.

@RISK includes an object oriented Application Programming Interface (API) that allows custom applications to be built on top of @RISK functionality. For very complicated models, or those that either require or benefit from a high level of automation, the use of the @RISK API may be a better solution than using macros. See the @RISK Developer Kit (XDK) documentation for more information.

Excel Tool

Figure 2 - Simulation Settings - Macros Tab, Excel Tool Settings

This option enables a model to utilize an Excel tool such as Goal Seek or Evolver during each iteration of a simulation. Typically, an Excel Tool is used when the values sampled by @RISK should be used in a goal seek or optimization model. During each iteration, @RISK will run the selected tool and incorporate the calculated results from the tool into the simulation outputs.

Three Excel tools are available to call during a simulation:

  • Goal Seek - Excel's built-in goal seek tool
  • Evolver - Palisade's optimization tool (a part of the DecisionTools Suite)
  • Solver - Excel's built-in optimization tool

The options in the lower half of the Macros tab will change based on the Excel Tool that is selected; Goal Seek has options that are configured through the Macros tab, whereas Evolver and Solver are both configured elsewhere.

The options available for Goal Seek are the same options as those set through the native Excel Goal Seek configuration:

  • Set Cell - The cell that contains the output for which a target value (the goal) is known.
  • To Value - The target value for the selected output.
  • By Changing Cell - The cell whose value will be modified until it results in the target value is found.