Excel VBA 컬렉션

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

Younjung Kim

Last updated on 4월 13, 2023

In this Article

VBA에서 컬렉션이란 무엇일까요?
- 컬렉션과 배열 비교
컬렉션 객체의 사용 범위
컬렉션 만들기, 항목 추가하기, 항목에 엑세스하기

이 튜토리얼에서는 VBA에서 컬렉션을 사용하는 방법을 설명합니다.

VBA에서 컬렉션이란 무엇일까요?

컬렉션은 많은 수의 항목에 쉽게 액세스하고 조작할 수 있는 여러 개의 유사한 항목을 포함하는 객체입니다.

컬렉션을 만들 수도 있지만, VBA에는 통합 문서의 모든 시트를 저장하는 시트 컬렉션과 같은 기본 제공 컬렉션도 함께 제공됩니다. For Each 루프를 사용하면 시트 컬렉션의 각 워크시트를 반복할 수 있습니다.

Sub TestWorksheets()
Dim Sh As Worksheet
For Each Sh In Sheets
    MsgBox Sh.Name
    MsgBox Sh.Visible
Next Sh
End Sub

인덱스 값(컬렉션 내 위치) 또는 워크시트의 실제 이름을 사용하여 컬렉션의 특정 워크시트를 불러올 수도 있습니다:

MsgBox Sheets(1).Name
MsgBox Sheets("Sheet1").Name

워크시트를 추가하거나 삭제하면 시트 컬렉션의 크기가 커지거나 작아집니다.

VBA 컬렉션에서는 인덱스 번호가 0이 아닌 1로 시작한다는 점에 유의하세요.

컬렉션과 배열 비교

배열과 컬렉션은 둘 다 데이터를 저장할 수 있지만 몇 가지 주목할 만한 차이점이 있습니다:

배열은 다차원인 반면 컬렉션은 단일 차원입니다. 다음과 같이 여러 차원으로 배열의 차원을 지정할 수 있습니다.

Dim MyArray(10, 2) As String

이렇게 하면 워크시트처럼 열 2개와 행 10개로 구성된 배열이 만들어집니다. 반면에 컬렉션은 사실상 하나의 열입니다.

배열을 채울 때 배열의 각 요소에 값을 넣으려면 별도의 코드가 필요합니다. 2차원 배열을 사용하는 경우 실제로는 첫 번째 열에 주소를 지정하는 코드 한 줄과 두 번째 열에 주소를 지정하는 코드 한 줄, 총 두 줄의 코드가 필요합니다. 컬렉션 객체에서는 추Add 메서드를 사용하면 새 항목이 컬렉션에 추가되고 인덱스 값이 자동으로 그에 맞게 조정됩니다.
데이터 항목을 삭제할때 배열의 경우가 더 복잡합니다. 요소의 값을 빈 값으로 설정할 수 있지만 요소 자체는 여전히 배열 내에 존재합니다. For Next 루프를 사용하여 배열을 반복하는 경우 루프가 빈 값을 반환하므로 빈 값이 무시되도록 코딩해야 합니다. 컬렉션에서는 Add 또는 Remove 메서드를 사용하면 모든 인덱싱 및 크기 조정이 자동으로 처리됩니다. 제거된 항목은 완전히 사라집니다. 배열은 데이터 크기가 고정된 경우에 유용하지만, 데이터의 양이 변경될 수 있는 경우에는 컬렉션이 더 좋습니다.
컬렉션은 읽기 전용이지만 배열의 항목들은 VBA를 사용하여 변경할 수 있습니다. 컬렉션을 사용하면 변경할 값을 제거한 다음 새로운 값을 추가해야 합니다.
배열에서는 크기를 지정할때 선언한 단일 데이터 유형만 사용할 수 있습니다. 배열에서는 사용자 정의 데이터 유형을 사용할 수 있습니다. 사용자 정의 데이터 유형을 사용하여 여러 유형이 있는 매우 복잡한 배열 구조를 만들수 있습니다. 컬렉션에서 각 항목에 대한 데이터 유형을 추가할 수 있습니다. 컬렉션 객체는 숫자, 날짜 또는 문자열 등 모든 데이터 유형을 사용할 수 있습니다. 숫자 데이터 유형으로 정의된 배열에 문자열 값을 넣으려고 하면 오류 메시지가 표시됩니다.
컬렉션은 일반적으로 배열보다 사용하기 쉽습니다. 코딩 용어로 컬렉션 객체를 만들면 두 가지 메서드(Add와 Remove)와 두 가지 속성(Count와 Item)만 있으므로 객체를 프로그래밍하는 것이 간편합니다.
컬렉션은 키(key)를 사용하여 데이터를 찾을 수 있습니다. 배열에는 이 기능이 없어서 특정 값을 찾으려면 배열을 반복하는 코드가 필요합니다.
배열의 크기는 배열을 처음 만들 때 정의해야 합니다. 배열에 저장할 데이터의 양에 대한 정의가 되어야 합니다. 이후 배열의 크기를 늘려야 한다면 ‘ReDim’을 사용하여 크기를 다시 조정할 수 있습니다. 배열에 이미 저장된 데이터를 잃지 않으려면 ‘ReDim Preserve’ 키워드를 사용해야 합니다. 컬렉션의 경우 크기를 정의할 필요가 없습니다. 항목이 추가되거나 제거될 때 자동으로 커지거나 줄어듭니다.

컬렉션 객체의 사용 범위

사용 범위 측면에서 컬렉션 객체는 통합 문서가 열려 있는 동안에만 사용할 수 있습니다. 통합 문서를 저장할 때는 저장되지 않습니다. 통합 문서를 다시 열면 VBA 코드를 사용하여 컬렉션을 다시 만들어야 합니다.

모듈의 모든 코드에서 컬렉션을 사용할 수 있게 하려면 모듈 창의 상단에 있는 선언 섹션에서 컬렉션 개체를 선언해야 합니다.

이렇게 하면 해당 모듈 내의 모든 코드가 컬렉션에 액세스할 수 있습니다. 통합 문서 내의 모든 모듈에서 컬렉션에 액세스하도록 하려면 컬렉션을 전역 객체로 정의해야합니다.

Global MyCollection As New Collection

컬렉션 만들기, 항목 추가하기, 항목에 엑세스하기

다음 코드를 사용하여 VBA에서 간단한 컬렉션 객체를 만들 수 있습니다:

Sub CreateCollection()
'컬렉션 객체를 생성합니다
Dim MyCollection As New Collection

'Add Items to Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"
End Sub

이 코드는 ‘MyCollection’이라는 새 객체를 생성한 다음 Add 메서드를 사용하여 3개의 값을 추가합니다.

컬렉션 항목 반복하기

아래와 같은 코드를 사용하여 컬렉션의 각 항목을 반복할 수 있습니다:

For Each Item In MyCollection
    MsgBox Item
Next Item

아래 코드는 .Count 속성으로 컬렉션의 크기를 가져와 서각 인덱스 번호를 반복합니다:

For n = 1 To MyCollection.Count
    MsgBox MyCollection(n)
Next n

첫 번째 For Each 루프는 두 번째 For Next 루프보다 빠르지만 한 방향(낮은 인덱스에서 높은 인덱스로)으로만 작동합니다. For Next 루프는 다른 방향(높은 인덱스에서 낮은 인덱스로)을 사용할 수 있고 Step 메서드를 사용하여 증분을 변경할 수 있다는 장점이 있습니다. 높은 인덱스에서 낮은 인덱스로 반복하는 방법은 여러 항목을 삭제할 때 유용합니다. 삭제가 진행됨에 따라 인덱스가 변경되므로 컬렉션의 끝에서 시작까지 삭제를 실행해야 하기 때문입니다.

컬렉션에 항목 추가하기

컬렉션의 Add 메서드에는 Key, Before, After.의 세 가지 선택적 매개변수가 있습니다.

Before와 After매개변수를 사용하여 컬렉션에 이미 있는 다른 항목을 기준으로 새 항목을 추가할 위치를 정의할 수 있습니다.

매개변수에는 새 항목의 기준이 될 인덱스 번호를 지정하면 됩니다.

Sub CreateCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2", , 1
MyCollection.Add "Item3"

End Sub

이 예제에서는 ‘Item2’가 컬렉션의 첫 번째 인덱싱된 항목(‘Item1’) 앞에 추가되도록 지정되었습니다. 이 컬렉션을 반복하여 각 항목들을 표시하면 ‘Item2’가 가장 먼저 표시되고 그 뒤에 ‘Item1’과 ‘Item3’이 표시됩니다.

‘Before’ 또는 ‘After’ 매개변수를 지정하면 컬렉션 내에서 인덱스 값이 자동으로 조정되어 ‘Item2’는 인덱스 값 1이 되고 ‘Item1’은 인덱스 값 2로 이동합니다.

‘Key’ 매개변수를 사용하여 컬렉션 항목을 식별하는 데 사용할 수 있는 참조 값을 추가할 수도 있습니다. 키 값은 문자열이어야 하며 컬렉션 내에서 고유해야 한다는 점에 유의하세요.

Sub CreateCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2", "MyKey"
MyCollection.Add "Item3"

MsgBox MyCollection("MyKey")
End Sub

‘Item2’에는 인덱스 번호(2) 대신 ‘MyKey’ 값을 사용하여 해당 항목을 참조할 수 있도록 ‘Key’ 값이 ‘MyKey’로 지정되었습니다.

‘Key’ 값은 문자열이어야 합니다. 다른 데이터 유형은 사용할 수 없습니다. 컬렉션은 읽기 전용이므로 키 값을 설정한 후에는 업데이트할 수 없습니다. 또한 컬렉션의 특정 항목에 대한 키 값이 존재하는지 확인하거나 키 값을 볼 수 없다는 점도 다소 단점입니다.

‘Key’ 매개변수는 특히 동료와 코드를 공유하고 지원을 받을 경우 가독성을 높이고 해당 값을 찾기 위해 전체 컬렉션을 반복할 필요가 없다는 추가적인 이점이 있습니다. 10,000개의 항목으로 구성된 컬렉션이 있고 키가 없다면 특정 항목 하나를 참조하는 것이 쉽지 않을 것입니다,

컬렉션에서 항목 제거하기

‘Remove’ 메서드를 사용하여 컬렉션에서 항목을 삭제할 수 있습니다.

MyCollection.Remove (2)

안타깝게도 컬렉션에 많은 수의 항목이 있는 경우 삭제하려는 항목의 인덱스를 찾는 것이 쉽지 않습니다. 이때 컬렉션을 만들 때 ‘Key’ 매개변수가 유용하게 사용됩니다.

MyCollection.Remove (“MyKey”)

컬렉션에서 항목이 제거되면 컬렉션 전체에 걸쳐 인덱스 값이 자동으로 재설정됩니다. 한 번에 여러 항목을 삭제할 때 ‘Key’ 매개변수가 매우 유용한 이유입니다. 예를 들어, 항목 인덱스 105를 삭제하면 즉시 항목 인덱스 106이 인덱스 105가 되고 이 항목 위의 모든 항목의 인덱스 값이 아래로 이동합니다. Key 매개변수를 사용하면 어떤 인덱스 값을 제거해야 하는지 걱정할 필요가 없습니다.

모든 컬렉션 항목을 삭제하고 새 컬렉션을 만들려면 Dim 문을 다시 사용하여 빈 컬렉션을 만듭니다.

Dim MyCollection As New Collection

컬렉션 객체를 완전히 제거하려면 개체를 Nothing으로 설정하면 됩니다.

Set MyCollection = Nothing

이 방법은 코드에서 컬렉션이 더 이상 필요하지 않은 경우에 유용합니다. 컬렉션 객체를 Nothing으로 설정하면 컬렉션에 대한 모든 참조가 제거되고 사용 중이던 메모리가 초기화됩니다. 필요하지 않은 큰 객체가 메모리에 있는 경우 코드 실행 속도에 중요한 영향을 미칠 수 있습니다.

컬렉션의 항목 수 계산하기

‘Count’ 속성을 사용하면 컬렉션에 있는 항목 수를 쉽게 확인할 수 있습니다.

MsgBox MyCollection.Count

이 속성은 컬렉션 인덱스 번호의 상한값을 제공하므로 컬렉션을 반복하기 위해 For Next 루프를 사용하는 경우 이 속성을 사용할 수 있습니다..

컬렉션에 특정 값이 있는지 확인하기

컬렉션을 반복하여 항목의 특정 값을 검색하려면 For Each Loop를 사용할 수 있습니다.

Sub SearchCollection()
Dim MyCollection as New Collection

MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"

For Each Item In MyCollection
        If Item = "Item2" Then
            MsgBox Item & " 을 찾았습니다"
        End If
Next
End Sub

이 코드는 컬렉션을 생성한 다음 ‘item2″라는 항목을 찾기 위해 반복합니다. 항목을 찾으면 특정 항목을 찾았다는 메시지 박스를 표시합니다.

이 방법의 단점 중 하나는 인덱스 값이나 키 값에 액세스할 수 없다는 것입니다.

For Next 루프를 사용하면 인덱스 값을 가져올 수 있지만 여전히 ‘키’ 값은 가져올 수 없습니다.

Sub SearchCollection()
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"

For n = 1 To MyCollection.Count
        If MyCollection.Item(n) = "Item2" Then
            MsgBox MyCollection.Item(n) & " found at index position " & n
        End If
Next n
End Sub

For Next 반복문을 사용하면 인덱스 위치(n)를 가져올 수 있습니다.

컬렉션 정렬하기

컬렉션을 정렬하는 기본 제공 기능은 없지만, 창의적인 사고로 Excel의 워크시트 정렬 기능을 활용하여 정렬을 수행하는 코드를 작성할 수 있습니다. 이 코드는 ‘SortSheet’라는 빈 워크시트를 사용하여 실제 정렬을 수행합니다.

Sub SortCollection()
Dim MyCollection As New Collection
Dim Counter As Long

'정렬되지 않은 컬렉션 항목들을 추가합니다
MyCollection.Add "Item5"
MyCollection.Add "Item2"
MyCollection.Add "Item4"
MyCollection.Add "Item1"
MyCollection.Add "Item3"

'컬렉션 항목들의 개수를 계산합니다
Counter = MyCollection.Count

'컬렉션의 각 항목들을 반복하여 'SortSheet'시트 A열의 연속될 셀에 값을 입력합니다
For n = 1 To MyCollection.Count
    Sheets("SortSheet").Cells(n, 1) = MyCollection(n)

Next n
' 'SortSheet'를 활성화하고 Excel의 정렬 기능을 사용하여 데이터를 오름차순으로 정렬합니다
Sheets("SortSheet").Activate
Range("A1:A" & MyCollection.Count).Select
    ActiveWorkbook.Worksheets("SortSheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SortSheet"). Sort.SortFields.Add2 Key:=Range( _
        "A1:A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("SortSheet").Sort
        .SetRange Range("A1:A5")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'컬렉션의 모든 항목을 삭제합니다 - 여기서 For Next 반복문은 역순으로 실행됩니다
For n = MyCollection.Count To 1 Step -1
     MyCollection.Remove (n)
Next n

'시트의 셀 값을 컬렉션 객체에 다시 입력합니다
For n = 1 To Counter
    MyCollection.Add Sheets("SortSheet").Cells(n, 1).Value

Next n

'컬렉션을 반복하여 새로 정렬된 항목의 순서대로 메시지박스에 표시합니다
For Each Item In MyCollection
    MsgBox Item

Next Item

' 'SortSheet'시트의 내용을 삭제합니다 - 필요한 경우 워크시트도 삭제해도 됩니다
Sheets("SortSheet").Range(Cells(1, 1), Cells(Counter, 1)).Clear
End Sub

이 코드는 먼저 임의의 순서로 추가된 항목으로 컬렉션을 만듭니다. 그런 다음 워크시트(SortSheet)의 첫 번째 열에 복사합니다.

그런 다음 Excel 정렬 기능을 사용하여 열의 데이터를 오름차순으로 정렬합니다. 내림차순으로 정렬하도록 코드를 수정할 수도 있습니다.

그런 다음 For Next 반복문을 사용하여 컬렉션에서 데이터를 비웁니다. Step 옵션은 컬렉션의 끝부터 시작 부분으로 지워지도록 합니다. 지워질 때 인덱스 값이 재설정되기 때문이며, 정방향으로 반복문을 진행하여 항목을 지우면 의도되로 지워지지 않습니다(인덱스 2가 인덱스 1이 됨).

For Next 반복문을 사용하여 셀에 정렬된 값을 빈 컬렉션으로 다시 전송합니다.

마지막으로 For Each 반복문을 사용하여 컬렉션에 정렬이 성공적으로 되었음을 메시지 박스에 표시합니다.

안타깝게도 키 값운 읽을 수 없기 때문에 키 값은 처리하지 않았습니다.

컬렉션을 서브/함수에 전달하기

컬렉션은 다른 매개변수와 같은 방식으로 서브프로시저 또는 함수에 전달할 수 있습니다.

Function MyFunction(ByRef MyCollection as Collection)

‘ByRef’를 사용하여 컬렉션을 전달하는 것이 중요합니다. 이는 원본 컬렉션이 사용된다는 것을 의미합니다. 컬렉션이 ‘ByVal’을 사용하여 전달되면 컬렉션의 복사본이 생성되어 원하지 않은 결과를 초래할 수 있습니다.

‘ByVal’을 사용하여 복사본을 생성하면 함수 내에서 컬렉션을 변경하는 모든 작업은 원본이 아닌 복사본에서만 발생합니다. 예를 들어 함수 내에서 컬렉션에 새 항목이 추가되면 원본 컬렉션에는 해당 항목이 표시되지 않으므로 코드에 버그가 발생할 수 있습니다.

함수에서 컬렉션 반환하기

객체를 반환하는 것과 같은 방법으로 함수에서 컬렉션을 반환할 수 있습니다. Set 키워드를 사용해야 합니다.

Sub ReturnFromFunction()
Dim MyCollection As Collection
Set MyCollection = PopulateCollection
MsgBox MyCollection.Count
End Sub

이 코드는 ‘MyCollection’ 객체를 생성한 다음 ‘Set’ 키워드를 사용하여 해당 컬렉션을 채우는 함수를 호출합니다. 이 작업이 완료되면 컬렉션의 항목 수를 표시하는 메시지 박스를 표시합니다.

Function PopulateCollection() As Collection
Dim MyCollection As New Collection
MyCollection.Add "Item1"
MyCollection.Add "Item2"

Set PopulateCollection = MyCollection
End Function

PopulateCollection 함수는 New Collection 객체를 생성하고 두 개의 항목을 추가합니다. 그런 다음 이 객체를 원래 서브 루틴에서 생성한 컬렉션 객체로 다시 전달합니다.

컬렉션을 배열로 변환하기

컬렉션을 배열로 변환하고 싶을 수 있습니다. 배열로 변환하면 데이터를 변경하고 조작하기 수월하다는 장점이 있기 때문입니다. 아래 코드는 컬렉션을 생성한 다음 이를 배열로 변환합니다.

컬렉션 인덱스는 1에서 시작하는 반면 배열 인덱스는 0에서 시작합니다. 컬렉션에 3개의 항목이 있을 경우 여기에 매칭되는 배열은 0부터 인덱스가 시작하기 때문에 배열의 크기를 2로 지정해야 합니다.

Sub ConvertCollectionToArray()
Dim MyCollection As New Collection
Dim MyArray(2) As String

MyCollection.Add "Item1"
MyCollection.Add "Item2"
MyCollection.Add "Item3"

For n = 1 To MyCollection.Count
    MyArray(n - 1) = MyCollection(n)
 
Next n

For n = 0 To 2
    MsgBox MyArray(n)
Next n
End Sub

배열을 컬렉션으로 변환하기

배열을 컬렉션으로 변환하고 싶은 경우도 있습니다. 예를 들어 코드를 사용하여 배열 요소를 가져오는 것보다 컬렉션을 사용하여 더 빠르고 효과적인 방식으로 데이터에 액세스하고 싶을 수 있습니다.

컬렉션에는 차원이 하나만 있으므로 단일 차원의 배열에서만 작동한다는 점에 유의하세요.

Sub ConvertArrayIntoCollection()
Dim MyCollection As New Collection
Dim MyArray(2) As String
MyArray(0) = "item1"
MyArray(1) = "Item2"
MyArray(2) = "Item3"

For n = 0 To 2
    MyCollection.Add MyArray(n)

Next n
For Each Item In MyCollection
    MsgBox Item
Next Item
End Sub

다차원 배열을 사용하려는 경우 배열 내 각 행에 대해 구분 문자를 사용하여 배열 값을 함께 연결하여 컬렉션을 만들고 이후 컬렉션 값을 읽을 때 프로그래밍 방식으로 구분 문자를 사용하여 값을 구분할 수 있습니다.

다차원 배열을 컬렉션으로 변환하는 또 다른 방법은, 첫 번째 차원 값을 추가(인덱스 1)한 다음 다음 차원 값을 추가(인덱스 2)하는 식으로 데이터를 컬렉션으로 변환할 수도 있습니다.

예를 들어 배열에 차원이 4개인 경우 변환된 컬렉션의 4개 인덱스들(0~3, 4~7, 8~11…)은 배열에서 한 항목의 값입니다(배열의 차원이 4이기 때문입니다).

또한 배열 값을 추가하여 키로 사용할 수도 있습니다(고유한 값일 경우). 키를 사용할 경우 특정 데이터를 쉽게 찾을 수 있습니다.