Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

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

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

share|improve this answer
    
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 '11 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 Jan 12 '11 at 23:12
    
thank you for your help. I have edited the question to supply more details. –  Brian Hooper Jan 13 '11 at 8:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.