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?

____________

You may also like...

9 Responses

  1. Hui says:

    Not so much macros as Named Formula
    Excel 2007 doesn’t like the word Chart to be included in Named Formula that are used in Charts
    http://chandoo.org/forums/topic/any-fans-of-walkenbach-2003-charts-out-there

  2. Steve Farrar says:

    Amazing- about 5 minutes after reading this I opened a workbook that had that very problem- had a macro called “age2?

  3. Gregory says:

    I ran into a similar problem when I created some defined names in Excel 2003 that were used by a macro. SDC1, SDC2, SDC3, SDC4 all work great in Excel 2003 but conflict with cell ranges in Excel 2007 and 2010. My work-around was to rename to _SDC1, etc. as you’ve done here.

  4. Ninad says:

    I typically name all my macros beginning with ‘mcr’ (w/o quotes). I also follow naming conventions for variables, etc. which make it easier to understand and follow.

  5. JC says:

    Is it true that I can only assign a single letter with the CRTL key in a macro, that means 26 letters (a-z) plus upper case (A-Z) with a maxumum total of 52 macros created for all spreadsheets.

  6. JDB says:

    Recently I had the problem ‘Reference must be to a macro sheet’ by assigning a macro to a button in Excel 2003. I named all my macros the same as the title of the button (FULL1, KEY1, KEY2, …).
    All worked fine, except for the macro FO3. Indeed, an existing range in Excel2003, but if this is the real reason, why didn’t I had the problem also for FO1 and FO2?

  7. Dave says:

    I just ran into a similar problem too, a conflict between a user defined named range and a macro name. If I assign a macro “myStuff” to a button, it works fine. But as soon as I give the name ‘myStuff” to a range I get the message “Cannot run the macro…The macro may not be available in this workbook or all macros may be disabled.” It seems the button assignment now requires the module name to prefix the macro name (module1.myStuff).
    Interestingly, I don’t get an error recording a macro using this name, like I would using an Excel range references – see “Prevent the Problem” above.

  8. GerryL says:

    Hi all,
    In Excel 2003 I used a lot of rangenames in a workbook that now conflict with cells in Excel 2010 such as ABC1, ABC2 etc. due to the large number of columns. I also used these names in macros.
    Is there a way to use VBA to search for all the names in a workbook and give the option to change the name in an input box?
    I found a macro that can automatically add text to all existing names but I like to decide for myself how I rename the range. Maybe with a message box which is asking me to enter a new name. I like to use VBA as I’m dealing with thousands of range names in the workbook.
    When this is done I have to check all the formulas and finally go through all the macros and rename the rangenames in the macros as well. Is there also a VBA macro that can search through all the macros and change rangenames? Preferably by an input message box so I can decide what the new name will be?
    Other co-workers are dealing with this problem now they switched from Excel 2003 to Excel 2010. When you only have a few names to change it could of course be done manually but for thousands of names it is very time consuming.
    I searched on the internet but could not find any VBA solution.
    Anyone who can assist???

  9. Gunawan says:

    i have similar problem, but the macro was lock and I cant edit the macro anymore, is there anyway to define the function ( say CAA1 as function and not as Cell reference ?

Leave a Reply to Gunawan Cancel reply

Your email address will not be published. Required fields are marked *