XL5: Run-time Error 1004 or 1006 Using ATP Worksheet Function

ID: Q119001

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you use one of the Analysis ToolPak worksheet functions, one of the following occurs:

You receive either of the following error messages:

Run-time error '1004': Run Method of Application class failed

-OR-

-time error '1006': Unable to get the ThisWorkbook property of the Application class

-or-

The function returns the #VALUE! error value.

CAUSE

These symptoms occur if the workbook that contains the function also contains a module sheet that references the Analysis ToolPak -- Visual Basic for Applications add-in file, ATPVBAEN.XLA.

The error messages occur when you use one of the following worksheet functions:


   EDATE()
   EOMONTH()
   IMSUM()
   NOMINAL()
   TBILLEQ()
   TBILLPRICE()
   TBILLYIELD()

You receive the run-time error '1004' if the Analysis ToolPak-Visual Basic for Applications add-in is also loaded (selected in the Add-ins dialog box). If the Analysis ToolPak-Visual Basic for Applications is not loaded, you receive the run-time error '1006.'

Note that the functions IMSUM() and NOMINAL() return the #VALUE! error value if the Analysis ToolPak--VBA add-in is not loaded.

The following functions return the #VALUE! error value whether the Analysis ToolPak-Visual Basic for Applications add-in is loaded or not:


   GCD()
   LCM()
   MULTINOMIAL()
   IMPRODUCT()
   CUMIPMT()
   CUMPRINC()

Note that this behavior also occurs if you use the FunctionWizard to insert the function.

WORKAROUND

To avoid receiving these error messages when you use one of the Analysis ToolPak worksheet functions, include the reference to the add-in file with the function name as in the following examples:


   =ANALYSF.XLA!EDATE("1/1/94",5)

   =ANALYSF.XLA!NOMINAL("1/1/94",5)

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

MORE INFORMATION

For additional information, please see the following article(s) in the Microsoft Knowledge Base:

Q118797 : "Cannot Find" Using Analysis ToolPak Visual Basic Function

Q118799 : XL5: "Argument Not Optional" Using Visual Basic ATP Functions

Additional query words: addins atp err msg toolpack

Keywords : xlwin
Version : 5.00
Platform : WINDOWS
Issue type :