Loop Enumeration Constants

[Matthew Herbert]

All,

I'm curious to know if there is a way to loop through the constants of an
enumeration. (Or, maybe another way to put this is this: Is there a way to
get an enumeration as a collection?)

I'll illustrate with a simple example below ("TestEnumLoop"), which puts the
constants of XlYesNoGuess into an array, loops through the array, and prints
the constant values to the Immediate Window. (Continue reading after
"TestEnumLoop" for the "hypothetical code").

Sub TestEnumLoop()
Dim varArr As Variant
Dim varItem As Variant
'Dim xlEnum As XlYesNoGuess

varArr = Array(xlGuess, xlNo, xlYes)

For Each varItem In varArr
Debug.Print varItem
Next varItem

End Sub

So, I'm curious to know if there is some way to loop through the
"collection" of constants for a given enumeration. For example, is there
something that would exist for the "hypothetical code" below?

Sub HypotheticalLoop()
Dim xlEnum As XlYesNoGuess

For Each xlEnum In XlYesNoGuess
Debug.Print xlEnumTest
Next xlEnum

End Sub

Thanks,

Matthew Herbert

 

[Bob Phillips]

I wouldn't have thought so, as a Enum list is just a set of integers with a
descriptive handle assigned to them. If it were in the OS somewhere maybe,
but this would be within VB as far as I can see.

HTH

Bob

 

[Chip Pearson]

You can do this ONLY IF you have the TypeLibInfo DLL installed on your
computer. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If this item exists, check it. If it does not
exist, then quit reading because you can't do what you want to do. The
file name of the DLL you need is TLBINF32.dll.

The following code shows how to get the names and values in the
XLYesNoGuess enum:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLILibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim N As Long
Dim S As String
Dim ConstName As String

Set TLIApp = New TLI.TLIApplication
Set TLILibInfo = New TLI.TypeLibInfo
Set TLILibInfo = TLIApp.TypeLibInfoFromFile( _
ThisWorkbook.VBProject.References("EXCEL").FullPath)

ConstName = "XLYesNoGuess"
For Each MemInfo In _
TLILibInfo.Constants.NamedItem(ConstName).Members
S = MemInfo.Name
N = MemInfo.Value
Debug.Print S, CStr(N)
Next MemInfo
End Sub

Using this knowledge, you can create two useful functions. EnumNames
returns an array of strings containing the names of the values in an
enum:

Function EnumNames(EnumGroupName As String) As String()
Dim TLIApp As TLI.TLIApplication
Dim TLILibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim Arr() As String
Dim Ndx As Long
Set TLIApp = New TLI.TLIApplication
Set TLILibInfo = New TLI.TypeLibInfo
Set TLILibInfo = TLIApp.TypeLibInfoFromFile( _
ThisWorkbook.VBProject.References("EXCEL").FullPath)
On Error Resume Next
With TLILibInfo.Constants.NamedItem(EnumGroupName)
ReDim Arr(1 To .Members.Count)
For Each MemInfo In .Members
Ndx = Ndx + 1
Arr(Ndx) = MemInfo.Name
Next MemInfo
End With

EnumNames = Arr
End Function


You would call this function with code such as:

Sub ZZZ()
Dim Arr() As String
Dim N As Long
Arr = EnumNames("XLYesNoGuess")
For N = LBound(Arr) To UBound(Arr)
Debug.Print Arr(N)
Next N
End Sub


You can also create a function to test if a value is defined for an
enum:

Function IsValidValue(EnumGroupName As String, Value As Long) As
Boolean
Dim TLIApp As TLI.TLIApplication
Dim TLILibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim Ndx As Long
Set TLIApp = New TLI.TLIApplication
Set TLILibInfo = New TLI.TypeLibInfo
Set TLILibInfo = TLIApp.TypeLibInfoFromFile( _
ThisWorkbook.VBProject.References("EXCEL").FullPath)
On Error Resume Next
With TLILibInfo.Constants.NamedItem(EnumGroupName)
For Ndx = 1 To .Members.Count
If .Members(Ndx).Value = Value Then
IsValidValue = True
Exit Function
End If
Next Ndx
End With
IsValidValue = False
End Function

This function returns True if Value is defined for EnumGroupName or
False if it is not defined. You would call this function with code
like the following:

Sub ABC()
Dim B As Boolean
B = IsValidValue("XLYesNoGuess", xlYes)
Debug.Print B ' True for xlYes
B = IsValidValue("XLYesNoGuess", 12345)
Debug.Print B ' False for 12345
End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Matthew Herbert]

Chip,

Thanks for the response and for the functions/procedures. Unfortunately, I
don't have the "TypeLib" reference; however, I'm going to do some digging
around to see how I might acquire that .dll in order to leverage this
functionality.

Thanks,

Matt

You can do this ONLY IF you have the TypeLibInfo DLL installed on your
computer. In VBA, go to the Tools menu, choose References, and scroll
down to "TypeLib Info". If this item exists, check it. If it does not
exist, then quit reading because you can't do what you want to do. The
file name of the DLL you need is TLBINF32.dll.

The following code shows how to get the names and values in the
XLYesNoGuess enum:

Sub AAA()
Dim TLIApp As TLI.TLIApplication
Dim TLILibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim N As Long
Dim S As String
Dim ConstName As String

Set TLIApp = New TLI.TLIApplication
Set TLILibInfo = New TLI.TypeLibInfo
Set TLILibInfo = TLIApp.TypeLibInfoFromFile( _
ThisWorkbook.VBProject.References("EXCEL").FullPath)

ConstName = "XLYesNoGuess"
For Each MemInfo In _
TLILibInfo.Constants.NamedItem(ConstName).Members
S = MemInfo.Name
N = MemInfo.Value
Debug.Print S, CStr(N)
Next MemInfo
End Sub

Using this knowledge, you can create two useful functions. EnumNames
returns an array of strings containing the names of the values in an
enum:

Function EnumNames(EnumGroupName As String) As String()
Dim TLIApp As TLI.TLIApplication
Dim TLILibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim Arr() As String
Dim Ndx As Long
Set TLIApp = New TLI.TLIApplication
Set TLILibInfo = New TLI.TypeLibInfo
Set TLILibInfo = TLIApp.TypeLibInfoFromFile( _
ThisWorkbook.VBProject.References("EXCEL").FullPath)
On Error Resume Next
With TLILibInfo.Constants.NamedItem(EnumGroupName)
ReDim Arr(1 To .Members.Count)
For Each MemInfo In .Members
Ndx = Ndx + 1
Arr(Ndx) = MemInfo.Name
Next MemInfo
End With

EnumNames = Arr
End Function


You would call this function with code such as:

Sub ZZZ()
Dim Arr() As String
Dim N As Long
Arr = EnumNames("XLYesNoGuess")
For N = LBound(Arr) To UBound(Arr)
Debug.Print Arr(N)
Next N
End Sub


You can also create a function to test if a value is defined for an
enum:

Function IsValidValue(EnumGroupName As String, Value As Long) As
Boolean
Dim TLIApp As TLI.TLIApplication
Dim TLILibInfo As TLI.TypeLibInfo
Dim MemInfo As TLI.MemberInfo
Dim Ndx As Long
Set TLIApp = New TLI.TLIApplication
Set TLILibInfo = New TLI.TypeLibInfo
Set TLILibInfo = TLIApp.TypeLibInfoFromFile( _
ThisWorkbook.VBProject.References("EXCEL").FullPath)
On Error Resume Next
With TLILibInfo.Constants.NamedItem(EnumGroupName)
For Ndx = 1 To .Members.Count
If .Members(Ndx).Value = Value Then
IsValidValue = True
Exit Function
End If
Next Ndx
End With
IsValidValue = False
End Function

This function returns True if Value is defined for EnumGroupName or
False if it is not defined. You would call this function with code
like the following:

Sub ABC()
Dim B As Boolean
B = IsValidValue("XLYesNoGuess", xlYes)
Debug.Print B ' True for xlYes
B = IsValidValue("XLYesNoGuess", 12345)
Debug.Print B ' False for 12345
End Sub


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

All,

I'm curious to know if there is a way to loop through the constants of an
enumeration. (Or, maybe another way to put this is this: Is there a way to
get an enumeration as a collection?)

I'll illustrate with a simple example below ("TestEnumLoop"), which puts the
constants of XlYesNoGuess into an array, loops through the array, and prints
the constant values to the Immediate Window. (Continue reading after
"TestEnumLoop" for the "hypothetical code").

Sub TestEnumLoop()
Dim varArr As Variant
Dim varItem As Variant
'Dim xlEnum As XlYesNoGuess

varArr = Array(xlGuess, xlNo, xlYes)

For Each varItem In varArr
Debug.Print varItem
Next varItem

End Sub

So, I'm curious to know if there is some way to loop through the
"collection" of constants for a given enumeration. For example, is there
something that would exist for the "hypothetical code" below?

Sub HypotheticalLoop()
Dim xlEnum As XlYesNoGuess

For Each xlEnum In XlYesNoGuess
Debug.Print xlEnumTest
Next xlEnum

End Sub

Thanks,

Matthew Herbert

.