如何在 VBA 中准备 Collection 对象

发布于 2024-12-09 16:15:58 字数 627 浏览 0 评论 0原文

下面的代码是在 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

准备好 vba 集合后对象，我想通过以下方式检索它key，假设我想要键“New York”的值。它应该返回纽约。

原文

Below code is in VB.net, how can i do the same thing with 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 vba collection object, i want to retrieve it by key,Suppose take I want value for the Key "New York". It should return NY.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江挽川 2024-12-16 16:15:58

您不能在 VBA 集合中执行此操作（更新：按照您在 vb.net 中布置的顺序，我注意到 Jean 已重新排序您的参数以满足您对集合的需求），您可以使用字典来做到这一点，请参见下文

字典比集合更高效，用途更广泛，因此我建议这样做

有用的阅读：Patrick Matthews 在 VBA 中使用字典类（以及集合和字典有何不同） <一href="http://www.experts-exchange.com/A_3391.html" rel="nofollow">http://www.experts-exchange.com/A_3391.html

公共子主()
昏暗的斯塔
设置 sta = CreateObject("scripting.dictionary")
sta.添加“纽约”、“NY”
sta.添加“密歇根”、“MI”
sta.添加“新泽西州”、“NJ”
sta.添加“马萨诸塞州”、“MA”
MsgBox sta(“纽约”)
结束子

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

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

回复收藏 0 原文

池木 2024-12-16 16:15:58

以下是如何向 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"

如您所见，参数顺序与 .NET 中的顺序相反。为了避免任何混淆，您可以使用命名参数，例如

sta.Add Item:="NY", Key:="New York"

Here's how to add items to a Collection object and retrieve them by key:

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.

sta.Add Item:="NY", Key:="New York"

回复收藏 0 原文

来日方长 2024-12-16 16:15:58

OtherWise 是创建一个您的类型，例如

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

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

回复收藏 0 原文

~没有更多了~