엑셀 VBA 매크로가 느릴 때 해결법: 셀 반복문을 배열 처리로 바꾸기

엑셀 VBA 매크로가 데이터 몇 행에서는 빠르게 실행되지만 행이 늘어나면서 느려진다면, 반복문 안에서 워크시트의 셀을 계속 읽고 쓰는지 먼저 확인해야 합니다.

화면 갱신을 끄는 것만으로는 셀 접근 횟수가 줄어들지 않습니다. 여러 셀의 값을 배열로 한 번에 가져오고, VBA 안에서 계산한 결과를 다시 한 번에 기록하면 Excel과 VBA 사이에서 데이터가 오가는 횟수를 줄일 수 있습니다.

이 글은 Microsoft의 Excel VBA 성능 최적화 문서를 기준으로 작성한 예제입니다. 실제 업무 파일에 적용하기 전에 파일을 복사하고, 복사본에서 시트 이름과 열 위치를 확인하세요. 특정 실행 시간이나 속도 개선 비율을 보장하는 코드는 아닙니다.

워크시트의 셀을 한 행씩 읽고 쓰는 대신 범위를 배열로 가져와 계산 결과를 한 번에 기록하는 방식입니다.

예제에서 계산할 데이터

예제는 이름이 매출인 워크시트에서 수량과 단가를 곱해 금액을 계산합니다.

A열: 주문번호가 입력된 기준 열
B열: 수량
C열: 단가
D열: 수량 × 단가 결과
1행: 제목
2행부터: 실제 데이터

D열의 기존 내용은 덮어씁니다
아래 코드는 D2부터 마지막 데이터 행까지 값이나 수식을 계산 결과로 교체합니다. D열에 보존해야 할 내용이 있다면 먼저 빈 열을 선택하고 코드의 결과 열을 변경하세요.

느려질 수 있는 셀 단위 반복 구조

아래와 같이 반복문 안에서 B열과 C열을 읽고 D열에 결과를 쓰면 한 행을 처리할 때마다 워크시트에 여러 번 접근하게 됩니다.

For i = 2 To lastRow

    quantity = ws.Cells(i, "B").Value2
    unitPrice = ws.Cells(i, "C").Value2

    If IsError(quantity) Or IsError(unitPrice) Then
        ws.Cells(i, "D").Value2 = vbNullString

    ElseIf IsNumeric(quantity) And IsNumeric(unitPrice) Then
        ws.Cells(i, "D").Value2 = _
            CDbl(quantity) * CDbl(unitPrice)

    Else
        ws.Cells(i, "D").Value2 = vbNullString
    End If

Next i

이 구조가 틀렸다는 뜻은 아닙니다. 데이터가 적고 실행 시간이 이미 짧다면 그대로 사용해도 됩니다. 하지만 처리 행이 많아질수록 셀을 읽고 쓰는 횟수도 함께 증가합니다.

배열 방식은 무엇이 다른가?

배열 방식은 작업 순서가 다음과 같습니다.

B열과 C열의 전체 데이터를 한 번에 VBA 배열로 가져옵니다.
워크시트가 아닌 배열 안에서 수량과 단가를 계산합니다.
계산 결과 배열을 D열에 한 번에 기록합니다.

이 방법은 계산식 자체를 빠르게 만드는 것이 아니라, Excel 워크시트와 VBA 사이에서 값을 주고받는 횟수를 줄이는 방법입니다.

배열 처리와 설정 복구를 포함한 전체 코드

아래 코드는 실행 전에 화면 갱신, 이벤트와 계산 모드의 현재 상태를 저장합니다. 실행이 끝나거나 중간에 오류가 발생하면 저장해 둔 상태로 되돌립니다.

Option Explicit

Private Const SHEET_NAME As String = "매출"
Private Const FIRST_DATA_ROW As Long = 2
Private Const LAST_ROW_KEY_COLUMN As String = "A"
Private Const INPUT_FIRST_COLUMN As String = "B"
Private Const INPUT_LAST_COLUMN As String = "C"
Private Const OUTPUT_COLUMN As String = "D"

Public Sub CalculateAmountWithArray()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowCount As Long
    Dim sourceData As Variant
    Dim resultData() As Variant
    Dim i As Long

    Dim oldCalculation As XlCalculation
    Dim oldScreenUpdating As Boolean
    Dim oldEnableEvents As Boolean
    Dim appStateChanged As Boolean

    Dim errorNumber As Long
    Dim errorDescription As String

    On Error GoTo ErrorHandler

    Set ws = ThisWorkbook.Worksheets(SHEET_NAME)

    lastRow = ws.Cells( _
        ws.Rows.Count, _
        LAST_ROW_KEY_COLUMN _
    ).End(xlUp).Row

    If lastRow < FIRST_DATA_ROW Then
        MsgBox "처리할 데이터가 없습니다.", _
               vbInformation
        Exit Sub
    End If

    sourceData = ws.Range( _
        INPUT_FIRST_COLUMN & FIRST_DATA_ROW & ":" & _
        INPUT_LAST_COLUMN & lastRow _
    ).Value2

    rowCount = UBound(sourceData, 1)

    ReDim resultData( _
        1 To rowCount, _
        1 To 1 _
    )

    oldCalculation = Application.Calculation
    oldScreenUpdating = Application.ScreenUpdating
    oldEnableEvents = Application.EnableEvents
    appStateChanged = True

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    For i = 1 To rowCount

        If IsError(sourceData(i, 1)) _
           Or IsError(sourceData(i, 2)) Then

            resultData(i, 1) = vbNullString

        ElseIf Len(CStr(sourceData(i, 1))) = 0 _
           Or Len(CStr(sourceData(i, 2))) = 0 Then

            resultData(i, 1) = vbNullString

        ElseIf IsNumeric(sourceData(i, 1)) _
           And IsNumeric(sourceData(i, 2)) Then

            resultData(i, 1) = _
                CDbl(sourceData(i, 1)) * _
                CDbl(sourceData(i, 2))

        Else
            resultData(i, 1) = vbNullString
        End If

    Next i

    ws.Range( _
        OUTPUT_COLUMN & FIRST_DATA_ROW _
    ).Resize( _
        rowCount, _
        1 _
    ).Value2 = resultData

    GoTo CleanExit

ErrorHandler:

    errorNumber = Err.Number
    errorDescription = Err.Description

    Resume CleanExit

CleanExit:

    On Error Resume Next

    If appStateChanged Then
        Application.Calculation = oldCalculation
        Application.EnableEvents = oldEnableEvents
        Application.ScreenUpdating = oldScreenUpdating
    End If

    On Error GoTo 0

    If errorNumber <> 0 Then

        MsgBox _
            "처리 중 오류가 발생했습니다." & vbCrLf & _
            "오류 번호: " & errorNumber & vbCrLf & _
            "오류 내용: " & errorDescription, _
            vbExclamation

    Else

        MsgBox _
            rowCount & "개 행의 금액 계산이 완료되었습니다.", _
            vbInformation

    End If

End Sub

내 파일에 맞게 바꿔야 하는 부분

코드 상단의 여섯 줄만 실제 파일 구조에 맞게 변경하면 됩니다.

워크시트 이름

Private Const SHEET_NAME As String = "매출"

실제 시트 이름이 매출원장이라면 "매출"을 "매출원장"으로 바꿉니다. 시트 탭에 표시되는 이름과 글자, 띄어쓰기가 정확히 같아야 합니다.

데이터가 시작되는 행

Private Const FIRST_DATA_ROW As Long = 2

제목이 두 줄이고 실제 데이터가 3행부터 시작한다면 숫자를 3으로 변경합니다.

마지막 행을 찾을 기준 열

Private Const LAST_ROW_KEY_COLUMN As String = "A"

A열에 빈칸이 많다면 모든 데이터 행에 값이 들어 있는 다른 열로 변경하세요. 기준 열에 값이 없으면 실제 데이터보다 위쪽 행까지만 처리될 수 있습니다.

입력 열과 결과 열

Private Const INPUT_FIRST_COLUMN As String = "B"
Private Const INPUT_LAST_COLUMN As String = "C"
Private Const OUTPUT_COLUMN As String = "D"

이 예제는 B열과 C열을 연속된 두 열로 한 번에 읽습니다. 수량과 단가가 서로 떨어진 열에 있다면 범위와 배열 위치를 함께 수정해야 하므로 코드를 그대로 적용할 수 없습니다.

코드를 Excel에 넣고 실행하는 순서

원본 Excel 파일을 복사합니다.
복사본을 .xlsm 형식으로 저장합니다.
Alt + F11을 눌러 VBA 편집기를 엽니다.
상단 메뉴에서 삽입 → 모듈을 선택합니다.
새 모듈에 전체 코드를 붙여 넣습니다.
코드 상단의 시트 이름과 열 위치를 실제 파일에 맞게 수정합니다.
VBA 편집기를 닫고 Excel로 돌아옵니다.
Alt + F8을 누릅니다.
CalculateAmountWithArray를 선택하고 실행을 누릅니다.

인터넷이나 이메일에서 받은 출처를 알 수 없는 매크로 파일은 실행하지 마세요. 매크로 사용을 허용하기 전에는 파일을 보낸 사람과 파일의 목적을 확인해야 합니다.

코드가 처리하는 값과 처리하지 않는 값

B열과 C열에 숫자가 들어 있으면 곱한 결과를 D열에 기록합니다. 다음과 같은 행에는 빈 값을 기록합니다.

B열이나 C열이 비어 있는 행
숫자가 아닌 글자가 들어 있는 행
#N/A, #VALUE! 같은 오류 값이 있는 행

B열이나 C열에 수식이 들어 있다면 Value2는 수식 문장이 아니라 현재 계산된 결과값을 가져옵니다. D열에도 수식이 아닌 계산 결과값이 기록됩니다.

오류가 발생했을 때 확인할 위치

오류 9: 첨자가 유효한 범위를 벗어났습니다

코드에 입력된 워크시트 이름이 실제 시트 탭 이름과 다를 때 주로 확인해야 합니다.

Private Const SHEET_NAME As String = "매출"

숨은 공백, 괄호와 숫자까지 실제 시트 이름과 정확히 일치하는지 확인하세요.

오류 1004가 표시되는 경우

결과를 기록할 D열에 병합된 셀이 있거나 워크시트가 보호돼 있으면 범위에 값을 기록하는 부분에서 오류가 발생할 수 있습니다. 병합 상태와 시트 보호 여부를 먼저 확인하세요.

실행 후 자동 계산이 되지 않는 경우

예제 코드는 오류가 발생해도 기존 계산 모드로 돌아가도록 작성돼 있습니다. 하지만 VBA 편집기의 중지 버튼을 누르거나 Excel을 강제로 종료하면 복구 구간이 실행되지 않을 수 있습니다.

이 경우 파일 → 옵션 → 수식 → 통합 문서 계산에서 자동을 선택하세요. 수식 계산 문제의 자세한 점검 순서는 엑셀 수식이 계산되지 않을 때 글에서 확인할 수 있습니다.

배열로 바꿔도 속도가 그대로일 수 있는 경우

배열 처리는 반복적으로 셀을 읽고 쓰는 구간을 줄이는 방법입니다. 매크로가 느린 원인이 아래 작업이라면 배열만으로는 충분하지 않을 수 있습니다.

외부 통합 문서를 반복해서 열고 닫는 작업
네트워크나 웹 서버의 응답을 기다리는 작업
복잡한 수식과 외부 연결의 재계산
대량의 파일 복사와 저장 작업
조건부 서식이 지나치게 넓게 적용된 통합 문서
이벤트 프로시저가 반복해서 실행되는 구조

매크로 한 부분만 느린 것이 아니라 Excel 파일 전체의 열기, 입력과 계산이 모두 느리다면 대용량 엑셀 파일이 느릴 때 확인할 항목 을 먼저 확인하는 편이 적절합니다.

이 예제를 적용하기 전 마지막 확인

□ 원본 파일이 아닌 복사본인지

□ 파일을 .xlsm으로 저장했는지

□ 실제 워크시트 이름을 입력했는지

□ A열이 마지막 행을 찾기에 적합한지

□ B열과 C열에 수량과 단가가 있는지

□ D열의 기존 내용을 삭제해도 되는지

□ 코드 실행 후 결과 일부를 원본 값과 비교했는지

이 코드에 적용한 Microsoft VBA 문서

배열로 범위를 한 번에 읽고 쓰는 방식, 실행 중 화면 갱신·계산·이벤트를 중지한 뒤 기존 상태로 복원하는 방식과 Value2 사용 근거는 다음 Microsoft Learn 문서에서 확인할 수 있습니다.

엑셀 VBA 매크로가 느릴 때 해결법: 셀 반복문을 배열 처리로 바꾸기

예제에서 계산할 데이터

느려질 수 있는 셀 단위 반복 구조

배열 방식은 무엇이 다른가?

배열 처리와 설정 복구를 포함한 전체 코드

내 파일에 맞게 바꿔야 하는 부분

워크시트 이름

데이터가 시작되는 행

마지막 행을 찾을 기준 열

입력 열과 결과 열

코드를 Excel에 넣고 실행하는 순서

코드가 처리하는 값과 처리하지 않는 값

오류가 발생했을 때 확인할 위치

오류 9: 첨자가 유효한 범위를 벗어났습니다

오류 1004가 표시되는 경우

실행 후 자동 계산이 되지 않는 경우

배열로 바꿔도 속도가 그대로일 수 있는 경우

이 예제를 적용하기 전 마지막 확인

이 코드에 적용한 Microsoft VBA 문서

Post a Comment

댓글 쓰기

문의하기 양식

엑셀 VBA 매크로가 느릴 때 해결법: 셀 반복문을 배열 처리로 바꾸기

예제에서 계산할 데이터

느려질 수 있는 셀 단위 반복 구조

배열 방식은 무엇이 다른가?

배열 처리와 설정 복구를 포함한 전체 코드

내 파일에 맞게 바꿔야 하는 부분

워크시트 이름

데이터가 시작되는 행

마지막 행을 찾을 기준 열

입력 열과 결과 열

코드를 Excel에 넣고 실행하는 순서

코드가 처리하는 값과 처리하지 않는 값

오류가 발생했을 때 확인할 위치

오류 9: 첨자가 유효한 범위를 벗어났습니다

오류 1004가 표시되는 경우

실행 후 자동 계산이 되지 않는 경우

배열로 바꿔도 속도가 그대로일 수 있는 경우

이 예제를 적용하기 전 마지막 확인

이 코드에 적용한 Microsoft VBA 문서

You Might Like

Post a Comment

댓글 쓰기

문의하기 양식