엑셀 VBA의 배열 대신 Collection 세번째(시트 데이터 읽기, 불러오기)

[ 들어가며 ]

 

VBA는 보통 시트에 입력된 데이터를

활용하는 경우가 많습니다.

 

오늘은 Collection을 사용하여

시트의 데이터를 불러와

사용하는 방법을 간단한 예시코드와 함께 알아보겠습니다.

 

 

[ 기본 데이터 ]

 

예시에 활용될 기본 데이터는 아래 그림과 같이

이름과 성적입니다.

 

 

[ 시트 데이터 불러오기 ]

 

먼저 시트에 있는 이름의 데이터를

Collection에 넣어보겠습니다.

코드는 아래와 같습니다.

Sub ReadWorksheets()

    Dim col As New Collection
    
    Dim rng As Range
    Set rng = Sheet1.Range("A1").CurrentRegion
    
    Dim i As Long
    For i = 2 To rng.Rows.Count
        col.Add rng.Cells(i, 1).Value
    Next i
    
    For Each Item In col
        Debug.Print Item
    Next Item
    
End Sub

먼저 Collection을 선언해 줍니다.

Dim col As New Collection

 

그 다음 rng 이름의 Range 객체를 선언합니다.

Range는 워크시트의 셀 그룹을 나타내는 데 사용됩니다.

Dim rng As Range

 

다음 코드는

rng 객체에 A1셀에서 Ctrl+A를 눌렀을 때와 동일한 영역을 설정합니다.

Set rng = Sheet1.Range("A1").CurrentRegion

 

Currentregion을 사용하면

엑셀에서 어떤 셀을 선택하고 Ctrl+A를 눌렀을 때와

동일한 동작을 합니다.

그리고 사용된 sheet1은 sheet1의 코드네임입니다.

코드네임에 대한 설명은 아래링크를 참고하시면 됩니다.

※ 엑셀 VBA에서 시트에 접근하는 두가지 방법(시트이름, 코드네임)

 

다음 코드는 

rng 범위의 행을 순회합니다.

첫번째 행은 제목행이므로

두번째 행부터 시작하여 범위 내의 행 수까지 반복을 합니다.

그리고 각 행의 첫번째 열의 값을 

col Collection에 추가합니다.

For i = 2 To rng.Rows.Count
    col.Add rng.Cells(i, 1).Value 
Next i

 

그 다음 col에 저장된 값들을 

직접 실행창에 출력합니다.

For Each Item In col
    Debug.Print Item
Next Item

 

여기서 점수가 70점 이상의 학생만

Collection에 추가하려면

코드를 다음과 같이 수정하면 됩니다.

Sub ReadWorksheets()

    Dim col As New Collection
    
    Dim rng As Range
    Set rng = Sheet1.Range("A1").CurrentRegion
    
    Dim i As Long
    For i = 2 To rng.Rows.Count
        If rng.Cells(i, 2).Value > 70 Then
            col.Add rng.Cells(i, 1).Value
        End If
    Next i
    
    For Each Item In col
        Debug.Print Item
    Next Item
    
End Sub

 

이렇게 하면

70점 이상의 학생만 

직접 실행 창에 출력이 됩니다.

반응형

[ Collection의 자료를 워크시트에 출력하기 ]

 

다음은 Collection에 저장된 데이터를

워크시트에 출력하는 코드입니다.

Collection에 입력된 70점 이상의 학생만

[ E1 ]셀부터 차례로 출력을 해보겠습니다.

Sub ReadWorksheets()

    Dim col As New Collection
    
    Dim rng As Range
    Set rng = Sheet1.Range("A1").CurrentRegion
    
    Dim i As Long
    For i = 2 To rng.Rows.Count
        If rng.Cells(i, 2).Value > 70 Then
            col.Add rng.Cells(i, 1).Value
        End If
    Next i
    
    Dim row As Long
    row = 1
    
    For Each Item In col
        Sheet1.Cells(row, 5).Value = Item
        row = row + 1
    Next Item
    
End Sub

 

앞선 코드에서 직접 실행창에 출력하게 했던 코드부분만 수정하면

아래 그림과 같이 [ E1 ]셀부터 해당 값들이 출력이 됩니다.

 

만약 제목을 추가로 출력하고 싶다면

아래와 같이 코드를 수정하면 됩니다.

Sub ReadWorksheets()

    Dim col As New Collection
    
    Dim rng As Range
    Set rng = Sheet1.Range("A1").CurrentRegion
    
    Dim i As Long
    For i = 1 To rng.Rows.Count
        If rng.Cells(i, 2).Value > 70 Or i = 1 Then
            col.Add rng.Cells(i, 1).Value
        End If
    Next i
    
    Dim row As Long
    row = 1
    
    For Each Item In col
        Sheet1.Cells(row, 5).Value = Item
        row = row + 1
    Next Item
    
End Sub

 

수정된 부분은 아래와 같습니다.

Dim i As Long
For i = 1 To rng.Rows.Count
    If rng.Cells(i, 2).Value > 70 Or i = 1 Then
        col.Add rng.Cells(i, 1).Value
    End If
Next i

이렇게 하면

출력될 때 제목까지 같이 출력이 됩니다.

 

이번에는 Collection을 사용하여

워크시트에 데이터를 불러오고 쓰는 방법을

간단한 코드와 함께 알아보았습니다.

 

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기