Volatile Functions and Array Arguments

User-defined functions whose arguments are static (their values do not change during a simulation) are not called by Extreme Speed unless the Volatile property of the function has been set.

When a user-defined function is encountered in Extreme Speed, Crystal Ball first checks to see if the function is Volatile. If Crystal Ball is denied access to the VBA project and the user-defined function is not passing array or cell range arguments, Crystal Ball treats the function as Volatile.

If access to the project is denied and the call is passing array or cell range arguments, the following message is displayed:

Cannot interpret a user-defined function with array arguments. You must first check the ‘Trust Access to Visual Basic Project’ checkbox in Microsoft Excel’s macro security settings dialog. See Appendix C in the User's Guide for more information about this error.

Cause: The Microsoft Excel Volatile property has not been set.

  In that case, in order for Extreme Speed to determine the state of the Volatile property in Microsoft Excel 2003, you must first make the following setting in Microsoft Excel:

  1. Go to the Tools, then Options panel.

  2. Click the Security tab.

  3. Click the Macro Security button.

  4. Click the Trusted Publishers tab.

  5. Check Trust Access To Visual Basic Project.

In Microsoft Excel 2007 or later, you should not encounter this specific problem with array arguments or any others involving the Analysis ToolPak, since the ToolPak is embedded directly into Microsoft Excel 2007 or later.

  However, to set the "Trust access..." property in Microsoft Excel 2007 or later to ensure that the Volatile property is set correctly, follow these steps:

  1. Click the Office button.

  2. Click the following sequence of buttons and links: Microsoft Excel Options, then Trust Center, then Trust Center Settings, then Macro Settings.

  3. On the Macro Settings page, under Developer Macro Settings, select Trust access to the VBA project object model.