Home → Techniques and Tips → VBA Programming with @RISK → Using VBA to Change References to @RISK
Applies to:
@RISK 5.x–7.x, Professional and Industrial Editions
Evolver 5.x–7.x
NeuralTools 5.x–7.x
PrecisionTree 5.x–7.x
StatTools 5.x–7.x
We wrote a bunch of automation code for @RISK (Evolver, NeuralTools, PrecisionTree, or StatTools) release 5 or 6. Now we've upgraded to release 7, and all the references in all our workbooks need to be updated. Is there any kind of automated solution, or do we have to make a lot of mouse clicks in every single workbook?
For the problem at hand, you could write a macro to delete the obsolete references and add the new ones; see the references below. You could put that macro in a separate workbook, then have it available for people to run when references in @RISK model workbooks need to be updated. The problem is that there are two prerequisites for executing such code: you need to tick "Trust access to the VBA project object model" in Excel's Trust Center settings, and you need to set a reference to Microsoft Visual Basic for Applications Extensibility. These can't be done programmatically and must be done by hand.
There's significant risk with "Trust access to the VBA project object model". That lets workbook macro code do pretty much anything, and if you unknowingly download and open a malicious workbook you'll have a serious security breach on your hands. (See also Enable or disable macros in Office documents.) This kind of risk is one reason why we don't offer automatic code to adjust the references.
See also:
Are there any programming practices we can follow so that we're not in this position again, when we upgrade from version 7 to version 8?
We have a couple of suggestions. One possibility is late binding, where you don't have references set in the workbooks but instead connect to the @RISK (Evolver, ...) object model at run time. While this preserves maximum flexibility, you lose the benefit of Intellisense (auto-complete of properties, tool tips for function arguments, and so forth) during code development. To learn more about late binding, in your Palisade software click Help » Developer Kit (XDK) » Automation Guide. Look near the end for the topic "Demand Loading @RISK" ("Demand Loading Evolver", ...).
If the workbooks have mostly the same macro code, another possibility is to move your macros to one workbook. In effect, you write your own add-in to @RISK. Then when references have to be updated you can do it only once, and redistribute the updated workbook. If you have enough commonalities, this will also reduce your maintenance burden — if any kind of problem is discovered in macro code, it can be fixed once, with no need to try to find all the workbooks that contain the problem code.
Last edited: 2015-12-29