How to prepare Collection object in VBA

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 s开发者_如何学运维hould return NY.

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

1. 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
   

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"

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