Home → Troubleshooting → @RISK for Excel: Other Issues → Macro Doesn't Store Values to Workbook

8.33. Macro Doesn't Store Values to Workbook

Applies to: @RISK for Excel, all releases

I have set @RISK to run a macro at every iteration, and my macro code makes changes to the workbook. It seems that these changes do get made for some groups of iterations, but not for others. Why isn't my macro running for all iterations?

Do you have multiple CPU set to Enabled in Simulation Settings? In a multiple-CPU simulation, each CPU has its own copy of the workbook. The master CPU (the one with a visible Excel window) parcels out the work to the worker CPUs, and they send back random numbers for @RISK distributions in their assigned blocks of iterations. At the end of simulation, the worker copies of Excel are shut down, and if your VBA code made any changes they are lost.

If you need to generate numbers with VBA code during a simulation, and have them available after the simulation, here are some possibilities:

If the simulation is not too large, change Multiple CPU to Disabled. Then there won't be any extra copies of your workbook, and any changes your macros make will go into the only copy of the workbook.
If you need to generate values in VBA, change the macro to a user-defined function with a numeric result, and call it from a formula in your workbook. You can then make that cell an @RISK output. You could use RiskData( ) functions to store all iteration values in the workbook, though that may slow down larger simulations. A faster alternative is to create a Simulation Data report after the simulation.

Last edited: 2018-08-10

This page was: Helpful | Not Helpful