Microsoft KB Archive/97829

= Microsoft Knowledge Base =

Excel: ADD.COMMAND Command_ref Argument Causes Macro Error
Last reviewed: September 12, 1996

Article ID: Q97829

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, version 4.0

SYMPTOMS
In the Microsoft Excel versions listed above, you can use the ADD.COMMAND macro function to restore a previously deleted built-in or custom command to a menu. The ADD.COMMAND function will halt your macro in error if you are trying to add a previously deleted built-in command AND one of the following is true:

 You set the DELETE.COMMAND formula to a name and then use this name as the command_ref argument in ADD.COMMAND. -or- You use the actual unique ID as the command_ref argument instead of referencing the DELETE.COMMAND cell. -or- When you restore commands to shortcut menus, you use the menu name as text for the command_ref argument.

STATUS
Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed above. We are researching this problem, and will post more information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
When a built-in command is deleted with the DELETE.COMMAND function, the function returns a unique ID for the built-in command. This unique ID can be used in the command_ref argument of the ADD.COMMAND function to restore the built-in command.

The command_ref argument must be the reference to the cell containing the DELETE.COMMAND function when restoring a command to one of the shortcut menus: that is bar_num 7, 8, or 9. For all other menus, the command_ref argument must be EITHER the reference to the cell that contains the DELETE.COMMAND OR the text string for the built-in command. If the command_ref argument is anything else, Microsoft Excel will halt the macro and you will receive the following error message

Macro error at cell:  where  is the reference to the cell containing the ADD.COMMAND function.

The syntax of ADD.COMMAND is as follows:

ADD.COMMAND(bar_num,menu,command_ref,position)

ADD.COMMAND Macros to Restore Built-in Commands
The following macro uses the ADD.COMMAND function to restore built-in commands to all menu bars, numbers 1-9. Note that this is the ONLY method you can use to restore a built-in command to a shortcut menu, bar numbers 7-9:

A1: =DELETE.COMMAND(7,8,&quot;Cut&quot;) A2: =ADD.COMMAND(7,8,A1,1) A3: =RETURN This macro uses the deleted command as text to restore the command.

The following macro will work with menu bar numbers 1-6:

A1: =DELETE.COMMAND(1,&quot;File&quot;,&quot;Open...&quot;) A2: =ADD.COMMAND(1,&quot;File&quot;,&quot;Open...&quot;,&quot;Close&quot;) A3: =RETURN

ADD.COMMAND Macros that Do NOT Work
The following macro syntax will not work for all menu bar numbers 1-9:

A1: =DELETE.COMMAND(1,&quot;File&quot;,&quot;Open...&quot;) A2: =ADD.COMMAND(1,&quot;File&quot;,1,&quot;Close&quot;) A3: =RETURN

-and-

A1: =SET.NAME(&quot;var&quot;,DELETE.COMMAND(1,&quot;File&quot;,&quot;Open...&quot;)  A2: =ADD.COMMAND(1,&quot;File&quot;,var,&quot;Close&quot;)   A3: =RETURN The following macro syntax will not work for shortcut menus only, bar numbers 7-9:

A1: =DELETE.COMMAND(7,8,1) A2: =ADD.COMMAND(7,8,&quot;Cut&quot;,1) A3: =RETURN The first macro uses the unique ID number for the Open command. The second macro uses a defined name for the unique ID number. The third macro, which applies only to shortcut menus, uses the command name as text. All of these macros will fail.