Microsoft KB Archive/95887

= Microsoft Knowledge Base =

Excel Err Msg: &quot;Macro Error at Cell ANALYSIS...&quot; with RANDOM
Last reviewed: September 12, 1996

Article ID: Q95887

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 4.0, 5.0

SUMMARY
In Microsoft Excel, you can use the RANDOM function in a macro to automate the Random Number Generation tool (available in the the Analysis ToolPak add-in macro). The RANDOM function has seven different syntax forms based on various distribution types. If you use cell references for the parameters of some of the arguments in the Uniform, Normal, Binomial, or Patterned distribution types, you will receive one of the following error messages:

Microsoft Excel for Windows, version 4.0, 4.0a
Macro Error at Cell ANALYSIS.XLA!B926

Microsoft Excel for Windows, version 5.0
Microsoft Excel Random Number Generation - Uniform bounds are required and must be numeric.

Microsoft Excel for the Macintosh, version 4.0
Macro Error at Cell ANALYSIS!B937 You cannot use cell references for the parameters of some the arguments in the following distribution types:

Uniform Distribution, Syntax 1 --

RANDOM(outrng, variables, points, distribution, seed, from, to)

You cannot use cell references in the parameters for the &quot;from&quot; and &quot;to&quot; arguments.

Normal Distribution, Syntax 2 -

RANDOM(outrng, variables, points, distribution, seed, mean,  standard_dev)

You cannot use cell references in the parameters for the &quot;mean&quot; and &quot;standard_dev&quot; arguments.

Binomial Distribution, Syntax 4 ---

RANDOM(outrng, variables, points, distribution, seed, probability,  trials)

You cannot use cell references in the parameters for the &quot;probability&quot; and &quot;trials&quot; arguments.

Patterned Distribution, Syntax 6

RANDOM(outrng, variables, points, distribution, seed, from, to,  step, repeat_num, repeat_seq)

You cannot use cell references in the parameters for the &quot;from&quot;, &quot;to&quot;, &quot;step&quot;, &quot;repeat_num&quot;, and &quot;repeat_seq&quot; arguments. Note: The parameters appear in the Parameters section of the Random Number Generation dialog box. To access this dialog box, choose Analysis Tools from the Options menu, select Random Number Generation, and choose the OK button.

Workaround
Although you cannot use cell references in the arguments specified above, you can use numbers or numbers that are defined as names.

To use cell references for the parameters of the arguments mentioned above, use the SET.NAME function to define the contents of a cell as a name and then use that name to reference the cell's contents. The following example macro uses the normal distribution syntax:

A1: =SET.NAME(&quot;mean&quot;,DEREF(!B2)) A2: =SET.NAME(&quot;std&quot;,DEREF(!B3)) A3: =RANDOM(!B1,1,1,2,1,mean,std) A4: =RETURN In the above macro, cells A1 and A2 use the DEREF function to obtain the contents of cells B2 and B3 on the active worksheet and set them equal to the names &quot;mean&quot; and &quot;std&quot;. Cell A3 uses the names defined in cells A1 and A2 (&quot;mean&quot; and &quot;std&quot;) to produce a result in cell B1 on the active worksheet. Cell A4 ends the macro.