Is there a way to count elements in a VBA enum?
Not sure on the etiquette here, so I'll post it and if advised, I'll come back and delete it. Chip Pearson posted this code on the Code Cage Forums (http://www.thecodecage.com/forumz/microsoft-excel-forum/170961-loop-enumeration-constants.html). I don't have the TypeLinInfo DLL on my machine, so I can't test it (I'm sure google will turn up places to download TLBINF32.dll). Nonetheless, here is his entire post to save someone else from registering for a forum:
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]