iterating over enum in Excel 2003 VBA macro

[LloydM]

Hi all, first posting...

I need to iterate over an enum in an Excel 2003 VBA macro. The examples I see on the net, such as:

Dim items As Array
items = [Enum].GetValues(GetType(MyEnum))
Dim item As String
For Each item In items

don't work. The compiler shows an "Expected: new or Type name" error on the "Dim items as Array" line. I also get a "Sub or Function not defined" error on the "GetType()" function. I tried "GetUnderlyingType()" but got a "type mismatch" error. Can anyone give me an example of a working enum iterator for an Excel 2003 macro?

-Thanks! LloydM

[RobDog888]

Welcome to the Forums.

That code sure looks like VB.NET code.

[RobDog888]

No, I didnt mean I was going to move it only that the code snippet he got is from vb.net and not vba. Still he shows that he needs it in vba so it will remain here

[Webtest]

I think the only "Enumerator" in Excel VBA is "Collections". You can create your own collections ...

Option Explicit
Sub Macro1()
    Dim theDays As New Collection
    Dim tStr As Variant
    
    theDays.Add "Sunday"
    theDays.Add "Monday"
    theDays.Add "Tuesday"
    theDays.Add "Wednesday"
    theDays.Add "Thursday"
    theDays.Add "Friday"
    theDays.Add "Saturday"
    
    For Each tStr In theDays
        Debug.Print tStr
    Next tStr
       
End Sub

Note that "Collections" requires Variants or Objects. Does this help or make any sense?

[Webtest]

Here is how "Array" is used in VBA:

'Using "ARRAY" in VBA
Sub macro2()
    Dim myArray As Variant
    Dim tStr As Variant
    
    myArray = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
    For Each tStr In myArray
        Debug.Print tStr
    Next tStr
    
End Sub

[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.

[April15Hater]

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.

Oh good ol' Chip. I swear I've read the font off of his pages!

I doubt the built-in's will have that functionality since the real feature here is the hidden intellisense. All that's happening here is the enumerated value is duplicated and then assigned to a hidden element. While it's handy because it helps keep the code maintainable, it does have some limitations worth mentioning

This will only return 1 - 3:

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

Sub test()
    Dim lngA                                As Long
    For lngA = FruitType.[_First] To FruitType.[_Last]
        Debug.Print lngA
    Next lngA
End Sub

Since Enums are zero-based, this will return 0-3:

Enum FruitType
    [_First]
    Apple
    Orange
    [_Last]
    Plum
    Lemon
End Enum

Sub test()
    Dim lngA                                As Long
    For lngA = FruitType.[_First] To FruitType.[_Last]
        Debug.Print lngA
    Next lngA
End Sub

In my post I was trying to make the point that if you iterated over an enum that skipped a value, the loop will not know to skip over the value also.
So that means that this will still return a 2 during the iteration:

Enum Weekday
    [_First] = 1
    Monday = 1
    '?     = 2
    Tuesday = 3
    Wednesday = 4
    Thursday = 5
    Friday = 6
    Saturday = 7
    Sunday = 8
    [_Last] = 8
End Enum

Sub test()
    Dim lngA                                As Long
    For lngA = FruitType.[_First] To FruitType.[_Last]
        Debug.Print lngA
    Next lngA
End Sub

I have to go with Webtest's solutions. Collections and arrays are a beautiful thing. Dictionaries are even better if you can spare the overhead.

[westconn1]

In my post I was trying to make the point that if you iterated over an enum that skipped a value, the loop will not know to skip over the value also.
So that means that this will still return a 2 during the iteration:

that is probably a good reason not to assign specific value to an enum, at least if you intend to use them in place of an array, of course if you wish to use them as bit representations, or anything similar, then that would be different, but on iterating should test if it is a valid enum item

[MarceloScofano]

Oh good ol' Chip. I swear I've read the font off of his pages!

I doubt the built-in's will have that functionality since the real feature here is the hidden intellisense. All that's happening here is the enumerated value is duplicated and then assigned to a hidden element. While it's handy because it helps keep the code maintainable, it does have some limitations worth mentioning

This will only return 1 - 3:

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

Sub test()
    Dim lngA                                As Long
    For lngA = FruitType.[_First] To FruitType.[_Last]
        Debug.Print lngA
    Next lngA
End Sub

Since Enums are zero-based, this will return 0-3:

Enum FruitType
    [_First]
    Apple
    Orange
    [_Last]
    Plum
    Lemon
End Enum

Sub test()
    Dim lngA                                As Long
    For lngA = FruitType.[_First] To FruitType.[_Last]
        Debug.Print lngA
    Next lngA
End Sub

In my post I was trying to make the point that if you iterated over an enum that skipped a value, the loop will not know to skip over the value also.
So that means that this will still return a 2 during the iteration:

Enum Weekday
    [_First] = 1
    Monday = 1
    '?     = 2
    Tuesday = 3
    Wednesday = 4
    Thursday = 5
    Friday = 6
    Saturday = 7
    Sunday = 8
    [_Last] = 8
End Enum

Sub test()
    Dim lngA                                As Long
    For lngA = FruitType.[_First] To FruitType.[_Last]
        Debug.Print lngA
    Next lngA
End Sub

I have to go with Webtest's solutions. Collections and arrays are a beautiful thing. Dictionaries are even better if you can spare the overhead.

Indeed, a very old thread.
But I too drink from CPearson's site... Marvel is the definition of it.
And having the need of speed up a lot of DB reading just to populate combo and list boxes with values, I just translate them to Enums.
And of course, do a For Each like, with the For Next is a must, and the [_First] and [_Last] trick from http://www.cpearson.com/excel/Enums.aspx is the way to go.
But, and for non-sequential Enums? and I have a lot of them, from 20 to 40 Enum itens.
To unify all my combo and listbox feeding needs, I adapted CPearson's trick:

Sub EnumValueNamesWrapingAndUnwrapingToClipboard()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' EnumValueNamesToClipboard
' This creates a text string of the comma separated value names of an
' Enum data type. Put the cursor anywhere within an Enum definition
' and the code will create a comma separated string of all the
' enum value names. This can be used in a Select Case for validating
' values passed to a function. If the cursor is not within an enum
' definition when the code is executed, the results are unpredicable.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim N As Long
Dim txt As String, S As String
Dim SL As Long, EL As Long, SC As Long, EC As Long
Dim DataObj As MSForms.DataObject
Dim auxTitle As String, auxStrValue As String, strAuxCase As String
Dim counter As Integer, EnumMin As Integer, EnumMax As Integer
Dim auxValue As Variant
Dim EnumIsSequential As Boolean

Const STR_ENUM As String = "enum "
    If VBE.ActiveCodePane Is Nothing Then
        Exit Sub
    End If
    With VBE.ActiveCodePane
        .GetSelection SL, SC, EL, EC
        With .CodeModule
            S = .Lines(SL, 1)
            Do Until InStr(1, S, STR_ENUM, vbTextCompare) > 0
                N = N + 1
                S = .Lines(SL - N, 1)
            Loop
            'Function title
            auxTitle = Right$(S, Len(S) - InStr(1, S, STR_ENUM, vbTextCompare) - Len(STR_ENUM) + Len(" "))
            N = SL - N + 1
            S = .Lines(N, 1)
            Do
                S = .Lines(N, 1)
                If InStr(1, S, "end enum", vbTextCompare) = 0 And InStr(1, S, "'", vbTextCompare) = 0 Then
                    txt = txt & " " & Trim(S) & ","
                End If
                N = N + 1
            Loop Until InStr(1, S, "end enum", vbTextCompare) > 0
            ReDim auxValue(0)
            ReDim Preserve auxValue(0 To StringCountOccurrences(txt, "=") - 2) 'because of [_First] and [_Last]
            For counter = 1 To UBound(auxValue)
                auxStrValue = RetornaElementoDesignado(counter + 1, Left(txt, Len(txt) - 1))
                If counter = 1 Then
                    EnumMin = CInt(Trim$(Right$(auxStrValue, Len(auxStrValue) - InStrRev(auxStrValue, "="))))
                    auxValue(counter) = Trim$(Left$(auxStrValue, InStr(1, auxStrValue, " = ")))
                ElseIf counter = UBound(auxValue) Then
                    EnumMax = CInt(Trim$(Right$(auxStrValue, Len(auxStrValue) - InStrRev(auxStrValue, "="))))
                    auxValue(counter) = Trim$(Left$(auxStrValue, InStr(1, auxStrValue, " = ")))
                Else
                    auxValue(counter) = Trim$(Left$(auxStrValue, InStr(1, auxStrValue, " = ")))
                End If
            Next counter
        End With
    End With
    EnumIsSequential = NumElements(auxValue) - 1 = EnumMax - EnumMin + 1
    strAuxCase = "Function ReturnNameEnum" & auxTitle & " (ByVal WhichEnum As " & auxTitle & ")As String" & vbCrLf _
                         & "  Select Case WhichEnum" & vbCrLf
    For counter = 1 To UBound(auxValue)
        strAuxCase = strAuxCase & "     Case Is = " & auxTitle & "." & auxValue(counter) & vbCrLf _
            & "          ReturnNameEnum" & auxTitle & " = " & ParseSpecialCharsAndDataTypeForSQL(auxValue(counter), False, True, False) & vbCrLf
    Next counter
    If EnumIsSequential Then
        strAuxCase = strAuxCase & "     Case Else" & vbCrLf _
            & "          debug.print " & """Passed """ & " & WhichEnum & " & """ WhichEnum As " & auxTitle & " inexistente! """ & vbCrLf _
            & "    End Select" & vbCrLf _
            & "End Function" & vbCrLf _
            & "Function LoadEnum" & auxTitle & "InArray () As Variant" & vbCrLf _
            & "    'If Enum is Sequential" & vbCrLf _
            & "    Dim items() As Variant, item As Long, counter As Long" & vbCrLf _
            & "    For item = " & auxTitle & ".[_first] To " & auxTitle & ".[_last]" & vbCrLf _
            & "        counter = counter + 1" & vbCrLf _
            & "    Next" & vbCrLf _
            & "    ReDim items(counter * 2 - 1) '-1: it's 0-based..." & vbCrLf _
            & "    For item = " & auxTitle & ".[_first] To " & auxTitle & ".[_last]" & vbCrLf _
            & "        items(item * 2) = item" & vbCrLf _
            & "    items(item * 2 + 1) = ReturnNameEnum" & auxTitle & "(item)" & vbCrLf _
            & "        items(item * 2) = item" & vbCrLf _
            & "    Next" & vbCrLf _
            & "    LoadEnum" & auxTitle & "InArray=items()" & vbCrLf _
            & "End Function"
    Else
        strAuxCase = strAuxCase & "     Case Else" & vbCrLf _
          & "          debug.print " & """Passed """ & " & WhichEnum & " & """ WhichEnum As " & auxTitle & " inexistente! """ & vbCrLf _
          & "    End Select" & vbCrLf _
          & "End Function" & vbCrLf _
          & "Function LoadEnum" & auxTitle & "InArray () As Variant" & vbCrLf _
          & "    'For Non-Sequential Enum" & vbCrLf _
          & "    Dim items() As Variant, item As Long, ExistingEnum As Long" & vbCrLf _
          & "    For item = " & auxTitle & ".[_first] To " & auxTitle & ".[_last]" & vbCrLf _
          & "        if ReturnNameEnum" & auxTitle & "(item) <> """" then" & vbCrLf _
          & "            ExistingEnum = ExistingEnum + 1" & vbCrLf _
          & "            auxExistingEnum = auxExistingEnum & CStr(item) & "",""" & vbCrLf _
          & "        end if" & vbCrLf _
          & "    Next" & vbCrLf _
          & "    auxExistingEnum = Left$(auxExistingEnum, Len(auxExistingEnum) - 1)" & vbCrLf _
          & "    arrayExistingEnum = Split(auxExistingEnum, "","")" & vbCrLf _
          & "    ReDim items(ExistingEnum * 2 - 1) '-1: it's 0-based..." & vbCrLf _
          & "    If ReturnNameEnum" & auxTitle & "(arrayExistingEnum(item)) = """" Then GoTo continue" & vbCrLf _
          & "        items(item * 2) = arrayExistingEnum(item)" & vbCrLf _
          & "        items(item * 2 + 1) = ReturnNameEnum" & auxTitle & "(arrayExistingEnum(item))" & vbCrLf _
          & "continue:" & vbCrLf _
          & "    Next" & vbCrLf _
          & "    LoadEnum" & auxTitle & "InArray=items()" & vbCrLf _
          & "End Function"
    End If
    Set DataObj = New MSForms.DataObject
    With DataObj
        .SetText strAuxCase
        .PutInClipboard
        Debug.Print strAuxCase
    End With
    Set DataObj = Nothing
End Sub

I added skip comment lines - I do a lot while developing.
I did not treat when Enum is not in Ascendant order; could be done, but I'm too OCD to allow a unordered Enum
Of course, it depends on [_First] and [_Last] values added properly.
The procedure loads in a unidimensional array tuples of value/name of Enum; so, after, we can navigate all Enum values in a:

auxArray=LoadEnumWhateverNameYouGaveItInArray()
For counter = lbound(auxArray) to ubound(auxArray) step 2
     EnumValue = auxArray(counter)
     EnumStringName = auxArray(counter+1)
Next counter

The procedure is generating to different LoadEnumWhateverNameYouGaveItInArray() functions; one for sequential enums and another for non-sequential enums, but you can forget about the sequential; the non-sequential enum function grab both situations; I left here because I first developed it and after adapted to the non-sequential case.

Hope it helps someone.