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.
sheet1.macroname
to becomespreadsheet.xls!sheet1.macroname
...