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.
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.
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.
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.
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.
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.
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?