Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2008

Tip: Looking for answers? Try searching our database.

Losing Module Level ENums in Excel 2003

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
INTP56 - 28 Jan 2008 03:41 GMT
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
INTP56 - 31 Jan 2008 20:58 GMT
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

> This is driving me crazy!
>
[quoted text clipped - 55 lines]
>
> TIA, Bob
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.