I built some macros and save the VBA project as .xlam (Excel Add In) file. The macros (Public Subs) are visible when I go to Excel > File > Options > Quick Access Toolbar(QAT) > Choose 'Macros' from the left drop-down menu. Then I can add the macros to the QAT so users can click the tools icon to run those macros.
I observed a VERY strange behavior. Here's what it was before, everything was normal.
Then I start typing new codes. I didn't run the code. I'm just typing. Then I noticed the Macro names in the QAT list turns into this:
The code I'm typing is as below:
After a couple experiment, it looks like if I change the argument type from a custom class to a built-in type. The macro names will back to normal. Like this:
This baffles me. I'm just typing. I'm NOT running the code. I'm not even touching the macros that's already on the list. I'm typing a new macro. Why typing the code will affect the macro name display? It's not just a cosmetic issue. Once the macro name display changed on the list, I won't be able to use QAT to access them. Every time I click the QAT button, I got the message that "Cannot run the macro'XXX'. The macro may not be available in this workbook or all macros may be disabled", even though the file was there and macro was enabled.
After some analysis, it looks like when a sub or a function referencing an undefined class, Excel will behave like this. But here in my case, the class was defined.
This behavior is always reproducible. If I remove the reference to a custom class, everything will back to normal and I can trigger the macro by clicking QAT buttons.
What I don't understand is - if there's a problem with my VBA code, it should throw compile error or run-time error. It should not error the Excel user interface. Does this sound like a bug to you? Thanks!
