VBA 코딩 패턴: Range Loop-쓰기(Write)

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

요약

Variant Array를 이용하여 엑셀 시트에서 많은 데이터를 쓸때 성능을 개선하는 코딩 패턴에 대해 소개한다.

엑셀 시트에서 많은 데이터를 읽을 때 성능을 개선하는 방법은 아래 글에서 이미 다루었다.

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

한줄 요약: Loop 보다 Variant Array로 읽는 방법이 비교할 수 없을 정도로 빠름.

이번에는 반대로 VBA 코드내에서 데이터를 엑셀 시트로 쓰는 방법에 대해 살펴본다. 각 방법에 대한 성능은 가장 아래쪽에 비교해 놓았다.

테스트 방법

다음과 같은 방법으로 테스트를 실행한다.

• Range(“B2”)를 기준으로 행과 열을 증가시키면서 각 cell에 값을 쓴다.
• cell에 기록되는 값은 “행번호, 열번호” 형식으로 한다.
• 열의 수는 고정(10열)으로 둔다.
• 행의 수는 10, 100, 500, 1000 ~ 100,000 까지 증가시키면서 반복한다.
• cell의 수는 행의 수 x 10 이므로, 최대 1백만 cell까지 테스트한다.

테스트 코드는 github에서 다운로드받을 수 있다.

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

기본 패턴-Nested Loop

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

상세 코드는 다음과 같다. 이전 글 VBA 코딩 패턴: Range Loop-읽기(Read) 와 코드가 거의 동일하고, 읽기를 쓰기로 변경하였다. (10 행)

Range개체의 Text는 Read-only 속성이라서 값을 입력할 수 없어서 Range 개체에 값을 바로 입력했다.

'* 기본 패턴(Nested 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 단위로 쓰는 코드 예시
            oBaseRange.Offset(lRowOffset, lColOffset) = CStr(lRowOffset + 1) + "," + CStr(lColOffset + 1)
        Next lColOffset
    Next lRowOffset
End Sub

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

열에 대한 내부 반복문을 열 개수만큼의 문장(11행 ~ 20행)으로 대체하였다. 마찬가지로 Range 개체에 값을 바로 입력했다.

'단일 Loop를 사용하는 패턴: Single Loop(1)
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 = aTargetRowCnt
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    For lRowOffset = 0 To lRowCount - 1 '읽는 행의 수 (Offset은 0부터 시작하므로 Count -1 까지 Loop 실행해야 함)
        oBaseRange.Offset(lRowOffset, 0) = CStr(lRowOffset + 1) + ",1"
        oBaseRange.Offset(lRowOffset, 1) = CStr(lRowOffset + 1) + ",2"
        oBaseRange.Offset(lRowOffset, 2) = CStr(lRowOffset + 1) + ",3"
        oBaseRange.Offset(lRowOffset, 3) = CStr(lRowOffset + 1) + ",4"
        oBaseRange.Offset(lRowOffset, 4) = CStr(lRowOffset + 1) + ",5"
        oBaseRange.Offset(lRowOffset, 5) = CStr(lRowOffset + 1) + ",6"
        oBaseRange.Offset(lRowOffset, 6) = CStr(lRowOffset + 1) + ",7"
        oBaseRange.Offset(lRowOffset, 7) = CStr(lRowOffset + 1) + ",8"
        oBaseRange.Offset(lRowOffset, 8) = CStr(lRowOffset + 1) + ",9"
        oBaseRange.Offset(lRowOffset, 9) = CStr(lRowOffset + 1) + ",10"
    Next lRowOffset
End Sub

단일 Loop를 사용하는 패턴-Single Loop(2)

Single Loop 구문을 유지하고 Range에 값을 입력할 때 Value2를 사용하였다.

'단일 Loop를 사용하는 패턴: Single Loop(2), Value2 사용
Public Sub SingleLoop2(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 = aTargetRowCnt
    lColCount = Range(oHeaderRange, oHeaderRange.End(xlToRight)).Columns.Count
    For lRowOffset = 0 To lRowCount - 1 '읽는 행의 수 (Offset은 0부터 시작하므로 Count -1 까지 Loop 실행해야 함)
        oBaseRange.Offset(lRowOffset, 0).Value2 = CStr(lRowOffset + 1) + ",1"
        oBaseRange.Offset(lRowOffset, 1).Value2 = CStr(lRowOffset + 1) + ",2"
        oBaseRange.Offset(lRowOffset, 2).Value2 = CStr(lRowOffset + 1) + ",3"
        oBaseRange.Offset(lRowOffset, 3).Value2 = CStr(lRowOffset + 1) + ",4"
        oBaseRange.Offset(lRowOffset, 4).Value2 = CStr(lRowOffset + 1) + ",5"
        oBaseRange.Offset(lRowOffset, 5).Value2 = CStr(lRowOffset + 1) + ",6"
        oBaseRange.Offset(lRowOffset, 6).Value2 = CStr(lRowOffset + 1) + ",7"
        oBaseRange.Offset(lRowOffset, 7).Value2 = CStr(lRowOffset + 1) + ",8"
        oBaseRange.Offset(lRowOffset, 8).Value2 = CStr(lRowOffset + 1) + ",9"
        oBaseRange.Offset(lRowOffset, 9).Value2 = CStr(lRowOffset + 1) + ",10"
    Next lRowOffset
End Sub

memory에 있는 데이터를 한번에 Range에 쓰는 방법-Variant Array 사용

2차원 Variant Array에 저장된 데이터를 한번에 Range에 쓰는 방법으로 상세 코드는 다음과 같다.

' 한번에 쓰는 패턴
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
    ReDim vRngArr(1 To aTargetRowCnt, 1 To lColCount)
    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)
            vRngArr(lRow, lCol) = CStr(lRow) + "," + CStr(lCol)
        Next lCol
    Next lRow
    oBaseRange.Resize(aTargetRowCnt, lColCount).Value2 = vRngArr
End Sub

핵심 코드는 14행이다. Range에 Variant array를 바로 대입하여 데이터를 write한다.

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

VBA 코딩 패턴: Range Loop-읽기(Read) 에서 read하는 다음 코드와 비교해 보면 좌항과 우항만 바뀌고 동일한 것을 알 수 있다.

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

성능비교

Range 쓰기 성능 비교

VBA 코딩 패턴: Range Loop-읽기(Read) 와 동일하게 10부터 10만까지 Row Count를 증가시키면서 각 방법의 실행시간을 정리하였다.
(실행환경: 2019 LG Gram, CPU i5, Ram 16GB, 256GB SSD)

Range 쓰기 성능은 다음과 같이 요약할 수 있다.

• Value2를 이용하여 값을 입력하는 방법과 Range 개체에 값을 바로 입력하는 방법은 거의 차이가 없다.
• Single Loop보다 Nested Loop가 약간 더 빠른 결과가 나왔다. 이건 읽기(Read)와 반대라서 항상 더 빠르다고 하기 어려워 보인다.
• Variant Array를 이용하여 데이터를 쓰면 비교할 수 없을 정도(최소 10배 이상)로 빠르다.

Range 읽기 성능 비교

읽기와 쓰기 성능을 바로 비교해 볼 수 있도록 읽기 성능 비교 내용을 아래에 복사해 둔다.

---

엑셀 시트(Sheet)에서 많은 양의 데이터를 VBA 코드로 읽고 쓸 때 Variant array를 활용하면 성능을 크게 향상시킬 수 있으니, 필요한 경우에 적절히 잘 활용하기 바란다.