엑셀 VBA로 작업 자동화하기

매크로 기록기는 클릭 하나하나를 VBA로 받아 적어 줘요. 하지만 직접 써야 기록을 유지 관리할 수 있는 코드로 바꿀 수 있어요. 같은 줄 열 개 대신 반복문 하나를 쓰고, 중간에 알 수 없는 대화 상자를 띄우며 멈추는 대신 무엇이 잘못됐는지 분명하게 알려 주는 매크로 말이에요. 이 가이드에서는 주문 목록과 보고서를 다루는, 작지만 실제로 배포할 수 있는 매크로 묶음을 만들어 봐요.

통합 문서와 편집기 준비하기

예제는 시트가 세 개인 통합 문서를 써요. Orders(아래 데이터), Report(PDF로 내보낼 내용이면 무엇이든), Log(A1:D1에 Time, Macro, Result, User 머리글)예요.

fx

	A	B	C	D	E
1	Order	Customer	Amount	Status	Last edited
2	1001	Rivera	450
3	1002	Chen	1,250
4	1003	Okafor	980
5	1004	Lindqvist	2,100
6	1005	Park	300
7	1006	Haddad	1,000

1. 매크로 사용 통합 문서로 저장하세요

   F12를 누르고 파일 형식에서 Excel 매크로 사용 통합 문서(*.xlsm) 를 고른 다음 저장을 클릭하세요. 일반 .xlsx 파일에는 VBA 코드를 저장할 수 없어요.

2. Visual Basic 편집기를 여세요

   Alt+F11을 누르세요. 왼쪽의 프로젝트 창(프로젝트 탐색기)에 열려 있는 통합 문서마다 시트 목록과 ThisWorkbook 개체가 보여요. 창이 숨겨져 있으면 Ctrl+R을 누르세요.

3. 모듈을 삽입하세요

   프로젝트 창에서 내 통합 문서를 선택하고 삽입 > 모듈을 선택하세요. 일반적인 매크로는 모듈에 넣어요. 이벤트 코드는 아래에서 보겠지만 시트나 ThisWorkbook의 코드 창에 따로 넣어요.

4. 변수 선언을 필수로 정하세요

   도구 > 옵션을 선택하고 변수 선언 요구에 체크하세요. 그러면 새 모듈이 항상 Option Explicit로 시작하고, 변수 이름을 잘못 쓰면 비어 있는 새 변수가 조용히 생기는 대신 컴파일 오류가 나요. 이 줄이 없는 모듈에는 맨 위에 직접 추가하세요.

Sub, Function, 변수와 상수

Sub는 서식 지정, 복사, 내보내기처럼 어떤 일을 해요. Function은 값을 돌려주고, 모듈에 있는 공용(Public) 함수는 기본 제공 함수처럼 워크시트 수식에서 쓸 수 있어요. 프로시저마다 일 하나만 맡기고, 그 일이 드러나는 이름을 붙이세요.

Option Explicit

Public Function TaxAmount(ByVal amount As Double, ByVal rate As Double) As Double
    TaxAmount = amount * rate
End Function

이 코드를 모듈에 붙여 넣은 뒤 Orders 시트의 셀에 =TaxAmount(C3,0.1)을 입력하면 엑셀이 125를 돌려줘요.

모든 변수는 Dim으로 형식과 함께 선언하세요. 행 번호와 개수는 Long, 금액은 Double, 텍스트는 String, 개체는 Range와 Worksheet예요. 고정된 값은 프로시저 한가운데에 설명 없는 1000으로 묻어 두지 말고 Const로 이름을 붙이세요.

범위를 반복하는 매크로 쓰기

이 매크로는 기준 금액을 물어보고, 그보다 큰 주문은 Review, 나머지는 OK로 표시한 다음, 몇 건을 표시했는지 알려 줘요. 같은 모듈의 TaxAmount 아래에 붙여 넣으세요.

Public Sub FlagLargeOrders()
    Const DEFAULT_LIMIT As Double = 1000
    Dim ws As Worksheet
    Dim cell As Range
    Dim lastRow As Long
    Dim flagged As Long
    Dim answer As String
    Dim limit As Double

    answer = InputBox("Flag orders above what amount?", "Flag large orders", CStr(DEFAULT_LIMIT))
    If answer = "" Then Exit Sub
    If Not IsNumeric(answer) Then
        MsgBox "Please enter a number.", vbExclamation, "Flag large orders"
        Exit Sub
    End If
    limit = CDbl(answer)

    Set ws = ThisWorkbook.Worksheets("Orders")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "There are no orders to check.", vbInformation, "Flag large orders"
        Exit Sub
    End If

    For Each cell In ws.Range("C2:C" & lastRow)
        If cell.Value > limit Then
            cell.Offset(0, 1).Value = "Review"
            flagged = flagged + 1
        Else
            cell.Offset(0, 1).Value = "OK"
        End If
    Next cell

    MsgBox flagged & " order(s) flagged for review.", vbInformation, "Flag large orders"
End Sub

Alt+F8을 누르고 FlagLargeOrders를 선택한 뒤 실행을 클릭하세요. 기본값 1000을 그대로 받아들이면 "2 order(s) flagged for review"라는 메시지가 나와요. Chen(1,250)과 Lindqvist(2,100)예요. Haddad의 1,000은 1,000보다 큰 값이 아니니 OK예요.

fx

	A	B	C	D	E
1	Order	Customer	Amount	Status	Last edited
2	1001	Rivera	450	OK
3	1002	Chen	1,250	Review
4	1003	Okafor	980	OK
5	1004	Lindqvist	2,100	Review
6	1005	Park	300	OK
7	1006	Haddad	1,000	OK

각 부분이 하는 일은 이래요.

• InputBox와 MsgBox. InputBox는 사용자가 입력한 값을 돌려주고, 취소를 클릭하면 빈 문자열을 돌려줘요. 그래서 ""이면 매크로를 끝내요. MsgBox로 결과를 알려 주면 매크로가 아무 말 없이 끝나는 일이 없어요.
• End(xlUp) 은 시트 맨 아래에서 Ctrl+Up을 누를 때처럼 A열의 마지막 사용 행을 찾아요. 그래서 반복문이 오늘은 주문 6건, 다음 달에는 600건을 처리해요.
• For Each cell In … 은 범위의 셀을 하나씩 모두 방문해요. Offset(0, 1)은 한 열 오른쪽의 셀이에요.

Do While은 조건이 바뀔 때까지 반복해요. 아래 매크로는 A열의 첫 빈 행, 즉 새 주문이 들어갈 행을 찾아요.

Public Sub ShowNextEmptyRow()
    Dim ws As Worksheet
    Dim r As Long

    Set ws = ThisWorkbook.Worksheets("Orders")
    r = 2
    Do While ws.Cells(r, "A").Value <> ""
        r = r + 1
    Loop

    MsgBox "The next order goes in row " & r & ".", vbInformation
End Sub

예제 데이터에서는 8행이라고 알려 줘요.

Select 없이 범위를 직접 다루기

기록기는 내가 클릭한 것을 그대로 받아 적기 때문에 코드에 Select와 ActiveCell이 가득해요. 아래 두 Sub는 모두 Orders 시트의 D3에 "Review"를 넣어요.

Public Sub MarkReviewRecorded()
    Sheets("Orders").Select
    Range("D3").Select
    ActiveCell.Value = "Review"
End Sub

Public Sub MarkReviewDirect()
    ThisWorkbook.Worksheets("Orders").Range("D3").Value = "Review"
End Sub

모든 면에서 직접 다루는 쪽이 나아요. 사용자가 보고 있는 화면을 바꾸지 않고, 더 빠르게 실행되고(반복문 안에서 선택하는 건 느려요), 엉뚱한 셀을 건드릴 일도 없어요. 기록된 버전에서 통합 문서를 지정하지 않은 Sheets와 Range는 현재 활성 통합 문서를 가리켜요. 그래서 매크로를 실행할 때 다른 통합 문서가 앞에 있으면 그 통합 문서를 고쳐 버려요. ThisWorkbook은 언제나 코드가 들어 있는 통합 문서를 뜻해요.

기록된 매크로를 정리할 때는 Select와 Selection/ActiveCell 짝을 시트와 범위를 직접 지정하는 한 줄로 바꾸세요.

오류를 처리하고 실행마다 기록 남기기

오류 처리가 없으면 실패했을 때 종료와 디버그 단추가 있는 대화 상자가 뜨면서 매크로가 멈추고, 작업은 반쯤 끝난 상태로 남아요. On Error GoTo는 어떤 오류든 프로시저 끝에 있는 레이블로 보내서 그곳에서 제대로 알릴 수 있게 해 줘요.

아래 매크로는 모든 데이터를 새로 고치고, 시각을 찍고, Report 시트를 통합 문서와 같은 폴더에 PDF로 내보내고, 성공하든 실패하든 결과를 기록해요.

Public Sub RefreshAndExport()
    Dim ws As Worksheet
    Dim pdfPath As String
    Dim msg As String

    On Error GoTo ErrHandler

    If ThisWorkbook.Path = "" Then Err.Raise vbObjectError + 1, , "Save the workbook before exporting."

    Set ws = ThisWorkbook.Worksheets("Report")

    ThisWorkbook.RefreshAll
    ws.Range("B1").Value = Now

    pdfPath = ThisWorkbook.Path & Application.PathSeparator & "Report " & Format(Date, "yyyy-mm-dd") & ".pdf"
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath

    WriteLog "RefreshAndExport", "Saved " & pdfPath
    MsgBox "Report saved to " & pdfPath, vbInformation, "Refresh and export"
    Exit Sub

ErrHandler:
    msg = "Error " & Err.Number & ": " & Err.Description
    WriteLog "RefreshAndExport", msg
    MsgBox "The report was not exported." & vbNewLine & msg, vbExclamation, "Refresh and export"
End Sub

Public Sub WriteLog(ByVal macroName As String, ByVal result As String)
    Dim logSheet As Worksheet
    Dim nextRow As Long

    Set logSheet = ThisWorkbook.Worksheets("Log")
    nextRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1

    logSheet.Cells(nextRow, "A").Value = Now
    logSheet.Cells(nextRow, "B").Value = macroName
    logSheet.Cells(nextRow, "C").Value = result
    logSheet.Cells(nextRow, "D").Value = Application.UserName
End Sub

중요한 부분은 이래요.

• 레이블 앞의 Exit Sub. 이 줄이 없으면 성공한 실행도 그대로 오류 처리 부분까지 내려가요.
• 오류 정보를 먼저 복사하기. 다른 코드가 실행되기 전에 msg에 Err.Number와 Err.Description을 담아 두기 때문에, 메시지가 원래 실패 내용을 그대로 설명해요.
• Err.Raise 는 읽기 쉬운 설명이 붙은 오류를 직접 만들어요. 그래서 "통합 문서를 저장하지 않음" 같은 상황도 다른 실패와 똑같이 오류 처리와 기록을 거쳐요.
• WriteLog는 별도의 공용 프로시저예요. 그래서 모든 매크로와 아래의 이벤트 코드가 함께 쓸 수 있어요. 인수를 받기 때문에 매크로 대화 상자에는 나타나지 않아요.

fx

	A	B	C	D
1	Time	Macro	Result	User
2	2026-10-12 08:02	RefreshAndExport	Saved C:\Reports\Report 2026-10-12.pdf	Ana
3	2026-10-13 08:01	RefreshAndExport	Error 9: 아래 첨자 사용이 잘못되었습니다.	Ana
4	2026-10-13 08:05	RefreshAndExport	Saved C:\Reports\Report 2026-10-13.pdf	Ana

오류 처리를 테스트하려면 Report 시트의 이름을 바꾸고 매크로를 실행해 보세요. 존재하지 않는 시트 이름에 대한 VBA 오류인 "Error 9: 아래 첨자 사용이 잘못되었습니다."가 기록되고, 보고서를 내보내지 못했다는 메시지가 나와요. 테스트가 끝나면 시트 이름을 원래대로 돌려놓으세요.

이벤트로 코드 자동 실행하기

이벤트 프로시저는 어떤 일이 일어나면 저절로 실행돼요. 이벤트를 일으키는 개체의 코드 창에 넣어야 하고, 이름이 정확해야 해요.

Workbook_Open은 파일을 열 때(매크로가 사용 설정된 경우) 실행돼요. 프로젝트 창에서 ThisWorkbook을 두 번 클릭하고 다음 코드를 붙여 넣으세요.

Private Sub Workbook_Open()
    WriteLog "Workbook_Open", "Opened"
End Sub

이 코드가 Log 시트에 기록을 쓰기 때문에, 파일을 닫을 때 엑셀이 변경 내용을 저장할지 물어요.

Worksheet_Change는 그 시트의 셀이 편집될 때마다 실행돼요. 프로젝트 창에서 Orders 시트를 두 번 클릭하고 아래 코드를 붙여 넣으세요. 누군가 C열의 금액을 바꿀 때마다 E열에 시각을 기록해요.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changed As Range
    Dim cell As Range

    Set changed = Intersect(Target, Me.Range("C2:C1000"))
    If changed Is Nothing Then Exit Sub

    On Error GoTo CleanUp
    Application.EnableEvents = False

    For Each cell In changed.Cells
        cell.Offset(0, 2).Value = Now
    Next cell

CleanUp:
    Application.EnableEvents = True
End Sub

편집한 셀이 C2:C1000 밖에 있으면 Intersect가 Nothing을 돌려주니 다른 편집은 무시돼요. Application.EnableEvents = False는 매크로가 직접 쓴 값 때문에 이벤트가 다시 실행되는 일을 막고, CleanUp 레이블은 중간에 무언가 실패해도 이벤트를 다시 켜 줘요. 이벤트가 꺼진 채로 남으면 엑셀을 다시 시작하거나 속성을 True로 되돌릴 때까지 어느 통합 문서의 이벤트 코드도 실행되지 않아요.

자주 하는 실수

• .xlsx로 저장하기. 엑셀이 경고하는데, 그래도 계속하면 VBA 코드가 지워져요. .xlsm을 쓰세요.
• 기록기 습관을 그대로 두기. Select, Selection, ActiveSheet를 쓰면 매크로가 사용자가 마지막에 클릭한 곳에 따라 달라져요. 통합 문서, 시트, 범위를 직접 지정하세요.
• Exit Sub 없는 오류 처리. 성공하든 실패하든 실행할 때마다 오류 메시지로 끝나요.
• 프로시저 맨 위의 On Error Resume Next. 모든 오류를 숨겨서 매크로가 잘못된 데이터로 계속 진행돼요. 실패할 수 있다고 예상되는 한 줄 앞에서만 쓰고, 바로 다음에 On Error GoTo ErrHandler로 되돌리세요.
• 하드 코딩된 경로와 의미 없는 숫자. C:\Users\Ana\Desktop은 다른 사람의 컴퓨터에서는 동작하지 않아요. ThisWorkbook.Path나 이름 붙인 Const를 쓰세요. 통합 문서가 OneDrive나 SharePoint에 저장되어 있으면 ThisWorkbook.Path가 웹 주소를 돌려줄 수 있는데, ExportAsFixedFormat은 그 주소에 저장하지 못해요. 내보내기에는 로컬 폴더를 쓰세요.
• 내려받은 파일의 매크로가 실행되지 않음. 엑셀은 인터넷에서 받은 파일의 매크로를 차단해요. 믿을 수 있는 파일이라면 파일을 닫고, 파일 탐색기에서 마우스 오른쪽 단추로 클릭해 속성을 고르고, 차단 해제(Unblock)에 체크한 다음 확인을 클릭하세요.

연습

연습

동료에게 넘겨줄 수 있는 매크로 만들기

1. 위에서 본 Orders, Report, Log 시트를 만들고 통합 문서를 .xlsm으로 저장하세요.
2. FlagLargeOrders를 추가하고 기본 기준으로 실행해서 Chen과 Lindqvist만 표시되는지 확인하세요. 900으로 다시 실행해서 Okafor와 Haddad가 추가되는지 확인하세요.
3. RefreshAndExport와 WriteLog를 추가하세요. 실행한 다음 통합 문서와 같은 폴더에 PDF가 생기고 Log 시트에 행이 추가되는지 확인하세요.
4. Report 시트의 이름을 바꾸고 RefreshAndExport를 다시 실행해서, 오류가 화면에 표시되고 기록에도 남는지 확인하세요. 시트 이름을 원래대로 돌려놓으세요.
5. Orders 시트에 Worksheet_Change 코드를 추가하고, 금액 하나를 바꾼 뒤 E열의 시각을 확인하세요.

그다음 같은 패턴을 실제 통합 문서에 적용해 보세요. 새로 고치고, 시각을 찍고, 내보내고, 기록을 남기는 매크로 하나와, 정확히 무엇이 실패했는지 알려 주는 오류 처리를 갖추는 거예요.

실력 점검

• 분명한 이름, 선언된 변수, 이름 붙인 상수를 갖춘 Sub를 쓸 수 있나요?
• Select나 ActiveCell 없이, 늘어나는 범위를 반복할 수 있나요?
• 중간에 멈추지 않고 실패를 알리고 기록하는 오류 처리를 추가할 수 있나요?
• Select와 Activate가 보이면 대개 매크로를 더 안전하게 쓸 수 있다는 뜻인 이유를 설명할 수 있나요?
• 통합 문서를 열거나 셀이 바뀔 때 코드가 자동으로 실행되게 할 수 있나요?