如何在 VBA 中准备集合对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7729006/
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
How to prepare Collection object in VBA
提问by Raj
Below code is in VB.net, how can i do the same thing with vba
下面的代码在 VB.net 中，我如何用vba做同样的事情
Option Strict On
Imports System.Collections
Public Class Collect
Public Shared Sub Main()
Dim sta As New Collection
sta.Add("New York", "NY")
sta.Add("Michigan", "MI")
sta.Add("New Jersey", "NJ")
sta.Add("Massachusetts", "MA")
End Sub
End Class
After preparing the vbacollection object, i want to retrieve it by key,Suppose take I want value for the Key "New York". It should return NY.
准备好vba集合对象后，我想通过键检索它，假设我想要键“纽约”的值。它应该返回 NY。
回答by brettdj
You can't do this in a VBA Collection (update: in the same order as you have laid out in vb.net, I note Jean has re-ordered your arguments to meet your needs a collection), you can do it with a Dictionary, see below
您不能在 VBA 集合中执行此操作（更新：与您在 vb.net 中的顺序相同，我注意到 Jean 已重新排序您的参数以满足您的需要集合），您可以使用字典见下
Dictionaries are more efficient and more versatile than Collections, so I would recommend going that way
字典比集合更高效、更通用，所以我建议这样做
Useful reading: Patrick Matthews Using the Dictionary Class in VBA (and how Collections and Dictionaries differ) http://www.experts-exchange.com/A_3391.html
Public Sub Main() Dim sta Set sta = CreateObject("scripting.dictionary") sta.Add "New York", "NY" sta.Add "Michigan", "MI" sta.Add "New Jersey", "NJ" sta.Add "Massachusetts", "MA" MsgBox sta("New York") End Sub
有用的阅读：Patrick Matthews 在 VBA 中使用字典类（以及集合和字典的区别）http://www.experts-exchange.com/A_3391.html
Public Sub Main() Dim sta Set sta = CreateObject("scripting.dictionary") sta.Add "New York", "NY" sta.Add "Michigan", "MI" sta.Add "New Jersey", "NJ" sta.Add "Massachusetts", "MA" MsgBox sta("New York") End Sub
回答by Jean-Fran?ois Corbett
Here's how to add items to a Collection object and retrieve them by key:
以下是如何将项目添加到 Collection 对象并通过键检索它们：
Dim sta As Collection
Set sta = New Collection
'syntax is: sta.Add myItem, [myKey]
sta.Add "NY", "New York"
sta.Add "MI", "Michigan"
sta.Add "NJ", "New Jersey"
sta.Add "MA", "Massachusetts"
MsgBox sta.Item("New York") ' Returns "NY"
As you can see, the argument order is the reverse of that in .NET. To avoid any mix-up, you could use named arguments instead, e.g.
如您所见，参数顺序与 .NET 中的相反。为避免混淆，您可以改用命名参数，例如
sta.Add Item:="NY", Key:="New York"
回答by Bruno Leite
OtherWise is create a your Type, for example
其他方式是创建一个你的类型，例如
Public Type City
Name As String
Acron As String
End Type
Sub FillType()
Dim x(3) As City
Dim y As Variant
x(0).Name = "NewYork"
x(0).Acron = "NY"
x(1).Name = "Michigan":x(1).Acron = "MI"
x(2).Name = "New Jersey":x(2).Acron = "NJ"
x(3).Name = "Massachusetts":x(3).Acron = "MA"
For i = LBound(x) To UBound(x)
Debug.Print x(i).Name, x(i).Acron
Next i
End Sub
[]′s
[]的
