개요

엑셀 업무를 VBA로 자동화한 내용을 써보려고 한다.

엑셀 VBA(Visual Basic for Application)란 엑셀에서 사용할 수 있는 프로그래밍 언어이다.

매주, 매달 하는 엑셀 업무들이 있는데 그걸 몇달간 하면서 비효율적이라는 생각이 들었고 자동화에 도전해보고 싶다는 생각을 하게 됐다.

회사에서 월1회 하는 자료조사 업무가 있는데, 이건 자료조사 하는 법을 정리했을 때 5페이지가 나올 정도로 가장 복잡하다. (다른 업무는 보통 1장 ~ 3장)

그래서 이 업무를 먼저 자동화해보기로 했다.

1. 매크로 기록하기

VBA를 아무것도 모르는데 시작하려니, 막막했다. 그때 VBA 책을 사서 봤는데, 거기서 매크로를 기록하고 그 코드를 분석하며 공부해나가는 것이 좋은 공부법이라고 했다.

그래서 먼저 모든 과정을 매크로로 기록했다. 그리고 원본에서 다시 돌려봤다. 원본이 똑같았기에 같은 결과가 나올 줄 알았는데, 중간에 자꾸 오류가 났다. 그래서 짧게 기록하고 그렇게 나온 코드를 합치기로 했다. 그래도 오류가 나서 중간중간 고치고 넘어갔다.

2. 하드코딩 된 변수 수정하기

1번의 과정을 통해 전체 코드가 만들어졌다. 그런데, 문서 a에서는 정상적인 결과가 나오는데 문서 b, c에서는 정상적인 결과가 나오지 않았다. 그래서 디버깅해보니, 다수의 변수들이 하드코딩 되어있었다.

예를 들면 아래와 같다. F2 셀을 F2부터 F10까지 자동채우기 하는 코드이다. 문서 a에서는 F10이 마지막 셀이었어서 F10으로 하드코딩되어 매크로로 기록된 것 같다. 하지만 문서 b에서는 F10이 마지막 셀이 아니었고, 그래서 나와야 하는 결과물이 나오지 않았다.

Range("F2").AutoFill Destination:=Range("F2:F10")

그래서 B열의 행 개수를 세어서 lastRow라는 변수를 만들었다. 그렇게 해서 F2부터 F열의 마지막 셀까지 범위를 잡을 수 있었다.

(여기서 F열이 아니라 B열의 행 개수를 센 이유는, F열은 중간에 빈 값이 있을 수 있기 때문이었다. B열은 id와 같은 키값을 담고 있어서, 빈 값이 없기 때문에 B열을 셌다.)

Dim lastRow As Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row

Range("F2").AutoFill Destination:=Range("F2:F" & lastRow)

3. 생략해도 되는 코드 삭제하기

매크로로 기록을 하다보면, 무의미한 코드가 많이 들어간다. 예를 들어 아무 셀이나 클릭을 한 건데, VBA로 보면 Range("A1").Select 이런식이다. 이런 코드를 삭제했다.

4. 코드 최적화하기

매크로로 기록하다보면, 코드가 불필요하게 길어질 때가 있다. 예를 들면 아래는 A1 기준의 표 전체에 테두리를 입히는 코드이다.

xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal만 코드가 변경되고 있고 그 안의 코드는 모두 동일한 것을 알 수 있다. 테두리의 왼쪽, 오른쪽, 위쪽, 아래쪽의 가장 바깥쪽까지 테두리를 입힌다는 뜻이다. 결국 현재 표의 범위를 뜻한다.

그리고 현재 표의 범위는 CurrentRegion으로 한번에 구할 수 있다.

<최적화 전 코드>

	Range("A1").Select
    ActiveCell.CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

<최적화 후 코드>

	With Sheets("Rawdata").Range("A1").CurrentRegion.Borders
       .LineStyle = xlContinuous
       .Weight = xlThin
   End With

성과

보고서 만드는 시간이 95% 단축되었다.

• 자동화 전 : 8분 20초

• 자동화 후 : 25초

  

다음에는?

• 다음에는 다른 업무도 자동화해보려고 한다. 이번에는 코드를 작성하는데 이틀, 최적화에 이틀 걸렸는데 할수록 시간이 단축되기를 기대하고 있다👍
• 다음에는 한번에 최적화를 하지 말고 짧은 단위로 최적화를 하면서 완성해나가고 싶다.

후기

• VBA는 오류메시지를 정확하게 알려주지 않아서 아쉬웠다. 거의 오류가 발생한 라인만 알려주는 것 같다.
• 생각보다 그동안 했던 코딩과 다르지 않다는 생각이 들었다.