MWE
04-09-2005, 02:56 PM
this is not really a VBA question, but perhaps someone with Excel experience has an idea what might be wrong here.
I have a rather large Personal.xls file with many (250?) vba procedures. It has a dozen code modules; each module containing procs specific to a particular application or function, e.g., signal processing, general math & stats, Excel, Word, etc. I have noticed something unusual about some of these modules. When I navigate to Tools | Macro | Macro to see what macros the application thinks can be called "directly", most of the macros listed are of the form
Personal.xls!MacroName
But some are of the form
Personal.xls!CodeModuleName.MacroName:dunno
If I create a custom menu item manually and select the target macro from the list of available macros, all available macros are listed and selectable. But only those listed/selected that are in the Personal.xls!MacroName format actually "work". If I create a custom menu item and select a macro that is of the form Personal.xls!CodeModuleName.MacroName; when I click on the menu item to execute the macro, I get an error message indicating that the macro is not available. :banghead:
If I move the problem macro into another code module in Personal.xls so that it displays as Personal.xls!MacroName, and rebuild the custom menu item, everything works fine.
So, it appears that Excel "sees" some code modules in Personal.xls differently than others and inserts the code module name into the "fully qualified macro name".
Any thoughts what is going on here?
A related question: how does Excel decide to list the available macros. It is certainly not alphabetically or by code module.
I have a rather large Personal.xls file with many (250?) vba procedures. It has a dozen code modules; each module containing procs specific to a particular application or function, e.g., signal processing, general math & stats, Excel, Word, etc. I have noticed something unusual about some of these modules. When I navigate to Tools | Macro | Macro to see what macros the application thinks can be called "directly", most of the macros listed are of the form
Personal.xls!MacroName
But some are of the form
Personal.xls!CodeModuleName.MacroName:dunno
If I create a custom menu item manually and select the target macro from the list of available macros, all available macros are listed and selectable. But only those listed/selected that are in the Personal.xls!MacroName format actually "work". If I create a custom menu item and select a macro that is of the form Personal.xls!CodeModuleName.MacroName; when I click on the menu item to execute the macro, I get an error message indicating that the macro is not available. :banghead:
If I move the problem macro into another code module in Personal.xls so that it displays as Personal.xls!MacroName, and rebuild the custom menu item, everything works fine.
So, it appears that Excel "sees" some code modules in Personal.xls differently than others and inserts the code module name into the "fully qualified macro name".
Any thoughts what is going on here?
A related question: how does Excel decide to list the available macros. It is certainly not alphabetically or by code module.