엑셀 VBA 디버깅 (오류 처리) 모든 방법 - 핵심 정리

엑셀 VBA 디버깅 (오류 처리) 모든 방법 - 핵심 정리

엑셀 VBA 디버깅 오류처리 목차 바로가기

중요 내용 요약

1. VBA 에서 발생하는 오류 종류 3가지

2. VBA 오류 처리 구문(On Error) 종류 4가지

3. VBA 디버깅 필수 단축키

4. VBA 디버깅 필수 기능

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

엑셀 VBA 발생하는 오류 종류 살펴보기

엑셀 VBA로 명령문을 작성하다보면 크게 3가지 종류의 오류가 발생합니다.

각 오류가 발생하는 상황과 원인, 그리고 간략한 해결 방법을 하나씩 살펴보겠습니다.

Syntax Error (구문 오류)

Syntax Error는 명령문을 작성하다가 특정 구문에 뭔가 누락되었거나 잘못 작성된 것을 감지하였을 때 발생합니다. 예를 들어, If 문을 작성 후 Then 또는 Goto 없이 다음 줄로 넘어갈 경우 아래와 같이 필요한 요소: Then 또는 Goto 라는 메시지창과 함께 Syntax Error가 발생합니다.

예를 들어 아래 명령문을 작성해보겠습니다. 명령문 편집기를 실행 후 [삽입] - [모듈]을 선택하여 새로운 모듈을 추가합니다. 이후 아래 명령문을 단계 별로 입력하면, 작성하는 도중 각각 구문 오류가 발생합니다.

Sub SyntaxErrorTest()
 
if thisworkbook.Name = "통합문서1" ' Goto 또는 Then이 누락되어 오류메시지가 출력됩니다.
 
End Sub

엑셀 개발도구를 활성화하는 방법은 아래 개발도구 활성화방법 관련 포스트를 확인하세요.

Compilation Error (명령문 편집 오류)

Syntax Error가 명령문 한 줄에 대해서 동작하는 오류라면, Compilation Error는 특정 구문이 모두 잘못되거나 누락된 경우 발생하는 오류입니다.

Syntax Error는 명령문을 작성하는 과정에 실시간으로 발생하여 사용자가 오류를 바로 감지할 수 있는 반면, Compilation Error는 명령문을 직접 실행하거나 컴파일할 경우에만 확인할 수 있습니다.

대표적으로 발생하는 Compilation Error 종류는 아래와 같습니다.

예를 들어 아래 명령문을 모듈에 추가한 뒤, 실행하면 "End If가 없는 If 블록 문" 이라는 오류메시지가 발생하며 명령문 실행이 중단됩니다.

Sub CompilationErrorTest()
 
If ThisWorkbook.Name = "통합문서1" Then
    MsgBox "현재 파일의 이름은 통합문서1 입니다." 'End If가 누락되어 오류메시지가 출력됩니다.
 
End Sub

Run-time Error (실행 중 오류)

VBA로 명령문을 작성하면서 가장 자주 발생하는 오류는 Run-time Error 입니다. Run-time Error는 그 종류가 매우 다양하며, 발생한 원인과 상황에 따라 해결 방법도 매우 다양합니다.

지금까지 알아본 Syntax Error, Compilation Error와 달리 Run-time Error는 VBA 명령문 밖에서 발생하는 문제이므로 Run-time Error가 발생한다면 명령문을 더욱 깊게 살펴봐야 하고, 이렇게 명령문을 살펴보는 과정을 '디버깅' 이라고 부릅니다.

예를 들어 아래 명령문을 VBA 모듈에 추가한 뒤 실행해보겠습니다. 명령문을 실행하면 '응용 프로그램 정의 오류 또는 개체 정의 오류입니다.' 라는 오류메시지가 출력됩니다.

Sub OpenFile()
 
Dim Path As String
Path = "C:\Test\Test.xlsx"
 
Application.Workbooks.Open Path
'파일 경로에 파일이 없을 경우 오류메시지를 출력합니다.
 
End Sub

위 명령문 실행 시 오류가 발생하는 이유는 VBA 명령문이 잘못 되어서가 아니라, 외부 요인, 즉 파일이 존재하지 않아서 그런 것 인데요. 이러한 오류를 예방하기 위해 명령문을 아래와 같이 수정합니다.

Sub OpenFile()
 
Dim Path As String
Path = "C:\Test\Test.xlsx"
 
If Dir(Path) = "" Then MsgBox Path & "경로에 파일이 존재하지 않습니다.": Exit Sub
'If 문으로 경로에 파일이 없을 경우, 오류메시지 대신 안내메시지 출력 후 명령문을 종료합니다.
Application.Workbooks.Open Path
 
End Sub

위와 같이 명령문을 작성하면, 해당 경로에 파일이 존재하지 않을 시 오류 대신 '경로에 파일이 존재하지 않습니다.' 라는 안내메시지를 출력 후 명령문을 종료합니다.

VBAProject 컴파일 도구로 명령문 실행 전 디버깅하기

매크로 편집기에서 기본으로 제공하는 VBAProject 컴파일도구를 사용하면, 명령문을 실행하기 전 미리 Syntax 오류와 Compilation 오류를 확인하고 수정할 수 있습니다.

컴파일러를 실행하는 것은 간단합니다. 매크로 편집기에서 [디버그] - [VBAProject 컴파일] 을 클릭하면 컴파일러가 실행됩니다.

오류 잡기 상황 설정 (유저폼 사용시 오류 처리 설정)

만약 프로젝트 안에 유저폼(사용자 정의 폼)이 포함되어 있을 경우 오류 잡기 설정을 변경하면 더욱 편리하게 오류를 디버깅할 수 있습니다.

매크로 편집기에서 [도구] - [옵션] 으로 이동한 뒤, [일반] 탭 으로 이동하면 '오류 잡기' 설정을 변경할 수 있습니다. 매크로 편집기에서 제공하는 오류 잡기 설정방식은 3가지입니다.

매크로 편집기는 기본값으로 3번째가 선택되지만, 만약 프로젝트 안에 유저폼이나 클래스모듈을 사용할 경우 2번째 옵션인 '클래스 모듈에서 중단'을 사용하는 것이 좋습니다.

엑셀 VBA 오류 처리 문구 살펴보기(On Error 구문)

VBA를 포함한 모든 프로그래밍 언어를 작성할 때 오류를 처리하는 방법은 크게 2가지로 나눠집니다.

가장 이상적인 상황은 작성한 명령문이 오류 없이 모두 완벽하게 동작하는 것이지만, 작성한 명령문을 배포하면 예상치 못한 오류가 자주 발생합니다. 따라서 이러한 예상치 오류가 발생할 것을 대비하여 VBA에서는 On Error 구문을 사용합니다.

사용할 수 있는 On Error 구문은 크게 4가지로 나눠집니다.

On Error 구문을 사용해서 오류를 처리하는 방법에 대한 자세한 설명은 아래 VBA 오류처리 방법 관련 포스트를 참고하세요.

엑셀 VBA 디버깅 기본 예제

이번 포스트에서는 VBA 명령문에서 오류가 발생하거나 명령문 실행 과정을 살펴보기 위해 코드를 디버깅하는 방법을 단계별로 살펴보겠습니다.

엑셀 VBA 디버깅 필수 단축키

VBA로 명령문을 작성할 때 반드시 알아두면 좋은 기능과 디버깅 단축키를 하나씩 소개해드리겠습니다.

1. F5 : 명령문 실행

키보드 F5키를 누르면 현재 선택된 명령문이 바로 실행됩니다. 만약 명령문을 각 단계별로 실행하려면 F8키를 눌러 명령문을 실행합니다.

F5키로 명령문을 실행하더라도 명령문 중간에 중단점이 설정되어 있으면, 해당 지점에서 명령문 실행이 중단됩니다.

2. F8 : 한 단계씩 코드 실행

키보드 F8키를 누르면 명령문을 한 줄씩 단계별로 실행할 수 있습니다. 매번 F8키를 누를 때마다, 명령문이 각 단계별로 실행되면서 현재 실행 중인 단계가 매크로 편집기에서 노란색으로 강조됩니다.

3. Ctrl + Shift + F8 : 현재 명령문 끝까지 코드 실행

만약 한 명령문 안에 여러 개의 보조명령문이 포함되어 있을 경우, 주 명령문 안에 포함된 보조 명령문의 각 실행 단계까지 하나씩 확인하기에는 어려움이 있습니다. 그럴 경우, 보조명령문 단계에서 Ctrl + Shift + F8 키를 누르면 보조명령문의 마지막 단계로 한 번에 이동하면서 다시 주 명령문으로 돌아올 수 있습니다.

4. Ctrl + F8 : 커서 지점까지 실행

키보드 Ctrl + F8를 누르면 현재 커서 위치까지 명령문이 실행됩니다. 따라서 명령문의 특정 지점까지만 빠르게 실행하고 싶다면, 특정 지점으로 마우스 커서를 클릭한 뒤, Ctrl + F8 키를 누르면 커서가 위치한 지점까지 명령문을 실행할 수 있습니다.

5. F9 : 중단점 설정

명령문 중간에 중단점을 설정하면 명령문 실행 도중 해당 지점에서 명령문이 멈추게 됩니다. 중단점은 매크로 편집기에서 짙은 빨간색으로 표시되며, 한 명령문 안에 여러개의 중단점을 설정할 수 있습니다.

또는 매크로 편집기외 왼쪽 구석에 있는 회색 영역을 클릭하면 중단점을 빠르게 설정할 수도 있습니다. 설정된 중단점을 선택 후 다시 F9 키를 누르거나, 중단점 좌측 회색영역을 클릭하면 설정된 중단점을 해제할 수 있습니다.

6. Ctrl + Shift + F9 : 설정된 모든 중단점 지우기

만약 명령문 안에 여러 개의 중단점이 있어 한 번에 해제하기 어려울 경우, Ctrl + Shift + F9 키를 누르면 통합문서 모듈 안에 포함된 모든 중단점을 한 번에 제거합니다.

7. 마우스로 드래그 : 실행 중 단계 이동

키보드 F8 키를 눌러 각 단계별로 명령문을 실행하다가, 다시 이전 단계로 돌아가거나 변수의 값을 바꿔 다시 실행하고자 할 경우 현재 실행 중인 단계 좌측에 있는 노란색 화살표를 위/아래로 드래그하여 원하는 지점으로 빠르게 이동할 수 있습니다.

엑셀 VBA는 명령문을 실행하는 도중 일반 변수(문자, 숫자)의 값을 변경할 수 있습니다. 뿐만 아니라 명령문을 단계별로 실행하면서 중간에 코드를 수정하고나 새로운 코드를 작성할 수 있습니다.

따라서 명령문을 단계별로 실행하면서 중간에 원치않는 결과가 나왔을 경우, 실행 중인 단계를 이전 단계로 드래그 한 뒤 명령문을 수정하여 원하는 값이 나올 때까지 명령문을 반복 실행 할 수 있습니다.

각 변수를 확인하기 위한 VBA 디버깅 필수 기능

각 단계별로 명령문을 실행하다보면, 현재 내가 보고 있는 변수의 값이 무엇인지 확인해야 합니다. 그럴 경우 마우스 커서를 해당 변수 위로 가져가면 입력된 값을 확인할 수 있는데요.

확인해야 할 변수의 종류가 많거나 For문과 같은 반복문에서 변수를 확인해야 할 때, 매번 마우스 커서를 변수 위로 가져가기에는 어려움이 있습니다.

그럴 경우 아래 기능을 활용하면 변수에 입력된 값을 보다 편리하게 확인할 수 있습니다.

1. 직접실행창 활용

직접실행창을 활용하면 특정 변수의 값을 빠르고 편리하게 확인할 수 있습니다. 뿐만 아니라 직접실행창을 통해 함수를 바로 실행할 수도 있습니다.

직접실행창은 매크로편집기의 [보기] - [직접실행창]을 클릭하면 활성화됩니다.

직접실행창으로 변수를 확인하는 방법은 간단합니다. ? 를 입력한 뒤, ? 뒤로 보고자 하는 변수 또는 함수를 입력합니다.

예를 들어 아래 구문을 직접실행창에 입력하면 현재 실행 중인 통합문서의 이름이 표시됩니다.

?ThisWorkBook.Name

명령문을 단계별로 실행하는 도중 직접실행창에 보고 싶은 변수를 ? 뒤에 입력하면 현재 변수의 값을 확인할 수 있습니다.

2. Debug.Print 구문 활용

Debug.Print 구문을 사용하면 명령문을 실행하면서 실시간으로 특정 변수의 값을 직접실행창에 빠르게 출력할 수 있습니다.

예를 들어 아래 명령문과 같이 For 문 중간에 Debug.Print 구문을 추가하면, For 문으로 변하는 i의 값이 직접실행창에 출력됩니다.

Sub DebugPrintTest()
 
Dim i As Long
 
For i = 1 To 10
Debug.Print i
Next
 
End Sub

3. 지역창 활용

직접실행창이나 Debug.Print 구문은 변수가 숫자/문자인 경우에만 확인 가능합니다. 또한 확인해야 할 변수 종류가 많을 경우, 매번 직접실행창으로 확인하기 어려운 경우가 많은데요.

그럴 경우, 지역창을 활용하면 현재 실행 중인 프로시져에 사용된 모든 변수의 목록과 값, 그리고 개체 안에 포함된 모든 속성을 한 번에 확인할 수 있습니다.

4. 조사식창 활용

지역창은 현재 실행 중인 프로시져(명령문)에 사용된 변수만 표시합니다. 만약 현재 실행 중인 명령문을 벗어나 전체 모듈에 사용된 전역변수를 실시간으로 확인해야 할 경우 '조사식창'을 사용합니다.

조사식창을 사용하는 방법은 매우 간단합니다. 보고 싶은 변수를 조사식창으로 드래그하거나, 변수를 우클릭 - [조사식 추가]를 클릭하면 조사식창에 변수가 등록됩니다.

조사식창을 활용하면 특정 조건에 도착했을 때, 명령문을 중단(★)할 수 있습니다. 예를 들어, For 문으로 1부터 100까지 반복하는 상황을 가정하겠습니다.

만약 값이 99일 때 멈춰야 할 때, F8키로 한 단계씩 실행한다면 99번을 반복해야 하는데요. 이런 경우, 조사식창을 사용하면 특정 조건일 경우에 명령문을 중단할 수 있습니다.

5. MsgBox 함수 활용 (메시지박스)

종종 직접실행창이나 지역창을 통해 변수 확인이 어려울 경우, MsgBox 를 사용하여 변수를 확인할 수 있습니다.

단, MsgBox는 매번 '확인' 버튼을 눌러 다음 단계로 진행해야 하기 때문에 명령문의 중간 진행 단계를 확인하는 정도로만 사용하는 것이 좋습니다. 예를 들어, MsgBox를 For 반복문 안에 사용할 경우, 메시지박스가 반복 출력되면서 디버깅에 문제가 발생하므로 사용 시 주의합니다.

간단한 예제로 아래 명령문을 모듈에 붙여넣기 한 뒤 실행합니다.

Sub MsgboxTest()
 
Dim i As Long
Dim s As String
For i = 1 To 10
s = s & i
MsgBox s   'For 문과 같은 반복문 중간에 MsgBox를 사용할 경우 주의해야 합니다.
s = s & ","
Next
 
End Sub

For 문 사이에 MsgBox가 포함되어 있어 메시지박스가 10번 출력되고, 메시지박스가 출력 될 때마다 확인버튼을 클릭해야만 디버깅을 완료할 수 있습니다.

VBA 숙련자들이 활용하는 디버깅 꿀팁! - Debug.Assert 구문

명령문의 복잡도(난이도)와 발생하는 오류의 횟수와 종류는 비례합니다. 따라서 복잡한 명령문을 작성 할 수록, 디버깅을 빠르게 처리하는 것이 더욱 중요해집니다.

어느 정도 명령문 작성에 익숙해지면, 오류가 발생하는 대표적인 몇몇 상황 (예: 파일 경로가 비어있음, 배열이 비어있음 등)을 미리 예상하고 사전에 오류를 방지할 수 있습니다.

그럴 때 사용할 수 있는 구문이 바로 Debug.Assert 구문입니다. Debug.Assert 구문은 Debug.Assert 구문 뒤에 입력된 조건을 만족하지 않을 경우, 명령문을 강제로 중단합니다.

예를 들어 아래 명령문을 복사하여 모듈 안에 붙여넣기 한 뒤 실행합니다.

Sub DebugAssertTest()
 
ActiveSheet.UsedRange.Delete
For i = 1 To 30
'i가 1 <= i <= 24가 아닐 경우, 명령문이 강제 중단됩니다.
Debug.Assert i >= 1 And i <= 24
Range("A" & i).Value = i
Next
 
End Sub

명령문을 실행하면 Debug.Assert 구문을 통해 i가 1과 24 사이일 때만 통과하고, i가 24보다 클 경우 Debug.Assert 구문으로 막히면서 명령문이 중단됩니다.

Syntax Error를 피하는 가장 쉬운 방법 - Option Explicit

모듈 최상단에 Option Explicit (명시적 선언)을 추가해주면, 해당 모듈에 사용되는 변수를 Dim 으로 반드시 선언되도록 강제할 수 있습니다.

예를 들어, 모듈에 Option Explicit을 선언하지 않고 아래 명령문을 실행해보겠습니다.

Sub OptionExplicitTest()
 
'MsgBox가 잘 출력되지만, s와 i가 어떤 형태의 값인지 지정하지 않았습니다.
MsgBox s & i
 
End Sub

그러면 비어있는 메시지박스가 출력됩니다. 물론 명령문은 잘 동작하지만, 명령문에 사용된 s와 i가 무엇인지?(문자인지, 숫자인지, 배열인지, 등..)을 인지할 수 없어 VBA에서는 자동으로 개체변수로 인식하게 되고 따라서 빈칸을 출력하는데요.

이번에는 명령문 상단에 Option Explicit을 선언한 뒤 명령문을 실행해보겠습니다.

Option Explicit
 
Sub OptionExplicitTest()
 
'Option Explicit을 선언한 상태에서, s와 i 변수를 Dim으로 지정하지 않아 오류가 출력됩니다.
MsgBox s & i
 
End Sub

이번에는 변수가 정의되지 않았다는 오류메시지가 나오면서 명령문 실행이 중단됩니다.

따라서 명령문 앞에 Dim으로 각 s 와 i 를 문자와 정수로 선언한 뒤, 명령문을 다시 실행합니다.

Option Explicit
 
Sub OptionExplicitTest()
 
Dim s As String
Dim i As Long
 
MsgBox s & i
 
End Sub

이번에는 s는 문자(빈칸), i는 정수(0) 으로 올바르게 인식되어 메시지박스로 0이 출력되는 것을 확인할 수 있습니다.