Microsoft KB Archive/96636

{|
 * width="100%"|

Excel Err Msg: &quot;Error Locating Support File...&quot; with RANDOM

 * }

-

The information in this article applies to:


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

-

SUMMARY
In Microsoft Excel, you can use the RANDOM function (available in the Analysis ToolPak add-in macro), to generate a set of random numbers. When you use this function in a macro, if you leave any of the optional arguments (variables, points, or seed) blank, you will receive the following error message:

An error has occurred locating a support file or processing data for an add-in procedure.

MORE INFORMATION
Using the RANDOM function in a macro produces the same results as choosing Analysis Tools from the Options menu and selecting Random Number Generation from the list of available tools. Using the Options menu method, it is not necessary to specify a value in the Random Seed box. Microsoft Excel still generates a set of random numbers.

In the RANDOM function, the variables argument specifies the number of columns in the output range, the points argument specifies the number of rows in the output range, and the seed argument specifies an offset value used in determining which random numbers to retrieve. You must specify a value for each of these arguments when you use RANDOM in a macro.

Workaround
To work around this problem, specify the three following arguments for the RANDOM function:

  For this argument Use this value -

Variables     COLUMNS(range) Points     ROWS(range) Seed    INT(RAND*number) or any positive integer.

The resulting formula will be

  =RANDOM(cell,COLUMNS(range),ROWS(range),1,INT(RAND*number),10,20)

where &quot;cell&quot; is the upper left cell in the output range, range is the reference or defined name of the area on the worksheet where you want to place the random numbers, and number is the largest possible number you want to use as a seed value.

Note: Using this formula, or a seed of 0, produces a different set of numbers each time you run the macro; using a constant such as 10 produces the same set of numbers each time you run the macro.

The following sample macro demonstrates the use of the RANDOM

function using 5 and INT(RAND*10) for the seed argument:

  Enter the following information in your macro sheet:

  A1: Test A2: =RANDOM(OFFSET(ACTIVE.CELL,0,0,5,2),2,5,1,5,10,20) A3: =RANDOM(OFFSET(ACTIVE.CELL,6,0,5,2),2,5,1,       INT(RAND*10),10,20) A4: =RETURN  Select cell A1 and choose Define Name from the Formula menu. The word &quot;Test&quot; will appear in the Name box, and $A$1 will appear in the the Refers to box. Select the Command option in the Macro section, and choose the OK button. From the File menu, choose New. Select Worksheet, and choose the OK button. With cell A1 selected, choose Run from the Macro menu. Select the Test macro and choose the OK button. The macro will generate two groups of random numbers. Select cell D1 on the worksheet and repeat Step 4.

Notice that the numbers in the top group in both cases are the same while the numbers in the bottom group are different. This is a result of using a constant versus a 0 or the RAND function as the Seed argument.