VBA 배열(Array) 핵심 정리 — 1차원·2차원·동적 배열 실무 패턴

월말 마감 전에 지점별 매출 시트를 돌면서 For Each로 셀을 하나씩 읽는 매크로를 돌리면, 데이터가 5,000행만 넘어도 체감 속도가 확 떨어집니다. 재무팀에서 이런 코드를 넘겨받았을 때, 저는 먼저 배열로 한 번에 읽었는지를 봅니다. 셀 단위 루프와 배열 루프의 차이는, 강의실에서 같은 파일로 돌려 보면 수강생들이 바로 느껴요. “어? 방금 전이랑 뭐가 달라졌죠?” — 그게 배열입니다.

저도 처음 VBA를 배울 때는 Range("A1")처럼 셀을 직접 찍는 방식만 썼어요. 선배가 “이거 배열로 읽어”라고 알려줬을 때, Dim arr()와 ReDim이 헷갈려서 한나절을 썼습니다. 특히 2차원 배열에서 행과 열 인덱스를 뒤바꿔 쓴 적이 있어요. 화면에는 숫자가 나오는데 합계만 틀리는 — 이 조합이 배열 초보에게 가장 답답한 순간이에요.

제가 강의 때 쓰는 같은 예제 통합문서로 1차원·2차원·동적 배열을 연습해 볼게요. Array()와 Dim의 차이, 시트 범위를 배열로 읽는 패턴, ReDim Preserve 실수까지 정리해요. ChatGPT로 엑셀 VBA 매크로 만들기 글에서 AI로 코드를 받아도, 배열 부분이 틀리면 “런타임 오류 9: 첨자가 적용 범위를 벗어났습니다”가 자주 납니다. 그때 이 글의 인덱스·하한 규칙을 같이 보면 디버깅이 빨라져요.

VBA에서 배열이 필요한 이유

배열은 같은 타입의 값을 연속된 메모리에 묶어 두는 상자예요. 엑셀 시트의 “표”를 코드 안으로 복사해 둔다고 생각하면 됩니다.

방식	장점	단점
셀 직접 참조 (Range)	직관적, 시트와 동기화	행이 많으면 느림, 수식·서식 간섭
배열 (Variant 배열)	읽기·계산이 빠름	인덱스·하한 규칙을 알아야 함
컬렉션·Dictionary	키-값 검색에 유리	선언·초기화가 배열보다 무거움

Microsoft VBA 문서에서도 대량 데이터 처리 시 범위를 배열로 읽고 메모리에서 처리한 뒤 한 번에 쓰기를 권장합니다. 3,000행 이상 집계·검증 매크로라면 배열 전환이 체감 차이를 만듭니다.

저는 강의에서 이렇게 말해요. “시트는 화면용, 배열은 계산용.” 보고서 서식은 시트에 두고, 합계·검증·필터링은 배열에서 처리하면 매크로가 안정적입니다. 회사 파일에서 매크로가 느리다고 하면, 셀 단위 For 루프가 원인인 경우가 많아요.

따라 할 예제 데이터 (지점·월·매출 표)

아래 표를 원본 시트 A1:D7에 입력하세요.

A: 지점	B: 월	C: 품목	D: 매출
강남	3	노트북	2400000
강남	3	모니터	1500000
강남	4	키보드	500000
부산	3	노트북	3600000
부산	3	모니터	600000
부산	4	노트북	1200000

집계 시트에는 결과를 쓸 빈 칸을 두고, 아래 코드의 Debug.Print 결과를 즉시 실행 창(Ctrl+G)에서 확인합니다. VBA 편집기는 Alt+F11, 모듈 삽입은 삽입 → 모듈입니다.

1차원 배열 — 선언·초기화·루프

1차원 배열은 한 줄짜리 목록입니다. 지점명 목록, 월 목록처럼 열 하나를 코드로 옮길 때 씁니다.

고정 크기 선언

Sub OneDim_Fixed()
    Dim months(1 To 3) As String
    months(1) = "3월"
    months(2) = "4월"
    months(3) = "5월"

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

Dim months(1 To 3)에서 1이 하한(LBound), 3이 상한(UBound) 입니다. VBA 기본은 하한 0 또는 1인데, 강의에서는 1 To N을 권장해요. 엑셀 행 번호와 맞추기 쉽거든요.

Array()로 빠르게 초기화

Sub OneDim_ArrayFunc()
    Dim months As Variant
    months = Array("3월", "4월", "5월")   ' 하한은 0부터!

    Dim i As Long
    For i = LBound(months) To UBound(months)
        Debug.Print months(i)
    Next i
End Sub

Array()로 만든 배열은 하한이 0입니다. months(1)부터 돌리면 첫 값을 건너뛰거나 오류가 납니다. 루프는 항상 LBound~UBound로 도는 습관이 필요해요.

수강생 실습에서 제일 많이 나오는 질문이 “왜 0부터예요?”입니다. Array()는 VB6 호환 문법이라 0 기준이고, Dim (1 To N)은 우리가 직접 정한 범위예요. 헷갈리면 루프에 LBound/UBound만 쓰면 됩니다.

2차원 배열 — 시트 범위 ↔ 배열

2차원 배열은 행·열이 있는 표입니다. arr(행, 열) 순서로 접근합니다. 엑셀에서 “몇 행 몇 열”이라고 말하는 것과 같아요.

Sub TwoDim_Manual()
    Dim data(1 To 6, 1 To 4) As Variant

    data(1, 1) = "강남": data(1, 2) = 3: data(1, 3) = "노트북": data(1, 4) = 2400000
    data(2, 1) = "강남": data(2, 2) = 3: data(2, 3) = "모니터": data(2, 4) = 1500000
    ' ... 나머지 행 동일 패턴

    Dim r As Long, c As Long
    For r = 1 To 6
        For c = 1 To 4
            Debug.Print data(r, c)
        Next c
    Next r
End Sub

시트 범위를 배열로 읽기

실무에서는 수동 대입 대신 범위를 통째로 배열에 넣습니다.

Sub TwoDim_FromSheet()
    Dim arr As Variant
    arr = Range("A1:D7").Value   ' 7행×4열 2차원 배열

    Dim r As Long
    For r = 2 To UBound(arr, 1)   ' 1행은 머리글, 2행부터 데이터
        If arr(r, 1) = "강남" And arr(r, 2) = 3 Then
            Debug.Print arr(r, 4)  ' 매출
        End If
    Next r
End Sub

Range(...).Value로 읽으면 2차원 Variant 배열이 됩니다. 인덱스는 (행, 열) — arr(3, 4)는 3행 4열, 즉 D3 셀 값이에요.

저도 강의실에서 수강생이 arr(4, 3)과 arr(3, 4)를 뒤바꿔 합계가 틀린 코드를 디버깅한 적이 있습니다. 화면 셀 주소 D3은 열 D(4번째), 행 3이지만, 배열은 arr(행, 열)이라서 arr(3, 4)가 맞습니다. 이 한 가지만 머리에 박아 두면 배열 오류의 절반이 사라져요.

범위를 잘못 잡아 엑셀 #REF! 오류가 난 시트를 배열로 읽으면, 배열 안에도 오류 값이 그대로 들어옵니다. 매크로 돌리기 전에 시트 수식부터 점검하세요.

동적 배열 — ReDim Preserve

데이터 행 수가 매번 바뀔 때는 크기를 모르는 배열이 필요합니다. ReDim으로 크기를 정하고, 늘릴 때는 ReDim Preserve를 씁니다.

Sub Dynamic_Grow()
    Dim items() As String
    Dim n As Long
    n = 0

    ' 가상: 파일에서 읽은 지점명을 하나씩 추가
    Call AddItem(items, n, "강남")
    Call AddItem(items, n, "부산")
    Call AddItem(items, n, "대구")

    Dim i As Long
    For i = LBound(items) To UBound(items)
        Debug.Print items(i)
    Next i
End Sub

Private Sub AddItem(ByRef items() As String, ByRef n As Long, ByVal value As String)
    n = n + 1
    ReDim Preserve items(1 To n)
    items(n) = value
End Sub

ReDim Preserve는 마지막 차원만 크기 변경이 가능합니다. 2차원 배열에서 행만 늘리려면 열 차원을 마지막에 두는 식으로 설계해야 해요.

강의 실습에서 가장 많이 터지는 오류가 여기서 납니다. 학생이 ReDim items(1 To n)으로 크기를 늘린 뒤, Preserve 없이 다시 ReDim을 호출해서 기존 데이터가 전부 날아갔어요. 그리고 “왜 배열이 비죠?”라고 물어봅니다. 규칙은 간단합니다. 이미 데이터가 들어 있는 동적 배열의 크기를 바꿀 때는 Preserve, 처음 만들 때만 일반 ReDim.

Array() vs Dim — 언제 뭘 쓰나

상황	추천	이유
소량 상수 목록 (요일, 월 이름)	Array()	코드가 짧음
행 수가 코드에 고정	Dim arr(1 To N)	하한을 1로 맞추기 쉬움
실행 중 크기가 변함	Dim arr() + ReDim	유연함
시트 범위 통째로	arr = Range(...).Value	읽기 최적
2차원 표 연산	Dim arr(1 To R, 1 To C) 또는 Range	인덱스 명확

Array()는 Variant 배열을 반환합니다. 이후 As String 등으로 제한된 타입 배열에 바로 넣을 수 없어요. 타입을 엄격히 쓰려면 Dim + 루프 대입이 낫습니다.

저는 강의에서 “상수 몇 개면 Array, 표 전체는 Range→배열, 늘어나면 ReDim” 세 가지만 기억하라고 합니다. VBA·매크로 카테고리의 다른 글들도 대부분 이 패턴을 따릅니다.

시트 데이터를 배열로 한 번에 읽고 쓰기

대량 처리의 정석 패턴은 읽기 → 메모리 처리 → 한 번에 쓰기입니다.

Sub Read_Process_Write()
    Dim wsSrc As Worksheet, wsOut As Worksheet
    Set wsSrc = ThisWorkbook.Worksheets("원본")
    Set wsOut = ThisWorkbook.Worksheets("집계")

    Dim arr As Variant
    arr = wsSrc.Range("A1").CurrentRegion.Value

    Dim r As Long, outRow As Long
    outRow = 1
    For r = 2 To UBound(arr, 1)
        If arr(r, 1) = "강남" And arr(r, 2) = 3 Then
            outRow = outRow + 1
            arr(outRow, 1) = arr(r, 3)  ' 품목
            arr(outRow, 2) = arr(r, 4)  ' 매출
        End If
    Next r

    wsOut.Range("A1").Resize(outRow, 2).Value = arr
End Sub

위 예제는 개념 설명용입니다. 실무에서는 결과 전용 배열을 따로 ReDim 하거나, Collection에 담았다가 마지막에 Range에 쓰는 편이 안전해요. 같은 배열을 읽기와 쓰기에 동시에 쓰면 행이 밀릴 수 있습니다.

CurrentRegion은 커서 위치 기준 연속 영역입니다. 빈 행·열이 끼어 있으면 범위가 잘립니다. 월말 파일처럼 데이터 아래에 메모가 있으면 A1:D7처럼 끝 주소를 명시하는 게 낫습니다.

수강생이 자주 묻는 질문 하나 더. “배열에 넣으면 수식도 같이 오나요?” — Value로 읽으면 계산된 값만 옵니다. 수식 문자열이 필요하면 .Formula를 쓰거나 셀을 직접 참조해야 해요. 집계 매크로 대부분은 값만 있으면 충분합니다.

배열 → 시트 쓰기 한 방

Sub Write_Array_To_Sheet()
    Dim outArr(1 To 3, 1 To 2) As Variant
    outArr(1, 1) = "품목": outArr(1, 2) = "매출"
    outArr(2, 1) = "노트북": outArr(2, 2) = 2400000
    outArr(3, 1) = "모니터": outArr(3, 2) = 1500000

    Worksheets("집계").Range("A1").Resize(3, 2).Value = outArr
End Sub

셀 하나씩 Cells(i, j) =로 쓰는 대신 Resize(...).Value = outArr로 한 번에 쓰면 속도가 확 달라집니다. 재무팀 김대리가 만든 지점별 집계 매크로도 이 방식으로 바꾼 뒤 실행 시간이 체감됐다고 했어요.

자주 하는 실수 5개

1. Option Base 0과 1 혼동

모듈 맨 위에 Option Base 1을 쓰면 Dim arr(3)의 하한이 1이 됩니다. 기본은 0이에요. 팀 파일마다 다르면 인덱스가 어긋납니다. 공용 모듈에서는 Option Base를 쓰지 않고 1 To N을 명시하는 편이 안전합니다.

2. Array()는 0부터인데 1부터 루프

Array("강남", "부산")의 첫 값은 arr(0)입니다. For i = 1 To UBound만 쓰면 첫 항목을 건너뜁니다. LBound부터 돌리세요.

3. ReDim Preserve 없이 크기 변경

이미 값이 들어 있는 배열에 ReDim만 하면 내용이 초기화됩니다. 크기를 늘릴 때는 ReDim Preserve를 써야 해요. 강의실에서 이 실수로 “데이터가 사라졌다”는 일이 가장 많습니다.

4. 2차원 배열 행·열 인덱스 뒤바꿈

arr(행, 열)입니다. Cells(행, 열)과 순서는 같지만, 엑셀 주소 B3을 떠올릴 때 “B=열, 3=행”이라 헷갈리기 쉽습니다. arr(3, 2)가 B3이에요.

5. 단일 셀을 배열처럼 다룸

Range("A1").Value는 값 하나일 때 2차원이 아닐 수 있습니다. 항상 2차원으로 다루려면 Range("A1:A1").Value처럼 한 셀도 2차원으로 읽거나, IsArray로 분기하세요.

If IsArray(arr) Then
    Debug.Print arr(1, 1)
Else
    Debug.Print arr
End If

배열 매크로 디버깅 체크리스트

저는 강의·출장 디버깅 때 아래 순서로 봅니다.

1. 즉시 실행 창 — ? LBound(arr, 1), ? UBound(arr, 1)로 행 범위 확인
2. 열 범위 — UBound(arr, 2)로 열 개수 확인
3. 머리글 행 — 루프가 2행부터 시작하는지 (1행은 제목)
4. Preserve — 동적 배열 크기 변경 시 Preserve 여부
5. 시트 범위 — CurrentRegion이 의도한 영역인지, 아래 메모 행이 포함됐는지

“런타임 오류 9″가 뜨면 대부분 인덱스가 UBound를 넘었거나, Array()인데 1부터 접근한 경우입니다. ChatGPT VBA 매크로 가이드의 디버깅 루프에 오류 줄 번호와 함께 LBound/UBound 값을 넣어 요청하면 수정이 빨라집니다.

실무 패턴: 지점별 3월 매출 합계를 배열로 구하기

강의 마지막에는 항상 “배열로 읽어서 조건 합계” 연습을 합니다. SUMIFS로 시트에서 구할 수 있는 값을, 매크로 안에서 같은 로직으로 구해 보는 거예요.

Sub Sum_Gangnam_March()
    Dim arr As Variant
    arr = Worksheets("원본").Range("A1:D7").Value

    Dim r As Long
    Dim total As Double
    total = 0

    For r = 2 To UBound(arr, 1)
        If arr(r, 1) = "강남" And arr(r, 2) = 3 Then
            total = total + arr(r, 4)
        End If
    Next r

    Debug.Print "강남 3월 합계:", total   ' 3900000
End Sub

이 패턴의 장점은 시트 수식과 독립이라는 점입니다. 원본 시트에 임시 열을 추가하지 않아도 되고, 여러 조건을 If로 겹쳐 넣기 쉽습니다. 반대로 조건이 단순하고 결과를 시트에 바로 보여줘야 하면 SUMIFS가 낫습니다. 엑셀 SUMIFS COUNTIFS 가이드와 짝을 이뤄 보면 “시트 함수 vs VBA” 선택 기준이 잡혀요.

재무팀 월마감 파일에서는 ERP에서 받은 CSV를 원본에 붙여 넣고, 위 매크로로 검산하는 식으로 씁니다. 피벗 합계와 VBA 합계가 다르면, 보통 머리글 행 포함·빈 행·텍스트 매출 열이 원인입니다. 배열 루프는 2 To UBound만 지키면 머리글 문제는 바로 걸러집니다.

AI로 배열 코드 받을 때 — 검증·보안

회사 데이터가 들어 있는 통합문서로 AI에 코드를 맡길 때는 가명 처리된 예제 파일로 먼저 테스트하세요. 배열 크기·시트 이름·머리글 행 수는 파일마다 다릅니다. AI가 arr(1 To 100)처럼 임의 크기를 넣으면 실제 행 수와 어긋납니다.

저는 외부 강의에서도 “원본 파일 올리지 말고, 구조만 같은 샘플 10행” 규칙을 강조합니다. 배열 매크로는 한 번에 많은 셀을 건드리기 때문에, 테스트 없이 운영 파일에 돌리면 복구가 어렵습니다. AI가 준 코드에 Option Base 1과 Array()가 섞여 있으면 인덱스가 어긋날 수 있어요. 받은 직후 LBound/UBound를 출력해 보는 습관이 좋습니다.

관련 VBA·엑셀 링크

• ChatGPT로 엑셀 VBA 매크로 만들기
• 엑셀 #REF! 오류 해결
• 업무 프롬프트 작성 공식
• VBA·매크로 카테고리

FAQ

VBA 배열 하한은 0인가요 1인가요?

기본은 0입니다. Dim arr(1 To 5)처럼 쓰면 1부터, Array()는 0부터입니다. Option Base 1은 모듈별 설정이라 팀 파일에서는 LBound/UBound로 확인하는 게 안전해요.

ReDim과 ReDim Preserve 차이는?

ReDim은 배열을 새로 잡고 기존 값을 지웁니다. ReDim Preserve는 크기만 바꾸고 기존 값을 유지합니다. 동적 배열을 키울 때는 Preserve가 필요해요.

Range를 배열로 읽으면 왜 1행이 머리글로 잡히나요?

Range(...).Value는 시트 내용을 그대로 가져옵니다. A1:D1이 제목이면 arr(1, c)가 머리글입니다. 데이터 루프는 2 To UBound(arr, 1)부터 시작하세요.

2차원 배열에서 arr(행, 열) 순서가 헷갈려요

엑셀 Cells(행, 열)과 같습니다. 주소 D3은 3행 4열이므로 arr(3, 4)입니다. 열 문자(B, C, D)를 숫자로 바꾸는 연습을 하면 덜 헷갈립니다.

단일 셀만 읽었는데 IsArray가 False예요

한 셀 Range("A1").Value는 스칼라 값일 수 있습니다. Range("A1:A1").Value로 읽거나 IsArray로 분기하세요.

동적 배열을 2차원으로 늘릴 때 주의점은?

ReDim Preserve는 마지막 차원만 변경 가능합니다. 행을 늘릴 계획이면 Dim arr(1 To Cols, 1 To Rows)처럼 행을 마지막 차원에 두는 설계를 검토하세요.

배열 매크로가 느린데 더 줄일 방법이 있나요?

시트 쓰기를 셀 단위 대신 Resize().Value = 배열로 한 번에 하고, Application.ScreenUpdating = False를 처리 구간에만 켜세요. 읽기도 UsedRange 전체 대신 필요 범위만 잡으면 됩니다.

한 줄 요약

VBA 배열은 시트 표를 코드 안으로 복사해 빠르게 읽고 쓰는 도구입니다. 1차원은 목록, 2차원은 arr(행, 열), 크기가 변하면 ReDim Preserve — 루프는 항상 LBound~UBound로 돌리면 강의실에서 가장 많이 나오는 인덱스·Preserve 실수를 피할 수 있어요.

참고 문서

• Dim statement | Microsoft Learn
• ReDim statement | Microsoft Learn
• Arrays (Visual Basic) | Microsoft Learn

작성자 소개