HomeTechniques and TipsVBA Programming with @RISKDistribution Functions as Arguments to User-Written Functions

10.8. Distribution Functions as Arguments to User-Written Functions

Applies to: @RISK 5.x and newer

I have written a function in Visual Basic code, and I use that function in formulas in my Excel sheet. When a simulation is running the function seems to work, but when a simulation is not running my worksheet displays #VALUE. What is wrong?

In @RISK 5.0 and above, during a simulation the @RISK distribution functions return a single number of type Double. But when a simulation isn't running, the @RISK distribution functions return an array, of which the first element is the random number drawn by the function. (This change from 4.x was made to support the RiskTheo statistics functions, among other reasons.)

Therefore, your own function needs to declare the argument as a Variant, not a Double, and it needs to test the type of the argument at run time. Please see the accompanying example.

Last edited: 2015-08-12

Downloads

This page was: Helpful | Not Helpful