PDA

View Full Version : Sleeper: Personal.xls macros "not available"



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.

mvidas
04-10-2005, 08:50 AM
Well I must say that I don't often use the alt-f8 macros window, but i thought it used the format Personal.xls!CodeModuleName.MacroName only when two macros with the same name in different modules existed?

Also, I think excel does list the macros alphabetically by macro name (unless the codemodulename is there), at least in excel 2000.

MWE
04-10-2005, 10:02 AM
Well I must say that I don't often use the alt-f8 macros window, but i thought it used the format Personal.xls!CodeModuleName.MacroName only when two macros with the same name in different modules existed?

Also, I think excel does list the macros alphabetically by macro name (unless the codemodulename is there), at least in excel 2000.
Thanks for your reply. The "two macros with the same name in different modules" theory makes sense, but I would expect that if such were the case, both offending modules would use the format Personal.xls!CodeModuleName.MacroName. That does not seem to be true. Perhaps only the "2nd" module uses the longer format?? That still does not explain why macros listed with the embedded codemodulename are NEVER available when assigned to manually created menus. I will do some testing of this theory and post results back here.:dunno

The macros are not listed alphabetically -- at least not all are. Th eparticular case I examined had approx 50 macros listed. 45 or so were alphabetized; the rest were a jumble. The first 45 or so were from Personal.xls, then one from the local file, then two from personal.xls, then another from the local file, ...

mvidas
04-10-2005, 10:37 AM
Both macros should have the format including the codemodulename, I'd be surprised otherwise as that is how it is for me.
They are listed alphabetically, but only after the ! if it is there. So you might see:
amacro
bmacro
personal.xls!cmacro
personal.xls!dmodule.zmacro
emacro
personal.xls!fmodule.zmacro
gmacro

I'm not sure why the personal.xls!codemodulename.macroname format macros wont be available when assigning to manually created menus. If you have a macro in an .xla addin or something, you wont see it available on the list, but you can always type the macro name in manually and it will work.
Matt

Cyberdude
04-10-2005, 11:34 AM
I gotta admit that it's confusing when you select "All VBA Projects" in the macros list. I usually choose only the workbook I'm working in so I hadn't noticed how they handle ALL macros. They tend to be alphabetized, but when I selected all workbooks, it showed unqualified all the macros in the workbook whose module I had last clicked. Said differently, if in the VBE I have a module showing macros from Personal AND I have a module showing from another workbook, then the last module I clicked has all its macro names from that workbook listed unqualified and alphabetized ... sorta. At one point they started qualifying again with the workbook name and module name, then they switched back to unqualified. I looked at it real hard, and I couldn't make sense of what they are doing.
The thing that annoys me is that if I have duplicates in the same workbook, neither is shown. That's bad. That's one reason I wrote my own macro name lister and added an option to find all duplicates. I can also sort them by module name or macro name. When you have over 200 macro names, it really helps! :thumb