有没有办法计算 VBA 枚举中的元素？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16193458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to count elements in a VBA enum?
提问by BuZz
is there a proper way to count elements of an enum in VBA ?
At the moment, I leave an enum value such as KeepThisOneHerein the following example
有没有一种正确的方法来计算 VBA 中枚举的元素？目前，我留下了一个枚举值，例如KeepThisOneHere在下面的例子中
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.
我使用最后一个值来知道大小......我不喜欢这个解决方案，因为我不确定我是否能保证这些值总是以相同的方式被索引，并且代码可能会被第三方更改可能会在最后一个特殊值之后添加值，默默地破坏其余的代码。
采纳答案by sous2817
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:
不确定这里的礼仪，所以我会发布它，如果有人建议，我会回来删除它。Chip Pearson 在 Code Cage 论坛 ( http://www.thecodecage.com/forumz/microsoft-excel-forum/170961-loop-enumeration-constants.html)上发布了此代码。我的机器上没有 TypeLinInfo DLL，所以我无法对其进行测试（我确信 google 会找到下载 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.
仅当您的计算机上安装了 TypeLibInfo DLL 时，您才能执行此操作。在 VBA 中，转到“工具”菜单，选择“引用”，然后向下滚动到“TypeLib 信息”。如果此项目存在，请检查它。如果它不存在，那就停止阅读，因为你不能做你想做的事。您需要的 DLL 的文件名是 TLBINF32.dll。
The following code shows how to get the names and values in the XLYesNoGuess enum:
以下代码显示了如何获取 XLYesNoGuess 枚举中的名称和值：
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:
使用这些知识，您可以创建两个有用的函数。EnumNames 返回一个包含枚举值名称的字符串数组：
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:
如果为 EnumGroupName 定义了 Value，则此函数返回 True；如果未定义，则返回 False。您可以使用如下代码调用此函数：
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]
诚挚地，Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [网站上的电子邮件]
回答by pstraton
Here's an example of my workaround, which is pretty straightforward:
这是我的解决方法的示例，非常简单：
Enum FileSpecFields
FileSpecFields_Start '(zero-based)
FileNameIdx = FileSpecFields_Start
FolderNameIdx
BasePathIdx
FullPathIdx
CopyStatus
FileSpecFields_End = CopyStatus
End Enum
'...
ReDim FileSpecList(1 To MaxFiles, FileSpecFields_Start To FileSpecFields_End) As String
'...
But note that, if you are using a one-based Enum you may have to adjust the _End value definition, depending on how you're using it. Also, for zero-based Enums, the _End value is not the same as its count of items. And, if you add items at the end, you must update the _End value's definition accordingly. Finally, if your enum is a non-contiguous range of values, all bets are off with this approach!
但请注意，如果您使用基于 1 的 Enum，您可能需要调整 _End 值定义，具体取决于您的使用方式。此外，对于从零开始的枚举，_End 值与其项目数不同。而且，如果在最后添加项目，则必须相应地更新 _End 值的定义。最后，如果您的枚举是一个不连续的值范围，那么所有的赌注都不会使用这种方法！
回答by Our Man in Bananas
there isn't a way to get the count.
没有办法得到计数。
What you have to do is loop through the elements of the Enum until you get to the last one.
您需要做的是遍历 Enum 的元素，直到到达最后一个。
Chip Pearson has some good tips on Enumerated constants: Chip Pearson: Enum Variable Type
Chip Pearson 有一些关于枚举常量的好技巧：Chip Pearson: Enum Variable Type
回答by Andreas Dietrich
If you know the enum type(s) on design-timeyou could transform them into a Static Property Get MyEnumColl() as Collection ...(no class needed, initialized on 1st access statically) and thus easily loop through them or count them like shown here
如果你知道在设计时的枚举类型，你可以将其转化为一个Static Property Get MyEnumColl() as Collection ...（无级需要，在第一次访问静态初始化），因此很容易地遍历他们还是指望他们像显示在这里
