Applies to: @RISK 5.0 and Up, Professional and Industrial Editions

How to generate a random sample in a VBA macro

Solution
Use the Risk.Sample method. This samples a value from the specified distribution using Monte Carlo sampling (not Latin Hypercube) and ignores RiskCorrmat and RiskSeed.

Basic numeric example

x = Risk.Sample("RiskBinomial(10,0.2)")

Using cell references

x = Risk.Sample("RiskBinomial(A1,B1)")
x = Risk.Sample("RiskBinomial('My Sheet'!A1,'My Sheet'!B1)")
x = Risk.Sample("RiskBinomial(BinomialN,BinomialP)")

• References must be in A1 format.

• You can specify the worksheet or use named ranges.

Sampling a RiskDiscrete distribution

x = Risk.Sample("RiskDiscrete('My Sheet'!A1:A10,'My Sheet'!B1:B10)")
x = Risk.Sample("RiskDiscrete(Xarray,Parray)")

• You can define names for the arrays (Xarray, Parray) and reference them in Risk.Sample.

Writing the sampled value to a worksheet

Range("B1").Value = Risk.Sample("RiskBinomial(A1,A2)")
Range("myKeyLocation").Value = Risk.Sample("RiskBinomial(A1,A2)")

• If the sample returns an error like #VALUE, it will be written to the cell but will not trigger a VBA error.

Requirements

• Set a reference to @RISK in the VBA editor: Tools → References.

• For advanced usage, see the @RISK Developer Kit (XDK) or Automation Guide (Help » Developer Kit (XDK) » Automation Guide).

 

Related Articles:

Latin Hypercube

Setting References in Visual Basic

Cell References in Distributions

Generating Values from a Distribution

Accessing Simulation Data in VBA Code