Macro name being changed in Excel

I am creating VBA macros in my Excel spreadsheet. I notice that from time to time (after saving the spreadsheet and reopening it, usually) one or more of the macro names is being changed from sheet1.macroname to spreadsheetname.xls!macroname. This isn't a valid macro name so I can no longer run it.

I can fix the problem by deleting all the macros, saving the result, pasting the macros back in again and saving again, but one can't expect normal users to do that.

Does anyone know what is causing this, and what I can do to prevent it?

Edit with more detail

When I start up the spreadsheet, and display the Macro dialogue, one, or sometimes more, of the macros appears to have its name changed from sheet1.macroname to spreadsheet.xls!macroname. Attempting to run this macro by double clicking it produces the error message 'Macro name is not valid'. Closing the error message box and double-clicking a second time produces the error message 'Reference is not valid'. Subsequent attempts do the same.

Invoking a different macro sheet1.othermacro by double-clicking it works and dismisses the macro dialogue. On redisplaying it, the first macro has changed its name back to sheet1.macroname. Which does fix the problem, I suppose.

Brian Hooper

Posted 2011-01-12T08:39:15.897

Reputation: 1 755

It looks like the macro is placed within a Sheet's code space, have you tried instead placing it within a Module? However, then I'd really expect sheet1.macroname to become spreadsheet.xls!sheet1.macroname... – DMA57361 – 2011-01-13T08:42:23.833

@DMA57361, the macro is defined in the sheet1 excel object. Ought it to be defined there? – Brian Hooper – 2011-01-13T08:50:20.027

In general Modules should be the first choice for placing macros (see this Stack Overflow question for some discussion), with only event procedures usually in the sheet's code space.

– DMA57361 – 2011-01-13T08:55:49.507

@DMA57361, thank you. I'll give the matter some thought. – Brian Hooper – 2011-01-13T09:24:54.457

I think @DMA57361 has your solution. – Nick Spreitzer – 2011-01-13T18:26:55.627

Answers

I found the solution by chance.

The problem occurs only if the Module name is identical to the Macro name. So the Module has to be named differently. That's it!

Arnold

Posted 2011-01-12T08:39:15.897

Reputation: 36

I'm suspicious that the macro's name isn't really changing: If you open the Macros dialog box from excel when more than workbook is open, macros stored within the currently active workbook are displayed as MarcoName. However, macros stored within a workbook that is not active are displayed as WorkbookName.xls!MacroName. That said, are you certain that the macro name is changing?

When you say you can no longer run the macro, can you be more specific about what exactly happens? Nothing? Error message?

Also, are you recording these macros or are you writing them yourself?

Nick Spreitzer

Posted 2011-01-12T08:39:15.897

Reputation: 439

I'm writing the macros myself. The change of name I am observing in the Macro dialogue. The error message is "invalid macro name"; I will see if I can dig up any more evidence tomorrow. – Brian Hooper – 2011-01-12T22:48:39.043

Very strange. And you get that error message when you attempt to run the macro from the Macros Dialog? Or when you call it from other code? I'm very intrigued by this.... – Nick Spreitzer – 2011-01-12T23:12:31.400

thank you for your help. I have edited the question to supply more details. – Brian Hooper – 2011-01-13T08:34:19.040