Bug in Excel VBA compiler?

[Matt Werner]

Today a mysterious problem arose after making some edits to a VBA project.
After adding a few new lines of code, the compiler will no longer compile
(and run) my subroutine. The compiler reports "Compile error: Constant
expression required" on a line where I reference a Public Enum.

The enumeration is properly declared in the (General) (Declarations) section
of the same module. When editing on the same line of the error, the
enumeration is recognized by the editor as an option when typing.

The updates to the new code today have nothing to do with the enumeration or
the line in which the error is occurring. After REMing out all new code
from today, the error still persists even though it all worked fine
yesterday. If I REM out the line with the error, the compiler stops on the
next line with a reference to a public enumeration.

I experienced this same problem (with a different project) about a month
ago. After searching the KB and the Internet to no avail, the problem
mysteriously went away. Of course, I wasted half a day trying to figure it
out.

Then I remembered that the last time I copied all my code from the problem
subroutine into a new subroutine. I then renamed both subroutines so the
new one is named with the original name. I performed this same procedure
today, and poof! - the problem is gone.

I'm curious if anyone else has encountered this problem and if there is a
reason why it occurs. I have never been able to locate any KB article on
this. Obviously, this sort of problem is very frustrating because the code
is correct.

Any insight will be greatly appreciated.

[Peter Huang [MSFT]]

Hi

Thanks for your experience sharing.
Commonly it is hard to say what is going on without a reproduce scenario.
But I think you would try to create a new Excel and copy/paste all the code
to see if that works for you if you have the problem again

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.