Microsoft KB Archive/125949

= "Cannot Find" or "Compile Error" Running ATP Function =

Article ID: 125949

Article Last Modified on 8/15/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 for Macintosh

-



This article was previously published under Q125949



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

where FUNCTION is the name of the function that you used in the procedure.

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

When you run the macro a second time, you may receive the following error message

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.

The Methods/Properties list contains all of the Visual Basic functions included in the Analysis ToolPak - VBA add-in.

Additional query words: addins ATP XL

Keywords: kbprogramming kbprb kbcode KB125949

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.