Excel Macro Name Conflicts

Last week I ran into an interesting problem, when trying to fix a macro button in a client’s Excel file. I’ve created a simplified version of the workbook and button, to show you what happened.
The workbook had been converted from Excel 2003 to Excel 2010, and when the macro button was clicked, it showed an error message.

  • Cannot run the macro…The macro may not be available in this workbook or all macros may be disabled.

macronameconflict01b
Other buttons in the workbook were working fine, so the macros were enabled – that wasn’t the problem.

Assign a Macro to the Button

I tried to reconnect the button to the macro, but when I tried, another error message appeared.

  • Reference must be to a macro sheet.

macronameconflict02
Well, I haven’t used a macro sheet for about 10 years, so that was a bit confusing! Didn’t we get rid of macro sheets, several versions ago?

Cell Name Conflict

Finally, I realized what the problem was. The person who created the macro used a very short name – MBT1 – which was fine in Excel 2003, where the last column was IV.
When the file was converted to Excel 2010, the macro name became a problem, because there is a cell with the address MBT1. During the conversion, the button’s macro reference was automatically changed to _MBT1 to prevent a conflict with the cell reference.
macronameconflict04
However, the macro name wasn’t changed automatically – it was still named MTB1. So, the button couldn’t find a macro named _MTB1, and the error message appeared.
macronameconflict06

Fix the Macro Name Conflict

To fix the problem, I changed the macro name to MTB1_Macro, and connected the button to the renamed macro. Now, the button works correctly, and runs the macro without complaining.
macronameconflict07

Prevent the Problem

When you’re recording a macro, if you use a name that’s the same as a cell name in Excel 2010, you’ll see an error message, warning you about the name conflict.

  • The name conflicts with an Excel built-in name or the name of another object in the workbook.

macronameconflict01
Use longer names for your macros, or include an underscore, to avoid problems – now, and in the future. Who knows how many columns the next version of Excel will have?

Other Macro Naming Problems

You can also have problems running your macros if the procedures have the same name as a module in the workbook. To avoid that, start your module names with “mod”, such as modUpdate, and don’t use those names for any macros.
Have you run into any other problems with naming your macros?
____________