CODEWITHSUNDEEP
excelvba
Neil Vass
Neil Vass

Reputation: 5341

VBA enums give occasional "Constant Expression Required" errors

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.

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.

Upvotes: 30

Views: 10085

Answers (8)

mjj1
mjj1

Reputation: 1

I encountered this issue today for the first time.

The code that failed looks like this:

Private Enum showPlanOptions
  spoAllPlans = 1
  spoCurrentPlans = 2
End Enum
...
Private Sub fraShowPlans_AfterUpdate()
  With Me
    Select Case Me![fraShowRates]
    Case spoAllPlans
      .Filter = ""
    Case spoCurrentPlans
      .Filter = "isnull([planEnd])"
      .FilterOn = True
    End Select
  End With
End Sub

I had copied the code from another form module (one where "rates" were the relevant record source") and thought I had modified it for the record source "plans".

After reading the answers above, I looked carefully and saw that my select statement referenced the non-existent fraShowRates option group because I hadn't modified the code properly.

It would seem the IDE is clever enough to know there's a problem, but not clever enough to spell it out at compile time. Of course, the code fails at runtime!

Upvotes: 0

Eleshar
Eleshar

Reputation: 513

This ocurred to me today and for some reason the cause was the accidentally deleted "Option Explicit" statement at the top of the module with the Enum declarations. When I re-added it, it immediately started working.

Upvotes: 1

Trashman
Trashman

Reputation: 1584

I ran into this issue today and found another possible solution. I'm posting in case this may help someone else.

None of the following worked for me:

  1. Restarting Excel
  2. Restarting Windows
  3. Removing "Public" from my enum
  4. Adding "Public" back to my enum

I could not try using VBA cleaner add-in since this is a work computer and I don't have admin access.

I did not try creating a new module, so I don't know if that would have worked.

However, closing Excel and starting a new instance of Excel and using "Open and Repair" option worked for me. To do that:

  1. Close all Excel windows
  2. Open Excel from the Start Menu
  3. Go to "Open" on the left hand side
  4. Select "Browse" under "Other Locations"
  5. Navigate to the file you had a problem with
  6. Click the drop down arrow on "Open" in the lower right and select "Open and Repair..."

Upvotes: 1

Dustin Snyder
Dustin Snyder

Reputation: 1

I had the same issue with my Enum, I added Public Enum to the declaration and the problem stopped. No need to reboot.

Upvotes: 0

PaulK
PaulK

Reputation: 91

This error does now and then, when no changes were made to the enum or its use or any code related. What worked for me is to make the move the enum from the Class to a Module I have called 'Common' and make the enum Public instead of Private.

Upvotes: 0

Thor
Thor

Reputation: 498

An old question but just experienced this. Removed Public definer on the Enum and it compiled just fine. Didn't restart IDE. Surprising this is still here.

Upvotes: 2

Neil Vass
Neil Vass

Reputation: 5341

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!

Upvotes: 22

JustinJDavies
JustinJDavies

Reputation: 2693

Seems to be a bug.

Copy the same module's code to a new one, and recompile. That seems to solve it for some.

A similar fix exists, which involves editing and undoing on the enum definition's line.

Consider switching to numeric constants if this is a frequent problem.

Upvotes: 8

Related Questions