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

• 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.

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

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:

1. 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()
   
   

2. 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.

3. From the File menu, choose New. Select Worksheet, and choose the OK button.

4. 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.

5. Select cell D1 on the worksheet and repeat Step 4.

REFERENCES

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