Excel: ADD.MENU Command Returns Error Message

SUMMARY

The error message "Cannot find Macro" or "Error in Formula" results when a custom command is chosen from a custom menu in Microsoft Excel if both of the following occur:

1. The macro that the custom command calls is included in the menu/command definition as an external reference (for example, "678.XLM!Test")

2. The filename in the external reference begins with a number, such as the filename 678.XLM.

MORE INFORMATION

Steps to Reproduce Problem

1. Enter the following into a macro sheet and save the file as 678.XLM.

         A1: Menus                B1: Newmenu         C1:
         A2: =ADD.MENU(1,B1:C2)   B2: Newcommand      C2: 678.XLM!HELLO
         A3: =RETURN()
         A4:
         A5: HELLO
         A6: =ALERT("HELLO",1)
         A7: =RETURN()
   
   

2. Select cell A5.

3. From the Formula menu, choose Define Name. Select the Command option and choose OK.

4. Select cell A1. From the Macro menu, choose Run. The Newmenu menu should now appear.

5. From Newmenu, choose Newcommand.

Workaround

When a worksheet name begins with a number, the filename must be enclosed in single quotes if it is to be used when referring to an external reference.

To correct the problem above, change the macro reference in cell C2 to read

C2: '678.XLM'!HELLO

REFERENCES

"Microsoft Excel User's Guide," version 3.0, pages 641-649