I'm using an enum defined in a class module in Excel VBA. This has been working fine, but I've started getting a compile error on every time I do a comparison on enum variables:
In class CExample:
Enum MyEnum
Foo
Bar
End Enum
Elsewhere:
If someValue = myEnum.Foo Then
The text .Foo will be highlighted, and a "Compile error: Constant expression required" message pops up.
A search on Google suggests that this can randomly happen, and fixes such as restarting the IDE or adding a space after the enum declaration can make it start working again.
- http://www.tek-tips.com/viewthread.cfm?qid=1355882
- http://www.vbforums.com/showthread.php?405564-RESOLVED-Constant-Expression-Required-Error-when-checking-Enum
Is this really a known bug in VBA? Is there anything I can do to avoid it happening, or reliably get VBA working again if it does crop up?
In my case, closing and reopening Excel hasn't helped. Excuse me while I reboot my PC.
Update after reboot:
The problem persisted after rebooting my machine, which is surprising. I tried adding Public in front of the enum definition (they're meant to be public by default but I thought I'd give it a try), and the error disappeared. I've removed the Public keyword (so we're back to my original code) and it still compiles and runs fine.
It does look like this is a random bug in VBA. I'd be interested to know if experienced developers have found this comes up often - would you advise not using enums? Or does it pop u