XL: "Cannot Find" or "Compile Error" Running ATP Function

• Microsoft Excel for Windows, version 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS

In Microsoft Excel, when you run a Visual Basic procedure that contains an Analysis ToolPak function, you may receive the following error message

   Run-time error '1004':

   Cannot find macro '[ATPVBAEN.XLS]REG!reg_FUNCTION'.

In Microsoft Excel for the Macintosh, the first time you run the procedure that contains an Analysis ToolPak function, you may receive the following error message:

   Compile error in hidden module: VBA Functions and Subs

   Run-time error '1004':

   Cannot find macro '[ATPVBAEN.XLS]REG!reg_FUNCTION'.

CAUSE

This problem occurs if the Analysis ToolPak-Visual Basic for Applications add-in file (ATPVBAEN.XLA in Windows, Analysis ToolPak-Visual Basic for Applications on the Macintosh) is not open when you run the procedure that contains the Analysis ToolPak function.

Normally, when you reference an add-in file (or a workbook that contains a module) from another Visual Basic module, you can call a procedure or function in the referenced file even if that file is not open. However, when you reference the Analysis ToolPak-Visual Basic for Applications add-in file from a Visual Basic module that contains the Analysis ToolPak function, you receive the error messages described above if the Analysis ToolPak - Visual Basic for Applications file is not open when you run a procedure that calls one of the Analysis ToolPak functions.

WORKAROUND

To work around this behavior, use either of the following methods.

Method 1

Open the Analysis ToolPak-Visual Basic for Applications add-in file before you run a procedure that contains an Analysis ToolPak Visual Basic for Applications function. In Microsoft Excel for Windows, open ATPVBAEN.XLA located in the LIBRARY\ANALYSIS subdirectory of the Microsoft Excel directory. In Microsoft Excel for the Macintosh, open the Analysis ToolPak- Visual Basic for Applications file located in the Microsoft Excel:Macro Library:Analysis Tools folder.

Method 2

Load the Analysis ToolPak-Visual Basic for Applications add-in before you run a procedure that contains an Analysis ToolPak - Visual Basic for Applications function by using these steps:

1. On the Tools menu, click Add-Ins.

2. In the Available Add-Ins list, select the Analysis ToolPak - VBA check box, and click OK.

   Note that when you use Method 2, the Analysis ToolPak-Visual Basic for Applications add-in file is loaded each time you run Microsoft Excel.

MORE INFORMATION

To view the Visual Basic functions that are included in the Analysis ToolPakVisual Basic for Applications add-in, use the following appropriate steps.

Microsoft Excel for Windows

1. Open the file ATPVBAEN.XLA (located in the LIBRARY\ANALYSIS subdirectory of the Microsoft Excel directory).

2. In a Visual Basic module, click Object Browser on the View menu.

3. In the Libraries/Workbooks list, click ATPVBAEN.XLA.

Microsoft Excel for the Macintosh

1. Open the file Analysis ToolPak - VBA, which is located in the Microsoft Excel:Macro Library:Analysis Tools folder.

2. In a Visual Basic module, click Object Browser on the View menu.

3. In the Libraries/Workbooks list, click Analysis ToolPak - VBA.