Home → Techniques and Tips → VBA Programming with @RISK → Solver and Other Excel Recalculations within Your Macros
Applies to: @RISK For Excel, all releases
I have a macro set to run between iterations, on the Macros tab of @RISK Simulation Settings. That macro uses Excel Solver or does something else that triggers an Excel recalculation, but then the @RISK functions all get resampled. How can I hold the @RISK functions constant while my macro runs?
Solution (beginning with @RISK 7.5):
You may be able to use a point-and-click interface in Simulation Settings instead of writing VBA code. On the Macros tab of Simulation Settings, select "Excel Tool" instead of "VBA Macros". This will let you choose to run Excel Goal Seek, Excel Solver, or Palisade Evolver during each iteration. For Evolver or Solver, you have to set up the model in advance, but for Goal Seek you can enter the settings right in the Macros tab. If you prefer, or if your situation is more complicated, you can still use VBA and set the recalculation option, as described in the next section.
Solution (beginning with @RISK 6.2):
There's an important option in Simulation Settings. If you have any macros that trigger Excel recalculations, and those macros get executed during a simulation, open Simulation Settings and on the Macros tab select the option "If Excel Recalculations Occur during Macros, Distributions Return" » "Fixed Samples". Click OK and save the workbook. @RISK will remember this setting with all the other simulation settings in this workbook.
See the attached example KB187 for newer @RISK.
Caution: When you select Fixed Samples, @RISK will return the same value from a given distribution function every time it is called within one iteration — provided that your macro code doesn't change the parameters of the distribution. If the macro does change the argument values for an @RISK distribution function, @RISK will return a new sample for that distribution function.
Solution (@RISK 6.1 and earlier):
This is exactly the requirement: The @RISK functions must not change their values during the extra worksheet recalculations. If the @RISK functions resample, the model will not remain static during the processing of the macro, and Solver will be trying to hit a moving target. To demonstrate this problem, try running Excel Solver on a model where the objective calculation is dependent on a "=RAND()" Excel function. You will see that the objective calculation is a moving target and the Excel Solver optimization will not converge on an optimum solution.
To prevent @RISK functions from resampling when your macro triggers a recalc, ensure that no @RISK functions are precedents of any cells that are affected by your Excel Solver optimization. For example, suppose you have an a @RISK function in cell A145. None of your Excel formulas will reference A145. Instead, you establish a second cell, say A146, and all your formulas reference A146. When your macro runs, it will read the value of the @RISK function from cell A145 and write that as a plain numeric value (not a formula) to cell A146. This technique takes the @RISK functions out of Excel's precedent tracing, so that Excel doesn't call those functions when it does a worksheet recalculation.
Please have a look at the attached example, KB187 for older @RISK. In this example, there is a macro called "PlaceSampleAndRunSolver", which first places the static copy of the @RISK function sample into the model and then starts the Excel Solver optimization. The following events occur with each iteration:
Try running a simulation. The resulting population will be a distribution of optimal results.
Last edited: 2016-07-12