excelvbaenums

Is there a way to count elements in a VBA enum?


is there a proper way to count elements of an enum in VBA ? At the moment, I leave an enum value such as KeepThisOneHere in the following example

Enum TestEnum
   ValueA
   ValueB
   ValueC
   KeepThisOneHere
End Enum

I use the last value to know the size... I don't like this solution, because I am not sure I have a guarantee the values will always be indexed the same way, and the code might be changed by a third party who might add values after this last special one, silently breaking the rest of the code.


Solution

  • 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]