엑셀 VBA 매크로를 실행할 때 다음 메시지가 나타날 수 있습니다.
런타임 오류 '7':
메모리가 부족합니다.오류 7은 현재 작업에 필요한 메모리를 확보하지 못했을 때 발생할 수 있습니다. 열려 있는 파일과 프로그램이 너무 많거나, 큰 배열을 한 번에 만들거나, 반복문에서 통합 문서를 계속 열어 두는 코드가 원인일 수 있습니다.
다만 오류 메시지만 보고 모든 원인을 ‘메모리 누수’라고 판단해서는 안 됩니다. 먼저 VBA 편집기에서 어느 줄이 노란색으로 표시되는지 확인해야 합니다.
 |
| 오류가 발생한 코드 위치를 확인한 뒤 큰 배열, 반복 파일 열기와 복사 작업을 순서대로 점검합니다. |
이 글은 Microsoft의 VBA 오류 7, 동적 배열 Erase, Workbook.Close와 Excel Range.Value2 공식 문서를 기준으로 작성했습니다. 특정 파일 수, 데이터 행 수 또는 속도 개선 비율을 보장하지 않습니다.
오류 창에서 ‘디버그’를 눌러 멈춘 줄 확인
오류가 나타나면 바로 종료를 누르지 말고 디버그를 선택하세요. VBA 편집기가 열리면서 오류가 발생한 코드가 노란색으로 표시됩니다.
노란색으로 표시된 줄에 따라 확인할 부분이 달라집니다.
ReDim 또는 큰 범위를 배열로 가져오는 줄한 번에 만드는 배열의 크기와 처리 범위를 줄여야 합니다.
Workbooks.Open이 들어 있는 반복문이전 통합 문서를 닫지 않은 채 다음 파일을 계속 열고 있는지 확인합니다.
Range.Copy 또는 그림·도형을 복사하는 줄반복적인 클립보드 사용을 줄이고 값 직접 대입이 가능한지 확인합니다.
열려 있는 Excel 파일과 다른 프로그램을 닫고 Excel을 다시 실행합니다.
매크로 시작 직후 오류가 발생하는 경우
코드가 데이터를 처리하기도 전에 오류가 발생한다면 먼저 현재 Excel 환경을 정리합니다.
- 작업 중인 파일을 저장합니다.
- 필요하지 않은 Excel 통합 문서를 닫습니다.
- 다른 Office 프로그램과 메모리를 많이 사용하는 프로그램을 닫습니다.
- Excel을 완전히 종료한 뒤 다시 실행합니다.
- 원본이 아닌 파일 복사본에서 매크로를 다시 실행합니다.
Excel을 다시 실행한 직후에는 정상인데 같은 매크로를 여러 번 실행한 뒤 오류가 반복된다면, 코드에서 파일·배열·복사 작업이 계속 누적되는지 확인해야 합니다.
큰 범위를 한 번에 배열로 가져오는 줄에서 멈춘 경우
다음과 같이 워크시트의 넓은 범위를 한 번에 배열로 가져오면 해당 범위의 값을 담을 저장 공간이 한꺼번에 필요합니다.
allData = ws.Range("A1:XFD1048576").Value2위 코드는 Excel 워크시트 전체에 가까운 범위를 요청하므로 실제 데이터가 적더라도 매우 큰 배열을 만들려고 시도할 수 있습니다.
먼저 실제 데이터의 마지막 행과 마지막 열만 사용하도록 범위를 제한해야 합니다. 그래도 데이터가 크다면 여러 구간으로 나누어 처리합니다.
데이터를 5,000행씩 나누어 옮기는 예제
아래 예제는 Data 시트의 A열부터 C열까지 값을
Result 시트로 5,000행씩 나누어 옮깁니다.
수식과 서식을 복사하는 코드는 아닙니다. Value2를 사용하므로
셀에 현재 계산된 값만 기록합니다.
Option Explicit
Private Const SOURCE_SHEET As String = "Data"
Private Const TARGET_SHEET As String = "Result"
Private Const FIRST_DATA_ROW As Long = 2
Private Const BLOCK_SIZE As Long = 5000
Public Sub CopyValuesInBlocks()
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Long
Dim startRow As Long
Dim endRow As Long
Dim blockData As Variant
Dim copiedRows As Long
On Error GoTo ErrorHandler
Set sourceSheet = _
ThisWorkbook.Worksheets(SOURCE_SHEET)
Set targetSheet = _
ThisWorkbook.Worksheets(TARGET_SHEET)
lastRow = sourceSheet.Cells( _
sourceSheet.Rows.Count, "A" _
).End(xlUp).Row
If lastRow < FIRST_DATA_ROW Then
MsgBox "옮길 데이터가 없습니다.", _
vbInformation
Exit Sub
End If
For startRow = FIRST_DATA_ROW _
To lastRow Step BLOCK_SIZE
endRow = startRow + BLOCK_SIZE - 1
If endRow > lastRow Then
endRow = lastRow
End If
blockData = sourceSheet.Range( _
"A" & startRow & _
":C" & endRow _
).Value2
targetSheet.Range( _
"A" & startRow _
).Resize( _
UBound(blockData, 1), _
UBound(blockData, 2) _
).Value2 = blockData
copiedRows = copiedRows + _
UBound(blockData, 1)
Erase blockData
Next startRow
MsgBox _
copiedRows & "개 행을 옮겼습니다.", _
vbInformation
Exit Sub
ErrorHandler:
MsgBox _
"오류 번호: " & Err.Number & vbCrLf & _
"오류 내용: " & Err.Description, _
vbExclamation
End Sub코드에서 직접 바꿀 부분
원본 시트 이름과 결과 시트 이름이 다르면 다음 두 줄을 실제 시트 탭 이름으로 변경합니다.
Private Const SOURCE_SHEET As String = "Data"
Private Const TARGET_SHEET As String = "Result"
실제 데이터가 A열부터 F열까지라면 코드의
"A" & startRow & ":C" & endRow에서 C를
F로 변경합니다.
한 구간도 너무 크다면 BLOCK_SIZE를 5,000보다 작은 값으로 변경할
수 있습니다. 무조건 작게 설정하면 반복 횟수가 늘어나므로 파일의 실제 데이터
크기에 맞게 조정해야 합니다.
동적 배열을 사용한 뒤 Erase가 필요한 경우
VBA의 Erase는 배열 종류에 따라 작동 방식이 다릅니다. 동적
배열은 사용하던 저장 공간을 해제하지만, 고정 크기 배열은 요소를 초기화할 뿐
같은 방식으로 메모리 공간을 해제하지 않습니다.
동적 배열 선언과 해제
Dim numbers() As Double
ReDim numbers(1 To 100000)
' 배열 처리 코드
Erase numbers
Erase numbers를 실행한 뒤 같은 배열을 다시 사용하려면
ReDim으로 크기를 다시 지정해야 합니다.
작은 배열이나 프로시저가 곧 끝나는 코드에는 체감 효과가 없을 수 있습니다. 큰 동적 배열을 처리한 뒤 같은 프로시저에서 다른 큰 작업을 계속 실행하는 경우에 우선 확인하세요.
반복문에서 여러 파일을 열다가 오류가 발생하는 경우
반복문 안에서 통합 문서를 열고 작업한 뒤 닫지 않으면 동시에 열린 파일이
늘어납니다. 파일 하나의 작업이 끝난 뒤
Workbook.Close로 닫고 다음 파일을 열어야 합니다.
Set 변수 = Nothing은 객체 변수의 참조를 끊는 코드입니다. 통합
문서 자체를 닫는 작업은 Workbook.Close가 담당합니다. 따라서
Set Nothing만 실행하고 통합 문서를 닫지 않는 방식은 적절하지
않습니다.
파일을 하나씩 열고 닫는 구조 예제
아래 코드는 C:\Data\ 폴더의 .xlsx 파일을 하나씩
읽기 전용으로 열고, 워크시트 개수를 실행 창에 표시한 뒤 닫습니다. 원본
파일의 내용을 변경하거나 저장하지 않습니다.
Option Explicit
Private Const FOLDER_PATH As String = "C:\Data\"
Public Sub OpenFilesOneByOne()
Dim fileName As String
Dim sourceBook As Workbook
Dim processedFiles As Long
Dim errorNumber As Long
Dim errorDescription As String
On Error GoTo ErrorHandler
fileName = Dir$( _
FOLDER_PATH & "*.xlsx" _
)
Do While Len(fileName) > 0
Set sourceBook = Workbooks.Open( _
Filename:=FOLDER_PATH & fileName, _
UpdateLinks:=0, _
ReadOnly:=True, _
AddToMru:=False _
)
Debug.Print _
fileName, _
sourceBook.Worksheets.Count
sourceBook.Close SaveChanges:=False
Set sourceBook = Nothing
processedFiles = processedFiles + 1
fileName = Dir$
Loop
MsgBox _
processedFiles & "개 파일을 확인했습니다.", _
vbInformation
Exit Sub
ErrorHandler:
errorNumber = Err.Number
errorDescription = Err.Description
On Error Resume Next
If Not sourceBook Is Nothing Then
sourceBook.Close SaveChanges:=False
End If
Set sourceBook = Nothing
On Error GoTo 0
MsgBox _
"오류 번호: " & errorNumber & vbCrLf & _
"오류 내용: " & errorDescription, _
vbExclamation
End Sub실제 폴더가 다르면 아래 경로를 변경합니다. 폴더 경로 마지막에는 역슬래시가 있어야 합니다.
Private Const FOLDER_PATH As String = "C:\Data\"오류 처리 구간에서도 열려 있던 통합 문서를 닫도록 작성한 이유는 파일 처리 중간에 오류가 발생했을 때 해당 파일이 열린 상태로 남는 것을 줄이기 위해서입니다.
Range.Copy를 반복하는 코드에서 오류가 발생하는 경우
값만 옮기는 작업이라면 복사와 붙여넣기를 반복하지 않고 원본 범위와 대상
범위의 Value2를 직접 연결할 수 있습니다.
복사와 붙여넣기를 사용하는 코드
sourceRange.Copy
targetRange.PasteSpecial xlPasteValues값을 직접 대입하는 코드
targetRange.Value2 = sourceRange.Value2두 범위의 행과 열 크기가 같아야 합니다. 이 방법은 셀 값만 옮기며 글꼴, 배경색, 테두리, 수식과 열 너비는 복사하지 않습니다.
서식까지 반드시 복사해야 해 Range.Copy를 사용했다면 작업이 끝난
뒤 다음 코드로 Excel의 복사 상태를 종료할 수 있습니다.
Application.CutCopyMode = False이 코드는 복사 상태와 움직이는 테두리를 종료하는 역할입니다. Excel의 메모리를 전부 정리하는 명령은 아닙니다.
모듈이나 프로시저가 지나치게 큰 경우
Microsoft의 오류 7 공식 문서에는 모듈이나 프로시저가 지나치게 큰 경우도 원인으로 표시돼 있습니다. 하나의 프로시저에 파일 열기, 데이터 정리, 계산, 서식, 저장과 오류 처리를 모두 넣었다면 기능별로 나누는 것이 좋습니다.
하나의 큰 프로시저 대신 기능별로 분리
Public Sub RunProcess()
LoadSourceData
CalculateResults
WriteResults
SaveOutput
End Sub
Private Sub LoadSourceData()
' 데이터 가져오기
End Sub
Private Sub CalculateResults()
' 계산하기
End Sub
Private Sub WriteResults()
' 결과 기록하기
End Sub
Private Sub SaveOutput()
' 결과 파일 저장하기
End Sub프로시저를 나누는 것만으로 사용 중인 데이터의 메모리가 자동으로 줄어드는 것은 아닙니다. 하지만 지나치게 큰 프로시저를 줄이고 어느 구간에서 오류가 발생하는지 찾는 데 도움이 됩니다.
자주 오해하는 명령어의 실제 역할
| 명령어 | 실제 역할 | 주의할 점 |
|---|---|---|
Workbook.Close
|
열려 있는 통합 문서를 닫음 | 저장 여부를 명확히 지정해야 함 |
Set 객체 = Nothing
|
객체 변수의 참조를 해제 | 열린 통합 문서를 대신 닫아주지는 않음 |
Erase 배열
|
동적 배열의 저장 공간을 해제 | 고정 배열에서는 작동 방식이 다름 |
Application.CutCopyMode = False
|
복사·잘라내기 상태를 종료 | 전체 메모리를 초기화하는 명령이 아님 |
DoEvents
|
대기 중인 운영체제 이벤트를 처리할 기회를 제공 | 배열이나 객체 메모리를 직접 해제하지 않음 |
코드를 다시 실행하기 전 확인 순서
오류 7이 특정 코드 줄에서 계속 발생한다면 메모리 정리 명령을 무작정 추가하기보다, 해당 줄에서 한 번에 열거나 만들려는 데이터의 크기를 먼저 줄여야 합니다.
댓글 쓰기