【VBA】MacのExcelVBAでもDictionaryオブジェクトを使う方法

Rem Dictionaryオブジェクトが動くかテスト
Sub test()
'Dictionaryオブジェクトは必ず挿入したクラスの名前で定義する。
Dim MyDicObj As Class1

'キーと内容の変数を宣言

Dim myKey As Integer
Dim myValue As String

'新しいDictionaryを作る
Set MyDicObj = New Class1

'キーを1から10までループ
For myKey = 1 To 10
     '内容を作る
     myValue = myKey & "内容"
     'Dictionaryにキーと内容を追加
     MyDicObj.add myKey, myValue
Next myKey

'キーを指定して内容を抽出
MsgBox "5のキーに入っている内容は" & vbNewLine & MyDicObj.items(5) & vbNewLine & "です。"

Set MyDicObj = Nothing
End Sub  

Option Explicit
 
 
Private itemCount As Long
Private dictionaryKeys As New Collection
Private dictionaryValues As New Collection
 
Public Function add(ByVal Key As String, Value As Variant) As Variant
    itemCount = itemCount + 1
    If exists(Key) Then
        add = dictionaryValues.Item(Key)
        dictionaryValues.remove Key
        dictionaryValues.add Value, Key
    Else
        dictionaryKeys.add Key, Key
        dictionaryValues.add Value, Key
        add = Null
    End If
End Function
 
Public Function exists(ByVal Key As String) As Boolean
    Dim aKey
    Dim found As Boolean
    found = False
    For Each aKey In dictionaryKeys
        If aKey = Key Then
            found = True
            Exit For
        End If
    Next aKey
    exists = found
End Function
 
Public Function remove(ByVal Key As String) As Boolean
    If exists(Key) Then
        dictionaryKeys.remove Key
        dictionaryValues.remove Key
        itemCount = itemCount - 1
        remove = True
    Else
        remove = False
    End If
End Function
 
Public Sub removeAll()
    itemCount = 0
    Set dictionaryKeys = New Collection
    Set dictionaryValues = New Collection
End Sub
 
Public Property Get Count() As Long
    Count = itemCount
End Property
 
Public Property Get isEmpty() As Boolean
    If itemCount = 0 Then
        isEmpty = True
    Else
        isEmpty = False
    End If
End Property
 
 
Public Function keys() As Collection
    Set keys = dictionaryKeys
End Function
 
Public Function items() As Collection
    Set items = dictionaryValues
End Function


Public Property Get Item(ByVal Key As String) As Variant
    If exists(Key) Then
        If TypeOf dictionaryValues.Item(Key) Is Object  Then
            Set Item = dictionaryValues.Item(Key)
        Else
            Item = dictionaryValues.Item(Key)
        End If
    Else
        Item = False
    End If
End Property
 
Public Property Let Item(ByVal Key As String, Value As Variant)
    Me.add Key, Value
End Property

 

VBAを使っている方ばかりでしょうから、あまり詳しい説明はいらないと思いますが、クラスモジュールで定義したDictionaryオブジェクトと同じ動きをするクラスモジュールを呼び出すためのテストモジュールを作りましょう。ここはどう使いたいかでどんどん処理を変えてテストしてもらえればと思います。