VBA 코딩 패턴: Range Loop-읽기(Read)

VBA 코딩 패턴 중 Range Loop-읽기(Read) 에 대한 내용을 살펴본다.

요약

Range Loop는 엑셀 시트에 입력되어 있는 데이터를 VBA 코드로 읽어서 처리할 때 자주 사용하는 코딩 패턴이다. 시작 cell부터 마지막 cell까지 순차적으로 읽는 기본적인 패턴과, 순차적으로 읽지 않고 한번에 읽는 성능 개선 패턴을 아래에 소개하고 성능을 비교한다.

Test data, Test VBA Code

설명을 위해 예시 데이터를 10만개 행, 10개 열, 총 100만개 cell로 생성하고, 각 cell의 값은 (행, 열)의 좌표값으로 설정하였다.

아래 github 파일 Link에서 예시 데이터, “B2” cell을 시작점으로 10행(100 cell) 부터 10만행(100만 cell)까지 Range를 탐색하는 코딩 패턴과 실행결과를 확인할 수 있다.

파일 Link: github DAToolset/VBACode/VBA 코딩 패턴-Range Loop(Read).xlsm

Range Loop-읽기(Read) 기본 패턴-Nested Loop

기본 패턴은 시작 Cell을 기준으로 행과 열의 Offset을 증가시키면서 행에 대한 Loop(반복) 구문 내부에 열에 대한 Loop(반복) 구문을 사용한다. 상세 코드는 다음과 같다.

'기본 패턴
Public Sub NestedLoop(aTargetRowCnt As Long)
    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String
    Set oBaseRange = Range("B2")
    lRowOffset = 0
    For lRowOffset = 0 To aTargetRowCnt '읽는 행의 수(충분히 큰 수로 지정하고 Loop 내부에서 종료 조건 설정)
        If Trim(oBaseRange.Offset(lRowOffset, 0).Value) = "" Then Exit For 'Loop 종료 조건
        For lColOffset = 0 To 9 '읽는 열의 수
            'cell 단위로 읽고 처리하는 코드 예시
            'Debug.Print oBaseRange.Offset(lRowOffset, lColOffset).Value
            sVal = oBaseRange.Offset(lRowOffset, lColOffset).Text
        Next lColOffset
    Next lRowOffset
End Sub

내부 For Loop의 다음 코드는 Cell 값을 읽는 가장 간단한 코드를 예시로 작성하였다.

            sVal = oBaseRange.Offset(lRowOffset, lColOffset).Text

단일 Loop를 사용하는 패턴-Single Loop(1), Text  사용

열에 대한 내부 반복문을 열 개수만큼의 문장으로 대체하는 구문으로 상세 코드는 다음과 같다. Range의 값을 꺼낼 때 Text 를 사용하였다.

'단일 Loop를 사용하는 패턴: Single Loop(1), Text  사용
Public Sub SingleLoop1(aTargetRowCnt As Long)
    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String
    Set oBaseRange = Range("B2")
    lRowOffset = 0
    Dim lRowCount As Long, lColCount As Long, oHeaderRange As Range
    Set oHeaderRange = oBaseRange.Offset(-1, 0)
    'lRowCount = Range(oBaseRange, oBaseRange.End(xlDown)).Rows.Count
    lRowCount = aTargetRowCnt
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    For lRowOffset = 0 To lRowCount - 1 '읽는 행의 수 (Offset은 0부터 시작하므로 Count -1 까지 Loop 실행해야 함)
        sVal = oBaseRange.Offset(lRowOffset, 0).Text
        sVal = oBaseRange.Offset(lRowOffset, 1).Text
        sVal = oBaseRange.Offset(lRowOffset, 2).Text
        sVal = oBaseRange.Offset(lRowOffset, 3).Text
        sVal = oBaseRange.Offset(lRowOffset, 4).Text
        sVal = oBaseRange.Offset(lRowOffset, 5).Text
        sVal = oBaseRange.Offset(lRowOffset, 6).Text
        sVal = oBaseRange.Offset(lRowOffset, 7).Text
        sVal = oBaseRange.Offset(lRowOffset, 8).Text
        sVal = oBaseRange.Offset(lRowOffset, 9).Text
    Next lRowOffset
End Sub

단일 Loop를 사용하는 패턴-Single Loop(2), Text 대신 Value2 사용

Single Loop 구문을 유지하고 Range의 값을 꺼낼 때 Text 대신 Value2를 사용하였다. 성능 비교는 가장 아래쪽에 작성해 두었다.

' 행만 Loop, Text 대신 Value2 사용
Public Sub SingleLoop(aTargetRowCnt As Long)
    Dim lRowOffset As Long, lColOffset As Long, oBaseRange As Range, sVal As String
    Set oBaseRange = Range("B2")
    lRowOffset = 0
    Dim lRowCount As Long, lColCount As Long, oHeaderRange As Range
    Set oHeaderRange = oBaseRange.Offset(-1, 0)
    'lRowCount = Range(oBaseRange, oBaseRange.End(xlDown)).Rows.Count
    lRowCount = aTargetRowCnt
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    For lRowOffset = 0 To lRowCount - 1 '읽는 행의 수 (Offset은 0부터 시작하므로 Count -1 까지 Loop 실행해야 함)
        sVal = oBaseRange.Offset(lRowOffset, 0).Value2
        sVal = oBaseRange.Offset(lRowOffset, 1).Value2
        sVal = oBaseRange.Offset(lRowOffset, 2).Value2
        sVal = oBaseRange.Offset(lRowOffset, 3).Value2
        sVal = oBaseRange.Offset(lRowOffset, 4).Value2
        sVal = oBaseRange.Offset(lRowOffset, 5).Value2
        sVal = oBaseRange.Offset(lRowOffset, 6).Value2
        sVal = oBaseRange.Offset(lRowOffset, 7).Value2
        sVal = oBaseRange.Offset(lRowOffset, 8).Value2
        sVal = oBaseRange.Offset(lRowOffset, 9).Value2
    Next lRowOffset
End Sub

데이터를 한번에 memory로 읽는 방법-Variant Array 사용

데이터를 읽을 범위를 정하고 2차원 Variant Array로 한번에 읽는 방법으로 상세 코드는 다음과 같다.

' 한번에 읽는 패턴
Public Sub VariantArray(aTargetRowCnt As Long)
    Dim vRngArr As Variant, oBaseRange As Range, oHeaderRange As Range, lColCount As Long, sVal As String
    Set oBaseRange = Range("B2")
    Set oHeaderRange = oBaseRange.Offset(-1, 0)
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    vRngArr = oBaseRange.Resize(aTargetRowCnt, lColCount).Value2
    Dim lCol As Long, lRow As Long
    For lRow = LBound(vRngArr, 1) To UBound(vRngArr, 1)
        For lCol = LBound(vRngArr, 2) To UBound(vRngArr, 2)
            'Debug.Print vRngArr(lRow, lCol)
            sVal = vRngArr(lRow, lCol)
        Next lCol
    Next lRow
End Sub

여기에서 핵심은 다음 코드이다.

    vRngArr = oBaseRange.Resize(aTargetRowCnt, lColCount).Value2

Resize는 시작 범위(Range)로부터 지정한 행, 열의 수만큼 범위를 확장하여 값을 읽을 대상을 설정한다. Value2는 Range의 값을 형식없이 원래의 값으로 꺼내는 속성이다. 값을 꺼낼 Range에 여러 행과 열을 포함하고 있다면 그 값은 2차원 Variant Array로 꺼내진다. 그래서 위 코드는 읽을 범위 전체를 한번에 memory로 읽게 된다.

성능비교

10부터 10만까지 Row Count를 증가시키면서 각 방법의 실행시간을 정리하면 다음과 같다.
(실행환경: 2019 LG Gram, CPU i5, Ram 16GB, 256GB SSD)

실행 성능은 다음과 같이 요약할 수 있다.

• Nested Loop, Single Loop 반복 구문 자체의 실행 성능은 거의 차이가 없다.
• Range에서 값을 꺼낼 때 Text를 사용하면 느리고, Value2를 사용하면 빠르다.
• Variant Array를 이용하면 데이터가 많아도 매우 빠르게 읽을수 있다.

참고로 Range에서 값을 꺼낼때 사용하는 Text, Value, Value2 속성에 대한 비교는 나중에 시간이 되면 정리하겠다. 일단은 google에서 검색하여 찾은 stackoverflow.com의 글을 링크해 둔다.
https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2

관련 글:

데이터 표준점검 도구_1.개요 DA# Macro 기능 시연 영상 (YouTube) DA# Macro(1): DA#, DA# API, DA# Macro (매크로) 개요 Windows API Import시 32bit, 64bit Excel 모두 사용하도록 PtrSafe 지정 VBA 코딩 패턴: DB Query 결과 엑셀로 가져오기 VBA 코딩 패턴: VBA 성능 향상 VBA 코딩 패턴: Named Range (명명된 범위) VBA 코딩 패턴: Range Loop-쓰기(Write)