Losing Module Level ENums in Excel 2003

I

INTP56

This is driving me crazy!

I have a bunch of constants and Private Enums declared at the top of my
regular module.

e.g.
(I've paired this down for brevity.)

Private Const mcstrDataWorksheetName As String = "DataSheet"
Private mstrArray() as String
Private Enum eStageName
LBoundIndex = 1&
Text = 1&
DatabaseName = 2&
DisplayName = 3&
UBoundIndex = 3&
End Enum

Private Enum ePropertyIndex
LBoundIndex = 1&
Text = 1&
DisplayName = 2&
DefaultValue = 3&
Unit = 4&
UBoundIndex = 4&
End Enum

I use them like

Dim mstrArray(ePropertyIndex.LBoundIndex To ePropertyIndex.UBoundIndex)

Everything have been working fine for several weeks ... except
Every now and then, the Enums stop working! It's always after some
debugging, trying things, making some mistakes, and then, making some change
in a section of code that is seemingly unrelated to Enums, the next time I
try and run the program I get the following:

Microsoft Visual Basic
Compile Error:
Constant expression required.

And it goes to the first spot in my LoadObjects routine that uses an Enum,
with the member highlighted. (the Dot and DisplayName are highlighted)

ePropertyIndex.DisplayName

NOTE: Commenting out the line simply moves the error to the next Enum. If I
comment out every enum used in the Sub, it will recognize other module level
variables and constants. But will give me the same compile error whenever it
runs across the next Enum. I comment out everything I was just changing and
it won't run.

Sometimes, restarting Excel "fixes" the problem, sometimes rebooting the box
"fixes" the problem, other times, it just refuses to work and at what seems
like an arbitrary time in the future, it works again.

Anybody got any ideas?

TIA, Bob
 
I

INTP56

FWIW, this seems to be working ...

Comment out all enums.
Compile under the Debug Menu, let it fail.
Uncomment the enums, Recompile

I don't understand it, but this heuristic has worked every time so far.

Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top