iterating over enum in Excel 2003 VBA macro

[Yorgan]

Although this is an old thread I thought I should post a solution for the benefit of people Googling this question.

Enums in VBA as in many other languages are just wrappers for Integers, and can be treated as such for a lot of purposes including loops.

So say you have

Enum Weekday
  Monday
  Tuesday
  '....
  Saturday
  Sunday

You can loop over all values with a simple for loop:

For day = Monday to Sunday
' Loop code
Next day

Be careful though - if you add a new first or last item to the type such loops will no longer provide complete coverage.

Tested in Excel 2007 but I imagine it will work in all previous versions.

[April15Hater]

Although this is an old thread I thought I should post a solution for the benefit of people Googling this question.

Enums in VBA as in many other languages are just wrappers for Integers, and can be treated as such for a lot of purposes including loops.

So say you have

Enum Weekday
  Monday
  Tuesday
  '....
  Saturday
  Sunday

You can loop over all values with a simple for loop:

For day = Monday to Sunday
' Loop code
Next day

Be careful though - if you add a new first or last item to the type such loops will no longer provide complete coverage.

Tested in Excel 2007 but I imagine it will work in all previous versions.

How about if you had code as follows?

Enum Weekday
  Monday=1
  Tuesday=3
  Wednesday=4
  Thursday=5
  Friday=6
  Saturday=7
  Sunday=8
End Enum

Notice the skip over the 2 on the Tuesday Enumeration

For Day = Monday To Sunday
  Debug.Print Day
Next Day

Will return : 1-8 in the immediate window.

Know of any way to test if a number is defined in an enum?

[sanchin]

How about if you had code as follows?

Enum Weekday
  Monday=1
  Tuesday=3
  Wednesday=4
  Thursday=5
  Friday=6
  Saturday=7
  Sunday=8
End Enum

Notice the skip over the 2 on the Tuesday Enumeration

For Day = Monday To Sunday
  Debug.Print Day
Next Day

Will return : 1-8 in the immediate window.

Know of any way to test if a number is defined in an enum?

Regarding the first and last items in an Enum, just check the page by the great Chip Pearson

http://www.cpearson.com/excel/Enums.aspx

Simply transcribing, you can define your Enum as

    Enum FruitType
        [_First] = 1
        Apple = 1
        Orange = 2
        Plum = 3
        [_Last] = 3
    End Enum

and later use

FruitType.[_First]

or

FruitType.[_Last]

Unfortunately, some (perhaps all of?) built-in enums apparently do not have those definitions.