Excel: 'Cannot Find Macro <macro name>' With Add-in Button

• Microsoft Excel for Windows, version 4.0
• Microsoft Excel for the Macintosh, version 4.0

Summary:

Buttons assigned to add-in macro sheets in Microsoft Excel version 3.0 may not function properly when converted to Microsoft Excel version 4.0. This may result in the error message:

   Cannot find macro <macro name>.

More Information:

In Microsoft Excel 3.0, buttons assigned to add-in macro sheets could utilize the name of the macro only, without referring explicitly to an add-in file. Macro assignments that did refer to an add-in sheet would be abbreviated to only display the defined name of the macro assigned to the object when the corresponding add-in sheet was loaded.

Microsoft Excel 4.0, however, creates a true link between every object and any macro that is assigned to it. This allows Microsoft Excel to demand-load macro sheets, locating and launching unopened macro sheets as buttons linked to them are used. A true link also avoids naming conflicts between add-in sheets that contain duplicate macro names.

Microsoft Excel 4.0 creates a true link between buttons and their corresponding add-in macro sheets for any Excel 3.0 spreadsheet that is loaded. In some cases, these links are not properly created. A result of this may be that clicking a button returns an error message because a valid file name has not been attached to the macro name assigned to the object. Because there is no link to an external sheet, Microsoft Excel 4.0 searches on the spreadsheet that contains the button for the defined name associated with it. The name is not found (because it actually exists on an external add-in sheet) and Microsoft Excel returns the error message.

Workaround

To establish an explicit link between the button and its add-in:

1. Select the button or object.

2. From the Macro menu, choose Assign To Object.

3. Delete the current reference.

4. Write a full reference to the add-in sheet (for example, MACRO1.XLA!Fld)

5. Choose the OK button.