Home → Techniques and Tips → VBA Programming with @RISK → Sampling @RISK Distributions in VBA Code
Applies to: @RISK 5.0 and newer, Professional and Industrial Editions
How can I generate a random sample within a VBA macro or function?
Use the Sample method with the Risk object. Here's an example:
x = Risk.Sample("RiskBinomial(10,0.2)")
The Sample method normally returns a numeric value, but if there's an error in the definition of the distribution then the method returns an error variant in the usual way for Excel.
The sampled values are not the same numbers you would see from that function in a worksheet. They always use the Monte Carlo method, as opposed to Latin Hypercube; RiskCorrmat and RiskSeed are ignored. If you want to access simulation data, use members of the Risk.Simulation.Results object after the simulation finishes.
To call @RISK functions from Visual Basic, you must set up a reference from Visual Basic Editor to @RISK via Tools » References in the editor. Setting References in Visual Basic gives the appropriate reference(s) and how to set them.
Please see the XDK or Developer Kit manual for details on the objects and methods mentioned in this article, as well as alternative methods. (Beginning with @RISK 6.2, start with the Automation Guide for a high-level introduction: Help » Developer Kit (XDK) » Automation Guide.)
Am I restricted to just numeric arguments, or can I use cell references?
Yes, you can use cell references:
x = Risk.Sample("RiskBinomial(A1,B1)")
The cell references must be in A1 format, not R1C1, and they are taken to refer to the active worksheet. If you don't want to worry about which sheet is active, specify the worksheet or use defined names:
x = Risk.Sample("RiskBinomial('My Sheet'!A1,'My Sheet'!B1)")
x = Risk.Sample("RiskBinomial(BinomialN,BinomialP)")
I want to sample a RiskDiscrete with a long list of x and p. How can I use cell references?
It follows the pattern of Cell References in Distributions. Here's an example:
x = Risk.Sample("RiskDiscrete('My Sheet'!A1:A10,'My Sheet'!B1:B10)")
As an alternative, in the worksheet you can define names for the arrays, and then use the names in the Risk.Sample function:
x = Risk.Sample("RiskDiscrete(Xarray,Parray)")
Can I write the sampled value to my workbook?
Yes, just use Excel's Value property. You can apply it to a specific cell or to a defined range name:
Range("B1").Value = Risk.Sample("RiskBinomial(A1,A2)")
Range("myKeyLocation").Value = Risk.Sample("RiskBinomial(A1,A2)")
If the Risk.Sample method returns an error such as #VALUE, that will be written to the worksheet. This will not register as a VBA error that interrupts execution of your macro.
See also:
Last edited: 2018-02-28