excel - VBA enums give occasional "Constant Expression Required" errors

Question

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 up once in a blue moon and I was just unlucky?

Update after 6 weeks of further development:

The problem didn't recur during the rest of my time developing this project, so it looks like it is a rare problem.

Answer 1

As noted in the question, I got rid of the error by editing and saving the enum definition, then undoing the edit and saving again. Having recently done some more work on the project, I found a different but similar issue - one line of code would give a "Type mismatch" error, where there was no type mismatch and where the same function, unchanged, had been working fine with the same inputs.

Some of the intermittent errors I'm seeing might be due to a buildup of code artefacts in the Excel file - having done some reading, I've found that VBA code gets compiled and saved into the file. There's no "clean" or "rebuild all" option - VBA tries to work out for itself what incremental changes are needed. This can lead to all kinds of odd runtime behaviour in projects where you've made lots of code changes. This is likely the cause of the enum errors I was finding during initial development of this workbook. The section "What It Means to Decompile and Compact in VBA" in this article gives a good overview.

Most mentions of this problem recommend using VBA CodeCleaner: http://www.appspro.com/Utilities/CodeCleaner.htm. Chip Pearson, a well-known and respected VBA expert, says " I very strongly recommend this add-in". I'm surprised I haven't come across this before!