2

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.

Improve this question
5
  • 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
    Jan 13, 2011 at 8:42
  • @DMA57361, the macro is defined in the sheet1 excel object. Ought it to be defined there?
    – Brian Hooper
    Jan 13, 2011 at 8:50
  • 2
    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
    Jan 13, 2011 at 8:55
  • @DMA57361, thank you. I'll give the matter some thought.
    – Brian Hooper
    Jan 13, 2011 at 9:24
  • I think @DMA57361 has your solution.
    – Nick Spreitzer
    Jan 13, 2011 at 18:26

2 Answers 2

Reset to default
2

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!

Improve this answer
1

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?

Improve this answer
3
  • 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
    Jan 12, 2011 at 22:48
  • 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
    Jan 12, 2011 at 23:12
  • thank you for your help. I have edited the question to supply more details.
    – Brian Hooper
    Jan 13, 2011 at 8:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .