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

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

REFERENCES

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