[Excel VBA] Dictionary 사용법 정리

 

VBA Dictionary란?

---

Dictionary data type은 key, value쌍으로 이루어진 Collection이다
배열과 비슷하지만 배열과 달리 추가,삭제가 가능하고 Collection 객체와 같은 방법으로 사용이 가능하다

Dictionary 와 비교했을때, Collection의 문제점은
Key값이 이미 등록되었는지 확인이 어렵고,
이미 등록된 item에 대해서 값을 변경할 수 어렵다 .
이러한 관점에서, Dictionary가 보다 유용한 툴이다.

 

VBA Dictionary 사용법

---

Function	Example
Early binding reference	“Microsoft Scripting Runtime” (Add using Tools->References from the VB menu)
Declare (early binding)	Dim dict As Scripting.Dictionary
Create(early binding)	Set dict = New Scripting.Dictionary
Declare (late binding)	Dim dict As Object
Create(late binding)	Set dict = CreateObject("Scripting.Dictionary")
Add item (key must not already exist)	dict.Add Key, Value e.g. dict.Add "Apples", 50
Change value at key. Automatically adds if the key does not exist.	dict(Key) = Value e.g. dict("Oranges") = 60
Get a value from the dictionary using the key	Value = dict(Key) e.g. appleCount = dict("Apples")
Check if key exists	dict.Exists(Key) e.g. If dict.Exists("Apples") Then
Remove item	dict.Remove Key e.g. dict.Remove "Apples"
Remove all items	dict.RemoveAll
Go through all items (for each loop)	Dim key As Variant For Each key In dict.Keys     Debug.Print key, dict(key) Next key
Go through all items (for loop - early binding only)	Dim i As Long For i = 0 To dict.Count - 1    Debug.Print dict.Keys(i),      dict.Items(i) Next i
Go through all items (for loop - early and late binding)	Dim i As Long For i = 0 To dict.Count - 1 Debug.Print dict.Keys()(i), dict.Items()(i) Next i
Get the number of items	dict.Count
Make key case sensitive (the dictionary must be empty).	dict.CompareMode = vbBinaryCompare
Make key non case sensitive (the dictionary must be empty).	dict.CompareMode = vbTextCompare

 

 

 

 

VBA Dictionary를 사용한 간단 예제

---

 

• 3가지 과일타입과 값을 Dictionary에 추가
• 사용자에게 과일의 이름을 물어보기
• 사용자가 입력한 과일이 있는지 검색
• 입력한 과일이 있을 경우, 과일의 이름과 값을 출력
• 입력한 과일이 없을 경우, 입력한 과일이 없다고 알림.

 

' https://excelmacromastery.com/
Sub CheckFruit()

' Select Tools->References from the Visual Basic menu.
' Check box beside "Microsoft Scripting Runtime" in the list.
Dim dict As New Scripting.Dictionary

' Add to fruit to Dictionary
dict.Add key:="Apple", Item:=51
dict.Add key:="Peach", Item:=34
dict.Add key:="Plum", Item:=43

Dim sFruit As String '
Ask user to enter fruit
sFruit = InputBox("Please enter the name of a fruit")

If dict.Exists(sFruit) Then
    MsgBox sFruit & " exists and has value " & dict(sFruit)
Else
    MsgBox sFruit & " does not exist."
End If

Set dict = Nothing

End Sub

 

Dictionary 생성

---

 

Dim dict As New Scripting.Dictionary

또는,

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

 

Dictionary에 Item 추가

---

dict.Add Key:="Orange", Item:=45
dict.Add "Apple", 66
dict.Add "12/12/2015", "John"
dict.Add 1, 45.56

 

등록된 Item에 값 할당

---

' Adds Orange to the dictionary 
dict("Orange") = 45 

' Changes the value of Orange to 100
dict("Orange") = 100

 

Key 가 등록되어있는지 확인

---

 

' Checks for the key 'Orange' in the dictionary
If dict.Exists("Orange") Then
    MsgBox "The number of oranges is " & dict("Orange") 
Else
    MsgBox "There is no entry for Orange in the dictionary."
End If

 

기타 유용한 함수

---

 

• Count  : 등록된 총 Item 수 반환
• Remove : 입력한 Key 값을 삭제
• RemoveAll : 입력한 모든 Item 삭제

 

' https://excelmacromastery.com/
Sub AddRemoveCount()

Dim dict As New Scripting.Dictionary

' Add some items
dict.Add "Orange", 55
dict.Add "Peach", 55
dict.Add "Plum", 55
Debug.Print "The number of items is " & dict.Count

' Remove one item
dict.Remove "Orange"
Debug.Print "The number of items is " & dict.Count

' Remove all items
dict.RemoveAll Debug.Print "The number of items is " & dict.Count

End Sub

 

Key 값으로 정렬(Sorting) 방법

---

SortDictionaryByKey 함수

' https://excelmacromastery.com/
Public Function SortDictionaryByKey(dict As Object
                                 , Optional sortorder As XlSortOrder = xlAscending) As Object

Dim arrList As Object
Set arrList = CreateObject("System.Collections.ArrayList")

' Put keys in an ArrayList
Dim key As Variant, coll As New Collection
For Each key In dict
    arrList.Add key
Next key

' Sort the keys
arrList.Sort

' For descending order, reverse
If sortorder = xlDescending Then
    arrList.Reverse
End If

' Create new dictionary
Dim dictNew As Object
Set dictNew = CreateObject("Scripting.Dictionary")

' Read through the sorted keys and add to new dictionary
For Each key In arrList
    dictNew.Add key, dict(key)
Next key

' Clean up
Set arrList = Nothing
Set dict = Nothing

' Return the new dictionary
Set SortDictionaryByKey = dictNew

End Function

 

SortDictionaryByKey 함수를 사용한 예제

' https://excelmacromastery.com/
Sub TestSortByKey()

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

dict.Add "Plum", 99
dict.Add "Apple", 987
dict.Add "Pear", 234
dict.Add "Banana", 560
dict.Add "Orange", 34

PrintDictionary "Original", dict

' Sort Ascending
Set dict = SortDictionaryByKey(dict)
PrintDictionary "Key Ascending", dict

' Sort Descending
Set dict = SortDictionaryByKey(dict, xlDescending)
PrintDictionary "Key Descending", dict
End Sub

Public Sub PrintDictionary(ByVal sText As String, dict As Object)

Debug.Print vbCrLf & sText & vbCrLf & String(Len(sText), "=")

Dim key As Variant
For Each key In dict.keys
    Debug.Print key, dict(key)
Next

End Sub

 

값으로 정렬하는 방법

---

 SortDictionaryByValue 함수

' https://excelmacromastery.com/
Public Function SortDictionaryByValue(dict As Object
                                 , Optional sortorder As XlSortOrder = xlAscending) As Object

On Error GoTo eh

Dim arrayList As Object
Set arrayList = CreateObject("System.Collections.ArrayList")

Dim dictTemp As Object
Set dictTemp = CreateObject("Scripting.Dictionary")

' Put values in ArrayList and sort
' Store values in tempDict with their keys as a collection
Dim key As Variant, value As Variant, coll As Collection

For Each key In dict
    value = dict(key)

    ' if the value doesn't exist in dict then add
    If dictTemp.exists(value) = False Then
        ' create collection to hold keys
        ' - needed for duplicate values
        Set coll = New Collection
        dictTemp.Add value, coll

        ' Add the value
        arrayList.Add value
    End If
   
    ' Add the current key to the collection
    dictTemp(value).Add key
Next key

' Sort the value
arrayList.Sort

' Reverse if descending
If sortorder = xlDescending Then
    arrayList.Reverse
End If

dict.RemoveAll

' Read through the ArrayList and add the values and corresponding
' keys from the dictTemp
Dim item As Variant
For Each value In arrayList
    Set coll = dictTemp(value)
    For Each item In coll
        dict.Add item, value
    Next item
Next value

Set arrayList = Nothing

' Return the new dictionary
Set SortDictionaryByValue = dict

Done:
    Exit Function
eh:

If Err.Number = 450 Then
    Err.Raise vbObjectError + 100, "SortDictionaryByValue" _
                  ,"Cannot sort the dictionary if the value is an object"
End If

End Function

 

 SortDictionaryByValue 함수를 사용한 예제

' https://excelmacromastery.com/
Sub TestSortByValue()

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

dict.Add "Plum", 99
dict.Add "Apple", 987
dict.Add "Pear", 234
dict.Add "Banana", 560
dict.Add "Orange", 34

PrintDictionary "Original", dict

' Sort Ascending
Set dict = SortDictionaryByValue(dict)
PrintDictionary "Value Ascending", dict

' Sort Descending
Set dict = SortDictionaryByValue(dict, xlDescending)
PrintDictionary "Value Descending", dict

End Sub

Public Sub PrintDictionary(ByVal sText As String, dict As Object)
Debug.Print vbCrLf & sText & vbCrLf & String(Len(sText), "=")

Dim key As Variant
For Each key In dict.keys
    Debug.Print key, dict(key)
Next key

End Sub

 

참고자료

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery