Excel: Formulas from FILEFNS.XLA Add-In Return #VALUE!

Last reviewed: November 30, 1994
Article ID: Q79184
The information in this article applies to:
  • Microsoft Excel for Windows versions 3.0, 4.0
  • Microsoft Excel for OS/2, version 3.0
  • Microsoft Excel for the Macintosh, version 3.0

SUMMARY

The macro functions provided by Microsoft Excel's FILEFNS.XLA may return #VALUE! errors if you open FILEFNS.XLA from a macro sheet using the OPEN macro command. FILEFNS.XLA, like the other Library Macros, runs an Auto_Open macro upon loading the file. When a document that runs an auto- open macro is itself opened by a macro, the auto-open macro will not run.

MORE INFORMATION

The FILEFNS.XLA is a file functions macro that gives you four additional macro functions to create, delete, and get information about directories.

The Auto_Open macro in FILEFNS.XLA consists of several REGISTER statements that link to a dynamic-link library (FILEFNS.DLL). These are then referenced by CALL functions when one of the new macro functions is used.

If the Auto_Open macro is not run when FILEFNS.XLA is loaded, the REGISTER functions do not execute. As a result, if one of the CALL statements is then executed by using one of the new macro functions, the function will return a #VALUE! error.

Note: The functions are still added to the dialog box that appears when you choose Paste Functions from the Formula menu. This is because the functions are added to the menu as a functionality of add-in macros, not by the Auto_Open macro.

Workaround

To run an Auto_Open macro called on a sheet that was loaded by another macro, use the RUN macro function or call the macro directly as in the example below. The following macro will open FILEFNS.XLA and run the Auto_Open macro.

  1. Enter the following into a macro sheet:

          A1: =OPEN("C:\EXCEL\LIBRARY\FILEFNS.XLA")
          A2: =FILEFNS.XLA!Auto_Open()
          A3: =FILE.EXISTS("C:\EXCEL\EXCEL.EXE")
          A4: =RETURN()
    

  2. After running the above macro, cell A3 returns TRUE or FALSE.

Steps to Reproduce Problem

  1. Enter the following into a macro sheet:

          A1: =OPEN("C:\EXCEL\LIBRARY\FILEFNS.XLA")
          A2: =FILE.EXISTS("C:\EXCEL\EXCEL.EXE")
          A3: =RETURN()
    

  2. After running the above macro, cell A2 will return #VALUE!.

Note: The information described in this article applies to any macro sheets that have Auto_Open macros that link to DLLs, such as Solver or Q+E.

REFERENCES

"Microsoft Excel User's Guide," version 3.0, pages 622, 679-680


KBCategory: kbusage
KBSubcategory:

Additional reference words: CREATE.DIRECTORY DELETE.DIRECTORY
DIRECTORIES 3.0 3.00 4.0 4.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.