iterating over enum in Excel 2003 VBA macro
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
Re: iterating over enum in Excel 2003 VBA macro
Welcome to the Forums.
That code sure looks like VB.NET code. ;)
Re: iterating over enum in Excel 2003 VBA macro
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 ;)
Re: iterating over enum in Excel 2003 VBA macro
I think the only "Enumerator" in Excel VBA is "Collections". You can create your own collections ...
Code:
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?
Re: iterating over enum in Excel 2003 VBA macro
Here is how "Array" is used in VBA:
Code:
'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
Re: iterating over enum in Excel 2003 VBA macro
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
Code:
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.
Re: iterating over enum in Excel 2003 VBA macro
Quote:
Originally Posted by
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
Code:
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?
Code:
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
Code:
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?
Re: iterating over enum in Excel 2003 VBA macro
Quote:
Originally Posted by
April15Hater
How about if you had code as follows?
Code:
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
Code:
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
Code:
Enum FruitType
[_First] = 1
Apple = 1
Orange = 2
Plum = 3
[_Last] = 3
End Enum
and later use
or
Unfortunately, some (perhaps all of?) built-in enums apparently do not have those definitions.
Re: iterating over enum in Excel 2003 VBA macro
Quote:
Originally Posted by
sanchin
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
Code:
Enum FruitType
[_First] = 1
Apple = 1
Orange = 2
Plum = 3
[_Last] = 3
End Enum
and later use
or
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:
Code:
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:
Code:
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:
Code:
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.
Re: iterating over enum in Excel 2003 VBA macro
Quote:
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
Re: iterating over enum in Excel 2003 VBA macro
Quote:
Originally Posted by
April15Hater
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:
Code:
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:
Code:
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:
Code:
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:
Code:
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:
Code:
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.
