Users can create Microsoft Excel VBA event macros for workbooks. Depending on the event selected, Crystal Ball runs them automatically at various points in a simulation or optimization. The original simulation-specific macros are described in Chapter 4 of the Crystal Ball User Manual.
Beginning with Crystal Ball 11.1.1.0.00, macros based on VBA events are now available to run at various points during an OptQuest optimization. Table 200, following, summarizes events that can be defined with VBA code and describes when they run.
Note: | Event macros must be attached to worksheets or workbooks and not modules. |
Table 200. Events Accessible through VBA and When They Run within an Optimization
Event | Type | Runs... |
|---|---|---|
BeforeOptimizationEvent | Optimization | At the start of an optimization, when the OptQuest Run command is given or an equivalent command is given using the OptQuest Developer Kit. |
BeforeIterationEvent | Optimization | At the start of each iteration of an optimization, just after the decision variable values for that iteration have been written to the workbook, but before any nonlinear constraints have been evaluated by OptQuest. |
BeforeSimulationEvent | Simulation | On each iteration of an optimization just before the simulation begins. At this point, nonlinear constraints have been evaluated by OptQuest. |
AfterSimulationEvent | Simulation | On each iteration of an optimization, just after the simulation completes. Any extracted data values will have been written to the workbook as part of the simulation processing. Forecast objective and requirement values have not yet been updated to OptQuest. Note: at this point any solution values for this iteration are not yet valid. |
AfterIterationEvent | Optimization | After each iteration of an optimization. The simulation is complete, any extract data has been written to the workbook and objective and any requirement values have been updated to OptQuest. Note: at this point all solution values for this iteration are valid. |
AfterOptimizationEvent | Optimization | After an optimization is complete. |
EfficientFrontierEvent | Optimization | After each test point in an Efficient Frontier optimization is complete. |
The BeforeOptimizationEvent macro runs at the beginning of the optimization, when the Run button is first clicked or the Run command is given. After that, the Simulation and Iteration event macros take over and run as described until all requested trials and simulations have been run (or a stopping point set in the Advanced Options dialog has been reached). At that point, the optimization is complete and the AfterOptimizationEvent macro runs.