아이엑셀러 닷컴

사전 준비

얼마 전 강의에서 수식과 기본 기능을 이용하여 '중복된 항목 제거'하는 법에 대해 알아보았습니다. 4가지 방법으로 해결했었죠(수식 2가지 + 기본 기능 2가지).

VBA를 이용하여 해결할 수도 있습니다. VBA에 대해 알아야 하지만 훨씬 다양하고 복잡한 작업도 유연하게 처리할 수 있습니다. 어떻게 활용하는 지는 다른 강의에서 다루기로 하고, 이번 시간에는 기본기를 익히는 데 집중하도록 합니다.

이 강의는 아래 동영상 강의 내용과 보완 관계에 있습니다. 동영상과 함께 보시면 이해하기 더욱 쉽습니다.

워크시트 상태에서 ALT + F11 키를 눌러 Visual Basic Editor를 띄웁니다. [삽입] - [모듈] 메뉴를 선택해서 모듈 시트를 한 장 삽입하고 코딩을 시작합니다.

코딩 준비

방법 1: 컬렉션 개체 이용

중복된 값이 있는 여러 항목 중에서 고유한 값만 추출할 때, 컬렉션 개체^{Collection object}를 많이 사용합니다.

컬렉션 개체는 변수를 선언할 때 'New'라는 키워드를 붙여야 합니다다.

Dim oWords As New Collection

컬렉션 개체에 구성원을 추가할 때에는 Add, 제거할 때에는 Remove 메서드를 사용합니다. 실제 코드를 보도록 하죠.

Sub getUniqueWords()
    ''' 변수 선언        
    Dim shtWord As Worksheet
    Dim rTbl As Range           
    Dim rWrite As Range
    Dim oWords As New Collection    ''' 컬렉션 개체
    Dim vX As Variant    ''' 컬렉션 개체의 각 구성원에 접근
    Dim rX As Range
    Dim iX As Integer
    
    On Error Resume Next    ''' 반드시 필요
    Set shtWord = Worksheets("연습")
    Set rTbl = shtWord.Range("B3").CurrentRegion
    Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)
        ''' B열에서 제목을 제외한 영역을 rTbl 변수에 재지정

    Set rWrite = rTbl.Cells(1).Offset(, 8)    
        ''' 결과를 표시할 위치(J4 셀)
    Range(rWrite, rWrite.End(xlDown)).Clear
        ''' 결과를 표시할 영역 주변 정리
    
    ''' 컬렉션 개체에 항목을 겹치지 않게 추가하는 과정
    ''' 세부 설명은 아래에서
    For Each rX In rTbl.Cells
        oWords.Add rX, rX
    Next

    ''' J4 셀부터 결과 표시(J5, J6,...)
    For Each vX In oWords
        rWrite.Offset(iX) = vX
        iX = iX + 1
    Next
End Sub

CODE

컬렉션 개체에 항목을 추가할 때에는 Add 메서드를 이용합니다. 이 때 다음과 같은 형식을 사용합니다.

oWords.Add item:=rX, key:=rX

(또는 oWords.Add rX, rX)

항목을 추가할 때, item과 key를 사용하는데, 만약 같은 key 값이 이미 있다면 오류가 발생하여 실행이 중단됩니다. 하지만 앞에서 On Error Resume Next 문을 추가해 두었으므로 작업이 계속 진행되고, 결과적으로 중복되지 않는 항목들이 컬렉션 개체에 저장됩니다.

프로시저 내부에 커서를 둔 채, [실행] 아이콘을 클릭하면 지정한 위치에 결과가 표시됩니다.

방법 2: For ~ Next 문 이용

중복된 항목을 제거하려면 반드시 컬렉션 개체를 사용해야 하는 걸까요? 그렇지는 않습니다. 다른 방법도 가능합니다. 앞에서 작성한 코드를 조금 변형합니다. InStr 함수를 사용한 부분을 눈여겨 보세요.

InStr은 '한 문자열이 다른 문자열 내에서 처음 나타나는 위치'를 구해주는 VBA 함수로, 다음과 같은 형태로 사용합니다.

InStr ([시작], 문자열1 , 문자열2 , [비교])

[ ] 인수는 생략 가능

Sub getUniqueWords_2()
    Dim shtWord As Worksheet
    Dim rTbl As Range
    Dim rWrite As Range
    Dim rX As Range
    Dim iX As Integer
    Dim sWord As String     ''' 문자형 변수 추가
    
    On Error Resume Next
    Set shtWord = Worksheets("연습")
    Set rTbl = shtWord.Range("B3").CurrentRegion
    Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)
    Set rWrite = rTbl.Cells(1).Offset(, 9)
    Range(rWrite, rWrite.End(xlDown)).Clear

    For Each rX In rTbl.Cells
        If InStr(sWord, rX) = 0 Then
            ''' 이 조건이 True라는 것은 새로운 값이 등장했다는 뜻

            rWrite.Offset(iX) = rX    ''' J4 셀부터 결과 표시
            iX = iX + 1
            sWord = sWord & rX
                ''' 다음 비교를 위해 sWord에 rX 값을 계속 추가
        End If
    Next
End Sub

CODE

만약 결과값을 오름차순으로 정렬하려면 다음 한 줄을 프로시저 마지막에 추가하면 됩니다. 여기서부터는 VBA를 얼마나 알고 있느냐에 따라 활용도가 갈립니다.

Range(rWrite, rWrite.End(xlDown)).Sort key1:=rWrite, order1:=xlAscending, Header:=xlNo

내림차순으로 한다면 xlAscending 대신 xlDescending을 사용하면 됩니다.

이 밖에도 다른 방법들이 있을 수 있습니다. 응용력을 발휘하면 수식이나 엑셀 기본 기능으로는 할 수 없는 일들을 처리할 수 있습니다. 기회가 되면 다른 강의에서 다뤄보도록 하죠.

들어가기 전에 ㅡ 개/고양이보단 나은 사람이 되고자 하였으나

사전 준비

방법 1: 컬렉션 개체 이용

방법 2: For ~ Next 문 이용