Excel Err Msg: "Error Locating Support File..." with RANDOM()

ID: Q96636

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 "cell" 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 "Test" 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.

REFERENCES

"Online Help," version 5.0
"Function Reference," version 4.0, pages 345-346
"User's Guide 2," version 4.0, pages 38-40

Additional query words: 4.00a optional argument error random analysis tool pack pak

Keywords :
Version : 4.00 4.00a 5.00
Platform : WINDOWS
Issue type :