Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Enumeration Constants
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Enumeration Constants
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 "Matthew Herbert" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Enumeration Constants
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").FullPat h) 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").FullPat h) 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").FullPat h) 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] On Sun, 17 Jan 2010 22:45:01 -0800, Matthew Herbert wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Enumeration Constants
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 "Chip Pearson" wrote: 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").FullPat h) 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").FullPat h) 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").FullPat h) 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] On Sun, 17 Jan 2010 22:45:01 -0800, Matthew Herbert wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
enumeration members | Excel Worksheet Functions | |||
enumeration by vba or by matric or both | Excel Programming | |||
VSTO Cell Alignment Enumeration | Excel Programming | |||
Enumeration | Excel Programming | |||
Using enumeration with controls | Excel Programming |