有没有一种方法可以计算VBA枚举中的元素? [英] Is there a way to count elements in a VBA enum?

问题描述

在VBA中是否有一种正确的方法来枚举枚举的元素?此刻，在下面的示例中，我将保留一个枚举值，例如 KeepThisOneHere

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.

推荐答案

不确定此处的礼节，因此我将其发布，如果有建议，我将返回并删除它.皮尔·皮尔森(Chip Pearson)将此代码发布到了代码笼论坛(http://www.thecodecage.com/forumz/microsoft-excel-forum/170961-loop-enumeration-constants.html ).我的机器上没有TypeLinInfo DLL，因此无法对其进行测试(我确信google会把地方打开来下载TLBINF32.dll).尽管如此，这是他的整个帖子，以免其他人注册论坛:

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:

仅当您在计算机上安装了TypeLibInfo DLL时，才可以执行此操作电脑.在VBA中，转到工具"菜单，选择引用"，然后滚动直到"TypeLib信息".如果存在此项目，请检查它.如果没有存在，然后退出阅读，因为您无法做自己想做的事.这您需要的DLL的文件名为TLBINF32.dll.

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.

以下代码显示了如何在XLYesNoGuess枚举:

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

如果为EnumGroupName或Value定义了Value，则此函数返回True.如果未定义，则为False.您可以使用代码调用此函数类似于以下内容:

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

通常，奇皮尔逊微软MVP 1998-2010培生软件咨询有限公司www.cpearson.com[网站上的电子邮件]

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

这篇关于有没有一种方法可以计算VBA枚举中的元素?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！