VBA 배열 경계 검산: 강남 3월 합계와 ReDim Preserve 예제

배열 예제에서 행·열 경계를 잘못 설명하면 합계가 맞아도 다음 변경에서 오류가 납니다. 여기서는 머리글을 포함한 A1:D7을 읽고, 강남 지점의 3월 두 행만 원래 입력 순서대로 출력합니다. 고정 크기 출력 배열과 마지막 차원만 확장하는 동적 배열을 별개의 코드로 보여 줍니다.

성능 비교나 실제 VBA 실행은 하지 않았습니다. Python으로 가상 입력의 합계·일치 건수·경계 조건을 독립 검산하고 VBA 텍스트를 정적으로 확인했습니다. Windows Excel에서의 컴파일과 셀 출력 확인은 아래 절차에 남겨 두었습니다.

A1:D7과 예상 출력 다섯 셀

원본 시트에 다음 표를 입력하고 같은 통합문서에 집계 시트를 만듭니다. 실제 회사 자료가 아닌 여섯 행의 가상 값입니다.

조건에 맞는 합계는 2400000+1500000입니다. 결과를 정렬하는 코드는 없으므로 원본에서 먼저 나온 노트북이 먼저 출력됩니다. A1:D7의 D3은 데이터 두 번째 행의 금액 900000이며, 머리글까지 포함한 배열에서는 raw(3,4)에 대응합니다.

배열 경계와 출력 크기를 먼저 정하기

여러 셀이 있는 연속 범위의 Range.Value는 값을 담은 2차원 배열을 반환합니다. LBound는 지정한 배열 차원의 가장 작은 첨자를 반환합니다. 읽은 배열과 직접 선언한 배열에 같은 하한을 무조건 적용하지 말고 실제 LBound·UBound를 확인해야 합니다.

이 예제는 A1:D7을 읽은 뒤 행 1..7, 열 1..4인지 검사하고 다른 크기면 중단합니다. 헤더는 첫 행이므로 순회는 LBound(raw,1)+1에서 시작합니다. 행이 최대 여섯 건 일치할 때 합계 행 하나까지 담도록 출력은 7×2로 선언합니다. 일치하는 행이 없으면 B1은 0, 그 아래는 빈칸이 됩니다. 고정 범위 밖의 행을 암묵적으로 포함하지 않습니다.

수식처럼 보이는 텍스트는 복사 전에 중단

출력 A열에 옮길 품목 문자열은 왼쪽 일반 공백을 제외한 첫 글자가 =, +, -, @이면 거부합니다. 탭·줄바꿈을 포함한 ASCII 제어문자(0~31, 127)도 허용하지 않습니다. 조건에 맞는 행뿐 아니라 원본 여섯 행을 모두 검사한 후에만 출력합니다. 원본!C2에 문자열 =1+1을 넣는 반례는 집계 A1:B7을 쓰기 전에 중단해야 합니다.

C2를 수식 예시: =1+1처럼 설명으로 시작하는 라벨 또는 원래 노트북으로 복구하고 다시 확인합니다. 설명 라벨로 바꿔도 해당 행의 매출은 2400000, 전체 합계는 3900000입니다. 수식형 문자열을 그대로 보존해 옮기는 기능은 제공하지 않습니다. 이 검사는 Value2로 읽은 값에 대한 입력 계약이며 원본 셀에 이미 있는 Excel 수식의 실행을 차단하지 않습니다.

전체 집계 코드와 동적 배열 실습

Option Explicit

Public Sub ArrayWorkedExample()
    Dim src As Worksheet, dst As Worksheet, raw As Variant, output(1 To 7, 1 To 2) As Variant
    Dim r As Long, hits As Long, m As Double, amount As Double, total As Double
    Dim savedEvents As Boolean, errorText As String
    savedEvents = Application.EnableEvents
    On Error GoTo Failed
    Set src = ThisWorkbook.Worksheets("원본")
    Set dst = ThisWorkbook.Worksheets("집계")
    raw = src.Range("A1:D7").Value2
    Debug.Print "rows=" & LBound(raw, 1) & ".." & UBound(raw, 1) & "; cols=" & LBound(raw, 2) & ".." & UBound(raw, 2)
    If LBound(raw, 1) <> 1 Or UBound(raw, 1) <> 7 Or LBound(raw, 2) <> 1 Or UBound(raw, 2) <> 4 Then
        Err.Raise vbObjectError + 401, , "Expected a 7 by 4 input array"
    End If
    If CStr(raw(1, 1)) <> "지점" Or CStr(raw(1, 2)) <> "월" Or CStr(raw(1, 3)) <> "품목" Or CStr(raw(1, 4)) <> "매출" Then
        Err.Raise vbObjectError + 402, , "Header mismatch"
    End If
    For r = LBound(raw, 1) + 1 To UBound(raw, 1)
        If IsError(raw(r, 1)) Or IsError(raw(r, 3)) Then Err.Raise vbObjectError + 403, , "Error in branch or item"
        If Len(Trim$(CStr(raw(r, 1)))) = 0 Or Len(Trim$(CStr(raw(r, 3)))) = 0 Then Err.Raise vbObjectError + 404, , "Blank branch or item"
        RejectFormulaLikeText raw(r, 3), "C" & r
        m = WholeAmount(raw(r, 2), "B" & r)
        If m < 1 Or m > 12 Then Err.Raise vbObjectError + 405, , "Month must be 1..12"
        amount = WholeAmount(raw(r, 4), "D" & r)
        If CStr(raw(r, 1)) = "강남" And m = 3 Then
            hits = hits + 1
            output(hits + 1, 1) = raw(r, 3)
            output(hits + 1, 2) = amount
            total = total + amount
        End If
    Next r
    output(1, 1) = "강남 3월 합계"
    output(1, 2) = total
    If dst.ProtectContents Then Err.Raise vbObjectError + 406, , "Output sheet is protected"
    If IsNull(dst.Range("A1:B7").MergeCells) Then Err.Raise vbObjectError + 407, , "Merged output cells"
    If dst.Range("A1:B7").MergeCells Then Err.Raise vbObjectError + 408, , "Merged output cells"
    Application.EnableEvents = False
    dst.Range("A1:B7").Value2 = output
    Application.EnableEvents = savedEvents
    Debug.Print "matches=" & hits & "; total=" & total
    MsgBox "Compare the output with the expected input order.", vbInformation
    Exit Sub
Failed:
    errorText = Err.Number & " / " & Err.Description
    Application.EnableEvents = savedEvents
    MsgBox "Stopped: " & errorText, vbExclamation
End Sub

Public Sub DynamicArrayDemo()
    Dim values() As Variant
    ReDim values(1 To 2, 1 To 2)
    values(1, 1) = "A"
    values(2, 2) = "B"
    ReDim Preserve values(1 To 2, 1 To 3)
    Debug.Print values(1, 1); values(2, 2); UBound(values, 2)
    Debug.Assert values(1, 1) = "A"
    Debug.Assert values(2, 2) = "B"
    Debug.Assert UBound(values, 2) = 3
End Sub
Private Function WholeAmount(ByVal v As Variant, ByVal address As String) As Double
    Select Case VarType(v)
        Case vbByte, vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal
        Case Else
            Err.Raise vbObjectError + 201, , address & ": enter a numeric cell value"
    End Select
    If v < 0 Or v > 1000000000# Then
        Err.Raise vbObjectError + 202, , address & ": allowed range is 0..1000000000"
    End If
    If v <> Fix(v) Then Err.Raise vbObjectError + 203, , address & ": integer required"
    WholeAmount = CDbl(v)
End Function

Private Sub RejectFormulaLikeText(ByVal value As Variant, ByVal address As String)
    Dim text As String, character As Long
    If VarType(value) <> vbString Then Exit Sub
    text = CStr(value)
    For character = 0 To 31
        If InStr(1, text, Chr$(character), vbBinaryCompare) > 0 Then
            Err.Raise vbObjectError + 501, , address & ": control characters are not allowed"
        End If
    Next character
    If InStr(1, text, Chr$(127), vbBinaryCompare) > 0 Then
        Err.Raise vbObjectError + 502, , address & ": control characters are not allowed"
    End If
    text = LTrim$(text)
    Select Case Left$(text, 1)
        Case "=", "+", "-", "@"
            Err.Raise vbObjectError + 503, , address & ": formula-like text is not copied; use a descriptive label"
    End Select
End Sub

출력은 A1:B7과 동일한 크기의 배열을 한 번 전달합니다. 이전 실행의 일치 행이 더 많았더라도 남는 배열 요소가 Empty이므로 아래쪽 예전 값을 그대로 남기지 않는 구조입니다. 출력 범위에 다른 자료가 있다면 이 실습에 사용하지 마세요.

실행과 경계 확인 순서

1. 원본 파일의 복사본을 열고 원본, 집계 시트와 A1:D7을 입력합니다.

2. UTF-8 ArrayExample.bas를 텍스트 편집기에서 열어 표준 모듈에 붙여 넣고 VBA 컴파일을 확인합니다.

3. ArrayWorkedExample을 실행해 Immediate 창의 rows=1..7, cols=1..4와 matches=2, total=3900000을 확인합니다.

4. 집계 A1:B7을 위 표와 비교한 뒤, 별도 복사본의 모든 지점을 다른 이름으로 바꿔 0건·합계 0과 아래 빈칸을 확인합니다.

5. 다른 복사본의 여섯 행 모두를 강남·3월로 바꿔 출력이 마지막 A7:B7까지 들어가는지 확인합니다.

6. DynamicArrayDemo를 실행해 A·B 값이 유지되고 마지막 차원 상한이 3인지 확인합니다.

ReDim Preserve가 허용하는 변경

ReDim Preserve는 마지막 차원의 크기만 바꿀 수 있습니다. Preserve를 쓸 때 하한을 바꾸면 오류가 발생합니다. 따라서 동적 예제는 Dim values() As Variant로 선언한 뒤 1..2, 1..2를 할당하고 두 번째 차원의 상한만 3으로 늘립니다. 고정 크기로 선언한 배열에 ReDim을 적용하는 예제가 아닙니다.

첫 번째 차원을 바꾸는 ReDim Preserve values(1 To 3, 1 To 2)를 데이터가 조용히 초기화되는 방법으로 설명하면 안 됩니다. 이 예제에서 허용하지 않는 변경이며 실제 시험을 하려면 별도 복사본에서 오류로 중단되는지 확인해야 합니다. 원래 데이터를 유지한 채 행 방향으로 확장하려면 배열 설계 또는 새 배열로의 복사 절차가 필요합니다. 이 글의 집계는 입력이 여섯 행으로 고정돼 있어 동적 확장을 사용하지 않습니다.

실패와 복구를 입력 위치로 좁히기

월은 숫자 정수 1..12, 매출은 숫자 정수 0..1000000000만 받습니다. B2가 문자 3이나 0·13이면 출력 전에 중단합니다. B2를 숫자 3으로 다시 입력하고 원래 여섯 행의 기대값부터 확인하세요. 매출에 빈칸·문자·소수·오류가 있으면 해당 D열 셀을 고칩니다. 오류가 나는 행을 무조건 건너뛰는 코드는 넣지 않았습니다.

시트 이름을 잘못 쓰면 배열을 읽기 전 시트 참조에서 실패할 수 있습니다. 범위 오류와 시트 참조 오류를 같은 원인으로 단정하지 말고 멈춘 줄과 메시지를 기록하세요. 출력 시트가 보호됐거나 병합 셀이 있으면 연습용의 비병합 출력 범위를 준비합니다. 저장하지 않은 복사본을 닫으면 입력을 다시 만드는 기준점으로 돌아갈 수 있습니다.

실제 검증 범위와 한계

python verify_contract.py의 12개 검사가 통과했습니다. 기본 두 건 합계, 0건·6건의 출력 용량, 월 경계, 숫자 형식 계약, 동적 선언과 마지막 차원 변경 코드를 확인했고, 빈 라벨·숫자 0·일반 공백·탭 경계 검사를 보완했습니다. Python helper는 None 또는 빈 문자열·일반 ASCII 공백만 있는 문자열을 빈 라벨로 판정하고 숫자 0과 Boolean 라벨은 빈 값으로 오인하지 않습니다. 라벨과 입력 순서는 그대로 보존합니다. 이 보완은 기존 VBA의 Trim$ 빈 라벨 정적 계약에 맞춘 것으로 VBA 코드와 fixture는 바꾸지 않았습니다. VBA 컴파일러나 Excel 메모리 동작을 대신하는 검사가 아니며, 실제 Excel 출력과 오류 처리 결과는 실행자가 별도로 남겨야 합니다.

배열이 반복문보다 몇 배 빠르다는 수치는 제시하지 않습니다. 이 구현도 배열 안에서는 반복문을 사용합니다. 대용량 성능, 단일 셀을 읽었을 때의 값 형태, 여러 떨어진 범위를 합치는 동작, 정렬·중복 집계는 범위 밖입니다. A1:D7과 A1:B7이라는 입출력 계약을 바꿀 때는 경계·기대값·실패 사례를 함께 갱신하세요.

공식 자료

Range.Value property (Excel)

LBound function (Visual Basic for Applications)

ReDim statement (VBA)

출처 원문 수집일: S1 2026-09-24; S2 2026-09-24; S3 2026-09-24.

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기