[VBA] Collection 완벽 가이드

이 포스트는 Excel Macro Mastery 사이트의 'The Ultimate Guide To Collections in Excel VBA(by Paul Kelly)'의 내용을 정리한 것입니다. 이번 포스트에서는 엑셀 VBA의 Collection 자료구조에 대해 다룹니다.

Collection 사용법 요약

동작	예제
선언	Dim coll As Collection
런타임에 생성	Set coll = New Collection
선언과 생성	Dim coll As New Collection
아이템 추가	coll.Add "Apple"
아이템에 접근	coll(1) or coll(2)
첫 번째 아이템에 접근	coll(1)
마지막 아이템에 접근	coll(coll.Count)
아이템 갯수	coll.Count
For를 이용해 모든 아이템 순회	Dim i As Long For i = 1 To coll.Count     Debug.Print coll(i) Next i
For Each를 이용해 모든 아이템 순회	Dim fruit As Variant For Each fruit In coll     Debug.Print fruit Next fruit
아이템 삭제	call.Remove(i)
모든 아이템 삭제	Set call = New Collection

들어가며

Collection 자료구조는 VBA에서 매우 자주 사용되는 중요한 부분입니다. VBA에서 Collection을 이용한 대표적인 컴포넌트로는 Workbooks, Worksheets, Range와 Cell 이 있습니다.

다음 예제는 Workbook을 사용하는 방법을 간략히 보여 줍니다.

' Workbooks는 모든 열려 있는 워크북들을 저장하고 있는 Collection 이다.

' Count 는 Collection에 있는 워크북들의 개수를 나타낸다.
Debug.Print Workbooks.Count

' Example.xlsm 워크북의 풀네임을 출력한다.
Debug.Print Workbooks("Example.xlsm").FullName

' 열려 있는 워크북중 두번째 워크북의 풀네임을 출력한다.
Debug.Print Workbooks(2).FullName

Collection이란?

Collection과 배열은 동일한 데이터 타입의 변수들을 한 곳에 저장하기 위해 사용되는 대표적인 자료구조 입니다. 우리는 Collection과 배열을 이용해 학생들의 성적이나 나라들의 이름과 같은 동일한 속성을 가진 데이터들을 저장할 빠르고 쉽게 저장할 수 있으며 쉽게 관리할 수 있습니다.

배열에 대한 자세한 내용은 '[VBA] 배열 완벽 가이드' 에서 따로 다루고 있으니 참고 바랍니다.

만일 여러분이 학생 한명의 성적을 저장하고자 한다면 간단히 아래와 같이 변수 하나를 생성하여 저장하면 됩니다.

Dim mark As Long
mark = sheetMarks.Range("A1")

하지만 대부부의 경우 학생 하나의 성적만을 처리하지는 않습니다. 여러분이 100명의 학생의 성적을 관리하는 VBA 프로그램을 만든다고 상상해 보십시오. 여러분이 Collection이나 배열을 모르고 사용하지 않는다면 여러분은 학생 하나하나의 성적을 저장하기 위해 100개의 변수를 일일이 생생해야 합니다.

여기 또 다른 문제가 있습니다. 학생들의 성적 데이터를 처리하기 위해서는 여러분은 아래 예제 처럼 각각의 변수들에 대해 개별 작업을 해줘야만 합니다. 

' 각각의 성적을 위한 변수들 생성Declare a variable for each mark
Dim score1 As Long
Dim score2 As Long
.
.
.
Dim score100 As Long

' 각 변수들에 워크시트의 데이터를 저장
score1 = sheetMarks.Range("A1")
score2 = sheetMarks.Range("A2")
.
.
.
score100 = sheetMarks.Range("A100")

위 예제에서 보시다시피 지겹고도 반복적인 코드의 연속입니다. 하지만 여러분이 Collection이나 배열 같은 자료구조를 이용한다면 단 하나의 변수 선언과 For 루프를 이용한 일괄작업을 처리할 수 있습니다.

For 루프에 대한 자세한 내용은 '[VBA] For 루프 완벽 가이드'에서 따로 다루고 있으니 참고 바랍니다.

Collection과 For 루프를 이용해 위의 예제와 똑같은 동작을 하지만 더 간단한 코드를 작성해 보도록 하겠습니다.

' Collection 객체 생성
Dim collScores As New Collection

' 워크시트로 부터 100개의 값을 읽어 Collection에 저장
Dim c As Range
For Each c In Sheet1.Range("A1:A100")
    collScores.Add c.Value
Next

Collection vs 배열?

앞에서 Collection과 배열에 대해 언급했습니다. 둘 다 동일한 여러 변수들을 한 곳에 저장할 수 있다는 공통점이 있습니다. 그렇다면 둘의 차이점은 무엇일까요?

가장 대표적인 차이는 배열은 생성 될 때 고정된 크기를 가지는 반면 Collection은 가변적인 크기를 가집니다. 예를 들어 크기가 10인 배열이라면 저장할 수 있는 갯수는 최대 10까지이며, 저장하고 있는 아이템이 10개가 안되더라도 항상 10이라는 크기를 유지 합니다. 하지만 Collection의 경우는 저장하는 아이템의 개수가 늘어나는 만큼 동적으로 사이즈를 증가하고 줄어드는 만큼 사이즈를 감소 시킵니다.

배열은 고정 크기, Collection은 가변 크기

배열이 유용한 경우

여러분이 아래와 같은 학생 성적 워크시트를 가지고 있다고 상상해 봅시다.

시험은 이제 다 끝났고 점수가 학생이 추가되거나 줄어들 일은 없습니다. 다시 말해 여러분은 몇명의 학생이 있는지 미리 알 수 있다는 뜻이고 이것은 고정 크기를 가진 자료 구조를 사용할 수 있다는 뜻입니다.

아래 예제는 워크시트에서 총 몇개의 행이 있는지 세고 그만큼 사이즈를 가진 배열을 생성하는 것을 보여주고 있습니다.

'마지막 행 얻기. 이것은 학생들의 숫자를 의미한다
Dim studentCount As Long
studentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    
studentCount = studentCount - 1 '컬럼 이름행 제외
    
'배열 생성하고 사이즈 지정하기
Dim arr() As Long
ReDim arr(1 To studentCount)

배열은 데이터의 개수가 고정적일 때 유용하다

Collection이 유용한 경우

앞의 배열과 달리 데이터의 갯수를 예측할 수 없고 동적으로 변경 되는 경우라면 Collection을 사용하는 것이 유리 합니다. 예를 들어 오늘 팔린 과일들의 매출을 집계 한다고 가정해 봅시다. 그날 그날 어떤 종류의 과일이 팔릴지 모르고 계절에 따라 과일의 종류늘 늘어날수도 줄어 들 수도 있습니다. 따라서 어떤 크기의 배열을 생성할지 결정하기 어렵습니다.

물론 가능한 가장 큰 크기의 배열을 만들 수 있습니다. 문제는 그렇게 되는 경우 많은 배열 공간들이 낭비 되고 이를 처리하기 위해 별도의 코드를 추가해야 한다는 것입니다. 또는 배열을 해제하고 다시 할당하는 식으로 크기를 조절하는 방법도 있습니다만 이는 크기가 변경 될때 마다 대량의 해제와 할당이 필요하다는 점에서 매우 비효율 적이며 수행하기도 까다롭습니다.

이렇게 동적으로 크기가 변경되는 경우엔 Collection 을 사용하는 것이 더 좋습니다.

' 선언
Dim coll As New Collection

' 아이템 추가. VBA가 알아서 Collection 사이즈를 조절해 줍니다.
coll.Add "Apple"
coll.Add "Pear"

' 아이템 삭제. 역시 VBA가 알아서 사이즈를 조절해 줍니다.
coll.Remove 1

여러분이 Collection에 값을 추가하거나 삭제 할 때마다 VBA가 알아서 Collection 객체의 사이즈를 조절해 줍니다. 아이템을 추가하거나 삭제할 때 메모리를 추가 할당하거나 삭제하는 것을 여러분이 신경 쓸 필요가 없습니다.

데이터의 개수의 변동이 잦을 때 Collection 이 유용하다

Collection 의 단점

Collection은 읽기 전용 자료 구조입니다. 컬렉션이 기본 데이터 타입(예: 문자열, 날짜, 정수 등)은 읽기 전용입니다. 아이템을 추가하거나 삭제할 순 있지만 아이템의 값을 변경할 수는 없습니다. 아이템의 값을 변경하기 위해서는 배열을 사용해야 합니다.

Collection은 Read-Only다

Collection 생성

아래는 Collection을 선언하고 생성하는 코드 입니다.

' 동시 선언과 생성
Dim coll As New Collection

' 선언과 생성 분리
' 선언
Dim coll As Collection
' 생성
Set coll = New Collection

보시다시피 선언과 생성을 동시에 진행할 수도 있으며 선언과 생성을 분리할 수도 있습니다. 이러한 방법의 차이점은 첫 번째 방법의 경우 항상 Collection 객체가 생성된다는 것입니다. 두번 째 방법은 컬렉션은 Set ..  New 라인에 도달했을 때만 생성됩니다. 따라서 조건이 충족되는 경우에만 메모리를 할당하여 객체를 생성하도록 할 수 있습니다.

' 선언
Dim coll As Collection

' 파일을 찾았을 경우에만 객체 생성
If filefound = True Then
    Set coll = New Collection
Endif

이러한 방법은 컴퓨터 메모리가 제한적이었던 1990년대에는 중요했지만 요즘 같이 메모리가 풍부한 환경에서는 큰 차이를 가지지 못합니다.

Collection에서 모든 아이템 삭제

Collection은 RemoveAll과 같은 함수가 없습니다. Collection의 모든 아이템을 삭제 하기 위해서는 단순히 새로운 Collection을 다시 할당하면 됩니다.

Set Coll = New Collection

기존의 Collection 객체는 더 이상 참조 되지 않으므로 VBA는 기존 객체를 삭제하게 됩니다.

Sub DeleteCollection()

    Dim coll1 As New Collection
    
    coll1.Add "apple"
    coll1.Add "pear"
    
    ' The original collection is deleted
    Set coll1 = New Collection

End Sub

한 가지 주의해야 할 점은 동일한 Collection을 참조하는 두 개 이상의 변수가 있으면 삭제 되지 않는다는 것입니다. 아래 예에서 원본 Collection 항목은 coll2에서 참조 되고 있기 때문에 삭제되지 않습니다.

Sub CollectionNotDeleted()

    Dim coll1 As New Collection, coll2 As Collection
    
    coll1.Add "apple"
    coll1.Add "pear"
    
    ' Coll1 과 Coll2 모두 하나의 collection 객체를 참조하고 있습니다.
    Set coll2 = coll1
    
    ' Coll1 에 새로운 Collection이 할당 되어 새로운 객체를 참조 합니다.
    Set coll1 = New Collection
    
    ' Coll2 은 여전히 원래 Collection을 참조하고 있기 때문에 coll1은 계속 남아 있습니다.
    Debug.Print coll2(1)

End Sub

Collection에 아이템 추가

Collection 에 새로운 아이템을 추가하는 방법은 간단합니다. 단순히 Add 프로퍼티 다음에 추가 하려고하는 값을 작성해주면 됩니다.

collFruit.Add "Apple"
collFruit.Add "Pear"

여러분은 double과 같은 어떠한 기본타입이라도 Collection에 저장 할 수 있습니다.

   collTotals.Add 45.67
   collTotals.Add 34.67

이러한 방식으로 항목을 추가하면 사용 가능한 다음 인덱스에 자동으로 추가 됩니다. 위의 과일 예에서 Apple은 1번 인덱스에 추가 되고 Pear은 2번 인덱스에 추가 됩니다.

Before 와 After

여러분은 Add 프로퍼티를 사용할 때 아이템이 추가 되는 위치를 지정하기 위해 Before 또는 After 파라메터를 이용할 수 있습니다. 단 Before와 After는 동시에 사용할 수는 없습니다.

collFruit.Add "Apple"
collFruit.Add "Pear"

' Lemon을 1번 인덱스 앞, 즉 첫번째 아이템의 앞에 추가
collFruit.Add "Lemon", Before:=1

위 코드를 실행 시킨 후 collFruit 컬렉션에 들어 있는 아이템은 아래의 순서로 저장 됩니다.

• Lemon
• Apple
• Pear

collFruit.Add "Apple"
collFruit.Add "Pear"

' 1번 인덱스 뒤에 Lemon을 저장합니다
collFruit.Add "Lemon", After:=1

이번에는 아래와 같은 순서로 저장 됩니다.

• Apple
• Lemon
• Pear

Collection 아이템에 접근하기

Collection에 저장되어 있는 아이템들에 접근하기 위해서는 단순히 인덱스를 사용하면 됩니다. 아래에서 보시다시피 인덱스란 아이템이 Collection에 저장되어 있는 순서를 가리키는 숫자입니다.

Sub access()

    Dim coll As New Collection

    coll.Add "Apple" ' 1번 인덱스로 저장
    coll.Add "Pear"  ' 저장 순서에 따라 이번에는 2번 인덱스로 저장

    ' Apple이 출력 된다
    Debug.Print coll(1)

    ' 1번 인덱스, 즉 Apple 앞에 저장
    coll.Add "Orange", Before:=1

    ' 이번에는 Orange가 출력 된다
    Debug.Print coll(1)

    ' Apple 앞에 Orange를 추가했으므로 Apple는 2번 인덱스로 변경 되었다
    Debug.Print coll(2)

End Sub

여러분은 단순 인덱스 뿐 아니라 Item 프로퍼티를 통해서도 Collection에 저장되어 있는 아이템에 접근할 수 있습니다. 이것은 Collection의 기본 메소드이므로 아래는 둘 다 같은 결과를 출력합니다.

    Debug.Print coll(1)
    Debug.Print coll.Item(1)

Collection은 읽기 전용이다?

이번 섹션은 매우 중요한 부분이므로 집중해서 읽어 주십시오. Long, Double, String과 같은 기본 데이터 타입이 Collection에 저장 될 때 Read-Only로 저장됩니다. 만일 이런 데이터들을 수정하려고 시도하면 VBA는 런타임 오류를 발생 시킵니다. 

Sub WriteValue()

    Dim coll As New Collection

    coll.Add "Apple"

    ' This line causes an ERRROR
    coll(1) = "Pear"

End Sub

위 코드를 실행하면 '424 런타임 오류가 발생하였습니다. 개체가 필요합니다' 에러 메시지를 보실 수 있습니다.

하지만 클래스 객체, 즉, 참조 타입의 변수인 경우는 정상적으로 값을 수정하는 것이 가능합니다.

Sub ChangeObject()

    Dim coll As New Collection
    Dim o As New Class1
    
    ' 클래스의 멤버 변수에 값을 설정
    o.fruit = "Apple"
    
    ' 클래스 객체를 collection에 Add
    coll.Add o
    
    ' Apple가 출력 된다
    Debug.Print "Object fruit is " & coll(1).fruit
    
    ' collection이 저장하고 있는 클래스 객체의 값을 수정하려고 시도
    coll(1).fruit = "Pear"
    
    ' Pear라고 에러 없이 출력 된다.
    Debug.Print "Object fruit is " & coll(1).fruit

End Sub

이것은 얼핏 말이 안되는것 처럼 보이지만 그럴만한 이유가 있습니다. Collection에 추가 된 모든 항목은 읽기 전용입니다. 이것은 일반 타입을 저장하든 클래스 타입을 저장하든 동일하게 적용되는 규칙입니다.

다만, '일반 타입'을 저장하는 경우 해당 변수의 값 자체가 Collection에 저장됩니다. 하지만 클래스 타입의 변수를 저장하는 경우 클래스 객체 자체가 저장되는 것이 아니라 클래스 객체의 주소를 가리키고 있는 참조 변수가 저장 됩니다. 위 예제에서 수정한것은 Collection이 저장하고 있는 아이템을 수정한 것이 아니라 아이템이 가리키고 있는 객체의 값을 수정한 것이라서 허용이 됩니다.

쉽게 정리하면 Collection에서는 coll(1) = "Apple" 와 같은 직접 수정은 안됩니다. coll(1).fruit = "Apple"와 같이 . 이 찍혀있는(참조 변수) 경우만 수정이 가능합니다.

Collection에 다양한 타입 저장하기

Collection은 타입에 상관 없이 다양한 타입의 변수들을 저장할 수 있습니다. 아래 예제는 하나의 Collection에 여러 다른 종류의 타입 변수들을 저장하는 것을 보여주고 있습니다.

collFruit.Add "Apple"      ' String 타입
collFruit.Add 45           ' Long 타입
collFruit.Add #12/12/2017# ' Date 타입

일반적으로 하나의 Collection에 서로 다른 타입을 저장하는 경우는 많이 없지만, VBA의 Sheets 컬렉션 객체 경우 Worksheet와 Chart 시트를 동시에 저장하고 있습니다(Sheets 컬렉션에 대한 설명은 [여기]를 참고 하세요).

아래 코드는 현재 워크북에 있는 시트들의 이름과 타입을 보여주빈다. 주의해야 할 점은 다른 타입 변수들을 저장하고 있는 Collection 객체를 For Each로 접근하기 위해서는 Variant 타입의 변수를 사용해야만 합니다.

Sub ListSheets()

    Dim sh As Variant
    For Each sh In ThisWorkbook.Sheets
        ' Display type and name of sheet
        Debug.Print TypeName(sh), sh.Name
    Next

End Sub

만일 위의 예에서 sh 변수를 Worksheet 타입으로 선언했다면 Chart 타입의 아이템에 접근 할 때 오류를 발생 시킬 것입니다.

 

다시 한번 말씀 드리지만 위와 같이 하나의 Collection에 서로 다른 여러 타입을 섞어서 저장하는 것은 자주 사용되지도 않으며 권장하는 방법도 아닙니다. 하지만 아주 가끔 저렇게 사용하면 매우 유용한 경우가 있을 수도 있습니다.

Collection에 Key - Value 형식으로 아이템 저장하기

앞선 내용에서는 Collection의 Add 프로퍼티에 단순히 값만을 전달하여 저장했습니다. 하지만 Key - Value 쌍으로 저장하여 추후 아이템에 접근할 때 인덱스가 아닌 Key를 이용해 접근할 수도 있습니다.

collMark.Add Item:=45, Key:="Bill"

Debug.Print "Bill's Marks are: ",collMark("Bill")

위 예제에서는 Add 프로퍼티를 호출 할 때 명시적으로 Key를 지정하고 있습니다. 그리고 아이템에 접근할 때도 인덱스가 아닌 Key를 이용해 접근하는 것을 볼 수 있습니다.

 Sub UseKey()

    Dim collScore As New Collection

    collScore.Add 45, "철수"
    collScore.Add 67, "영희"
    collScore.Add 12, "동률"
    collScore.Add 89, "영만"

    ' 철수의 성적 출력
    Debug.Print collScore("철수")

    ' 영희의 성적 출력
    Debug.Print collScore("영희")

End Sub

인덱스 대신 Key를 이용해 아이템에 접근하는 방법은 다음과 같은 장점이 있습니다.

• 순서가 변경되더라도 여러분의 코드는 여전히 키가 가리키는 아이템에 접근할 수 있습니다.
• 전체 컬렉션을 순회하지 않더라도 원하는 아이템에 바로 접근할 수 있습니다.
• 코드의 가독성을 높여 줍니다.

키를 이용하여 접근하는 좋은 예로, VBA의 Workbooks 컬렉션의 각 워크북들에 접근하기 위해서는 인덱스를 사용한는것 보다 키(이름)을 이용해 접근하는 해야합니다. 워크북의 인덱스 순서는 워크북을 여는 순서에 따라 달라지기 때문에 인덱스를 이용한다면 같은 인덱스라도 매번 다른 워크북을 가져 올 수가 있습니다.

Sub UseAWorkbook()

    Debug.Print Workbooks("Example.xlsm").Name
    Debug.Print Workbooks(1).Name ' 워크북 여는 순서에 따라 매번 달라진다
    
End Sub

키를 이용 할 때 단점

VBA에서 Collection에 Key를 이용할 때 주의해야 할 단점은, 참조하는 키가 Collection에 존재하는지 알려주는 프로퍼티가 없다는 것입니다. 하지만 이 문제는 아래와 같이 쉽게 해결 될 수 있습니다. 다음 코드는 Collection에 키가 존재하는지 확인 합니다.

' 키 존재 여부 검사 함수
Function Exists(coll As Collection, key As String) As Boolean

    On Error Goto EH

    IsObject (coll.Item(key))
    
    Exists = True
EH:
End Function

' 사용
Sub TestExists()

    Dim coll As New Collection
    coll.Add Item:=5, key:="Apple"
    coll.Add Item:=8, key:="Pear"
    
    ' Prints true
    Debug.Print Exists(coll, "Apple")
    ' Prints false
    Debug.Print Exists(coll, "Orange")
    ' Prints true
    Debug.Print Exists(coll, "Pear")
    
End Sub

Collection의 모든 아이템 순회하기

Collection의 모든 아이템들을 순회하기 위해서 여러분은 For 루프 또는 For Each 루프를 사용할 수 있습니다.

For 루프 사용하기

일반적인 For 루프에서 각 아이템에 접근하기 위해서 인덱스를 사용합니다. 아래 코드는 모든 열려 있는 워크북들의 이름을 출력합니다.

Sub AllWorkbook()

    Dim i As Long
    For i = 1 To Workbooks.Count
        Debug.Print Workbooks(i).Name
    Next i

End Sub

위 예제에서 워크북들의 인덱스를 얻기 위해 1 부터 Workboos.Count까지 순회하고 있는 것을 볼수 있습니다. 첫번째 아이템의 인덱스는 언제나 1이고 가장 마지막 아이템의 인덱스는 Collection 객체의 Count 프로퍼티와 같습니다.

유저가 생성한 Collection도 다르지 않습니다.

Sub UserCollection()

    ' Declare and Create collection
    Dim collFruit As New Collection

    ' Add items
    collFruit.Add "Apple"
    collFruit.Add "Pear"
    collFruit.Add "Plum"

    ' Print all items
    Dim i As Long
    For i = 1 To collFruit.Count
        Debug.Print collFruit(i)
    Next i

End Sub

For Each 루프 사용하기

For Each 루프는 For 루프와 달리 인덱스를 사용하지 않습니다. 대신 For Each 루프는 Collection을 처음 부터 끝까지 순회하며 For Each 변수에 Collection 아이템을 담아 전달 합니다.

Sub UseBothLoops()

    ' Declare and Create collection
    Dim collFruit As New Collection

    ' Add items
    collFruit.Add "Apple"
    collFruit.Add "Pear"
    collFruit.Add "Plum"

    ' Print all items using For Each
    Dim fruit As Variant
    For Each fruit In collFruit
        Debug.Print fruit
    Next fruit

End Sub

For vs For Each

앞의 섹션에셔 For와 For Each를 사용하여 Collection을 순회하는 방법에 대해 살펴 보았습니다. 그럼 For와 For Each 루프의 차이는 무엇일까요.

For Each 루프

• 성능적으로 For보다 빠릅니다
• 코드가 더 깔끔(?) 합니다.
• 단방향으로만 순회가 가능합니다(왼쪽에서 오른쪽, 낮은 인덱스에서 높은 인덱스 순서)

For 루프

• 상대적으로 For Each 보다 느립니다.
• 코드가 조금 더 복잡합니다.
• 왼쪽에서 오른쪽, 오른쪽에서 왼쪽, 하나씩 건너 뛰면서 접근 등 다양한 방법으로 아이템에 접근 가능합니다.

Collection 정렬

안타깝게도 VBA Collection에는 기본 정렬 기능이 제공되지 않습니다. 대신 아래의  QuickSort를 사용할 수 있습니다.

Sub QuickSort(coll As Collection, first As Long, last As Long)
  
  Dim vCentreVal As Variant, vTemp As Variant
  
  Dim lTempLow As Long
  Dim lTempHi As Long
  lTempLow = first
  lTempHi = last
  
  vCentreVal = coll((first + last) \ 2)
  Do While lTempLow <= lTempHi
  
    Do While coll(lTempLow) < vCentreVal And lTempLow < last
      lTempLow = lTempLow + 1
    Loop
    
    Do While vCentreVal < coll(lTempHi) And lTempHi > first
      lTempHi = lTempHi - 1
    Loop
    
    If lTempLow <= lTempHi Then
    
      ' Swap values
      vTemp = coll(lTempLow)
      
      coll.Add coll(lTempHi), After:=lTempLow
      coll.Remove lTempLow
      
      coll.Add vTemp, Before:=lTempHi
      coll.Remove lTempHi + 1
      
      ' Move to next positions
      lTempLow = lTempLow + 1
      lTempHi = lTempHi - 1
      
    End If
    
  Loop
  
  If first < lTempHi Then QuickSort coll, first, lTempHi
  If lTempLow < last Then QuickSort coll, lTempLow, last
  
End Sub

사용 법은 아래와 같습니다.

Sub TestSort()

    Dim coll As New Collection
    coll.Add "USA"
    coll.Add "Spain"
    coll.Add "Belguim"
    coll.Add "Ireland"
    
    QuickSort coll, 1, coll.Count
    
    Dim v As Variant
    For Each v In coll
        Debug.Print v
    Next
    
End Sub

함수와 프로시져에서 Collection 사용하기

이번 섹션에서는 인자로써 또는 리턴 값으로써 사용되는 Collection에 대해 살펴 보도록하겠다.

Sub/Function의 인자로써 Collection

Collection 객체를 함수나 서브 루틴의 인자로 넘기는 것은 간단히 아래 처럼 하면 됩니다.

Sub UseColl()

    ' collection 객체 생성
    Dim coll As New Collection

    ' 아이템 추가
    coll.Add "Apple"
    coll.Add "Orange"

    ' 서브 루틴에 인자로써 넘기기
    PrintColl coll

End Sub

' 서브 루틴의 인자로 collection 객체 선언
Sub PrintColl(ByRef coll As Collection)

    Dim item As Variant
    For Each item In coll
        Debug.Print item
    Next

End Sub

ByVal 매개변수 vs ByRef 매개변수

바로 앞의 예제에서 우리는 collection 객체를 인자로 받는 프로시져에 ByRef라는 키워드를 사용했습니다. 이는 매개 변수를 참조로써 받는다는 의미이고 다른 옵션으로는 ByVal이 있습니다. 이름에서 유추할 수 있듯이 이는 매개 변수를 값으로써 넘겨 받는 다는 의미 입니다.

우리는 '참조'와 '값'의 차이에 대해 알아야 할 필요가 있습니다.

값으로 전달 된다함은 매개 변수의 복사본이 생성됨을 의미합니다. 즉, 매개 변수가 함수 또는 프로시져 내부에서 변경 되어도 복사본을 변경한 것이기 때문에 프로시져가 끝나고 호출자로 돌아갈 때 매개 변수로 사용 되었던 변수의 값이 변경되지 않습니다.

반대로 참조를 이용해 값을 전달하는 경우는 프로시져 내에서 값을 변경하는 경우 종료 후에 호출자에게 돌아가도 변경된 값이 유지되는 것을 볼 수 있습니다.

다음 예에서 우리는 total이라는 매개 변수를 ByVal과 ByRef 두 가지 타입으로 전달해 보도록 하겠습니다. 여러분은 아래 예제의 실행 결과로 ByRef 매개 변수의 경우 프로시져가 종료된 후에도 변경된 값을 계속 유지 됨을 확인하실 수 있을 겁니다.

Sub PassType()

    Dim total As Long
    total = 100

    PassByValue total
    Debug.Print total ' 100이 출력 된다

    PassByReference total
    Debug.Print total ' 555가 출력 된다

End Sub

Sub PassByValue(ByVal total As Long)
    ' ByVal 매개 변수의 변경은 프로시져 내에서만 유효하다
    total = 555
End Sub

Sub PassByReference(ByRef total As Long)
    ' ByRef는 프로시져가 종료 되어도 변경된 값을 유지한다
    total = 555
End Sub

이상 기본 타입에 대한 ByVal과 ByRef에 대해 살펴 보았습니다. 그런데 Collection의 경우는 ByVal과 ByRef가 일반 타입과는 다르게 동작합니다.

여러분이 Collection 객체를 ByVal을 이용해 프로시져에 매개 변수로 넘기다고 하더라도 그것은 ByRef 처럼 동작합니다. 아래 예제는 둘다 동일하게 인자로 넘어온 collection의 첫번째 아이템을 삭제하며 프로시져가 종료 되더라도 변경 사항은 유지되어 둘다 "Orange" 출력 합니다.

Sub PassCollection()

    Dim item As Variant
    
    Dim coll1 As New Collection
    coll1.Add "Apple"
    coll1.Add "Orange"
    
    PassByValue coll1
    
    Debug.Print coll1(1) ' ByVal이지만 Orange 출력
    
    Dim coll2 As New Collection
    coll2.Add "Apple"
    coll2.Add "Orange"
    
    PassByReference coll2
    
    Debug.Print coll2(1) ' Orange 출력

End Sub

Sub PassByValue(ByVal coll As Collection)
    coll.Remove 1
End Sub

Sub PassByReference(ByRef coll As Collection)
    coll.Remove 1
End Sub

매개 변수를 넘기는 방법과 상관 없이 Collection을 변경할 수 있는 이유는 Collection 변수는 사실 Collection 객체 자체가 아니라 메모리 어딘가에 있는 Collection 객체를 가리키고 있는 참조일 뿐이기 때문입니다. 이것은 매개 변수가 Collection 객체의 주소를 가지고 있다는 것을 의미합니다. 따라서 매개 변수로 넘어온 Collection 객체에 아이템을 추가하거나 삭제한다는 것은 변수가 가리키고 객체를 수정하는 것이므로 프로시져가 종료 되어도 그대로 유지 됩니다.

여러분은 참조니 값이니 이런 단어에 대해 크게 신경 쓰실 필요 없습니다. 단, 여러분은 ByVal과 ByRef가 매개 변수 전달 동작에 어떤 영향을 미치는지 알아야 합니다. 아래 예제는 Collection 매개 변수의 프로퍼티를 호출하는 것이 아니라 변수 자체를 변경하고 있습니다. 

' 원본 컬렉션을 비웁니다.
Sub PassByRef( ByRef coll As Collection)
    Set coll = Nothing 
End Sub 

' 원본 컬렉션을 비우지 않습니다. 
Sub PassByVal( ByVal coll As Collection)
    Set coll = Nothing 
End Sub

첫번째  ByRef로 전달된 coll 매개 변수에 Nothing을 대입하므로써 coll 변수가 가리키는 객체를 무효화 했습니다. 이제 이 프로시져가 끝나더라도 coll 은 아무것도 가리키지 않습니다. 반대로 ByVal로 전달된 두 번째는 coll에 Nothing을 대입하여 참조를 무효화 시키더라도 이는 원본 참조를 복사한 것이기 때문에 프로시져가 종료되고 호출자에게 돌아가더라도 원본 참조는 그대로 원래의 Collection 객체를 가리키고 있을것 입니다.

Collection 변수는 객체를 가리키고 있는 참조를 가지고 있다.

Collection 객체 리턴

함수에서 Collection을 반환하는 것은 객체를 반환하는 것과 같습니다. 함수에서 Collection 객체를 반환하기 위해서는 Set 키워드를 사용해야 합니다.

VBA  객체에 대한 할당에 대한 설명은 [여기]를 참고 하세요.

Sub FruitReport()
    ' NOTE: Collection 객체 생성을 위해 'New' 키워드를 사용하지 않았습니다.
    ' collection 객체는 CreateCollection 함수에서 생성되어 리턴 될 것입니다.
    Dim coll As Collection

    ' CreateCollection 함수로 부터 collection 객체를 리턴 받습니다.
    Set coll = CreateCollection

    ' 리턴 받은 coll을 이용해 여기서 뭔가를 합니다.

End Sub

Function CreateCollection() As Collection

    Dim coll As New Collection

    coll.Add "Plum"
    coll.Add "Pear"

    ' collection 객체를 리턴합니다.
    Set CreateCollection = coll

End Function

위 예에서 우리는 FruitReport 함수 내부에서 Collection 객체를 선언 할 때 New 키워드를 사용하지 않았습니다. Collection 객체는 CreateCollection 함수 내부에서 생성되며 리턴 할 때는 객체를 변수에 할당하는 것과 같이 Set 키워드를 사용한 것을 볼 수 있습니다.

마치며

Collection 은 배열과 함께 VBA에서 데이터를 저장하는 대표적인 자료구조 입니다. Collection은 동적인 크기를 가지며 추가 되거나 삭제되는 아이템의 개수를 예측하기 어려울 때 사용하면 좋습니다. 보다 사용하기 쉬우며 아이템의 추가와 삭제가 빈번하며 개수가 예측하기 어려울 때 사용하면 좋습니다.

1. Collection은 여러 변수를 하나의 변수에 저장할 수 있는 집합 자료 구조입니다.
2. VBA 에는 Workbooks, Worksheets, Cells와 같은 자체 컬렉션이 있습니다.
3. Collection에 저장되는 아이템들은 모두 같은 타입이어야 할 필요는 없지만 일반적으로 같은 타입을 하나의 Collection에 저장합니다. VBA 에 sheets 컬렉션에는 워크시트와 차트 시트가 모두 포함 되어 있습니다.
4. Collection을 사용하면 여러 항목에 대해 동일한 작업을 쉽게 수행할 수 있습니다.
5. Collection은 유사한 타입의 아이템들을 저장하는 그룹이라는 점에서 배열과 비슷합니다.
6. Collection은 많은 항목을 추가하고 제거할 때 더 좋습니다.
7. Collection은 배열보다 사용하기 쉽습니다.
8. 배열은 아이템의 갯수가 고정되어 있을 때 더 유용합니다.
9. 배열은 셀에서 읽고 쓸 때 더 효율적입니다.
10. Collection은 읽기 전용인 반면, 배열은 읽기/쓰기가 가능합니다.
11. Collection 을 생성 할 때 Dim을 사용할 수도, Dim ... Set을 이용할 수도 있습니다.
12. Nothing으로 설정하여 전체 컬렉션 객체를 삭제할 수 있습니다.
13. 컬렉션의 Add 함수와 함께 Before 및 After 인수를 사용하여 컬렉션의 특정 위치에 항목을 추가할 수 있습니다.
14. 컬렉션과 함께 키를 사용하여 항목에 다이렉트로 엑세스 할 수 있습니다.
15. For, For Each 루프를 사용하여 컬렉션의 모든 항목에 액세스 할 수 있습니다. For Each 루프가 성능상 조금 더 낫지만 단방향으로만 진행할 수 있습니다.
16. 컬렉션을 Function 또는 Sub에 대한 인자로 전달할 수 있습니다.
17. 함수에서 컬렉션을 리턴할 수 있습니다.

부록 1. 같이 읽으면 좋은 글

• [VBA] For 루프 완벽 가이드
• [VBA] 객체(Object) 완벽 가이드
• [VBA] 배열 완벽 가이드
• [VBA] Range와 Cell 완벽 가이드
• [VBA] Worksheet 완벽 가이드
• [VBA] Visual Basic for Application