매크로 - 범위로 처리하기

그동안은 매크로 작성 시 범위로 처리하는 것만 알고 있었는데,

최근에 유튜브를 보니 셀마다 처리하는 것이 아니라

배열에서 처리한 후 한꺼번에 범위에 일괄로 데이터를 입력하는 것이

훨씬 빠르다는 내용을 보고 충격을 받아 정리하고자 합니다.

 

먼저 범위로 처리하는 방법을 알아보겠습니다.

범위와 배열간 변환.xlsx

0.01MB

 

 

 

A열에 2를 곱한 값을 B열에 넣는다고 할 때

범위로 처리하는 것은 아래와 같습니다.

 

1. 코드 작성 및 실행

 

먼저 개발 도구 - Visual Basic을 누르고,

삽입 - 모듈을 클릭해서 모듈을 추가합니다.

 

그리고, 코드 창에 아래 내용을 복사해서 붙여 넣습니다.

Option Explicit

Sub 곱하기()
    Dim rng As Range, c As Range
    
    Set rng = Range(Range("a1"), Range("a1").End(xlDown))
    
    For Each c In rng
        c.Offset(0, 1) = c * 2
    Next
End Sub

 

그리고, 표준 도구 모음에서 실행 아이콘을 클릭하면

 

아래와 같이 A열 값에 2를 곱한 값이 B열에 입력됩니다.

 

10개이니까 빨리 되는 것 같지 5만 개이면 몇 초 걸리는지 보기 위해

D1셀에 시작 시간이라고 입력하고, E1셀에 프로그램 실행 시 현재 시간을 기록하도록 하고,

D2셀에는 종료 시간, E2셀에도 현재 시간을 기록하도록 한 다음

D3셀에 소요 시간, 그리고, E3셀에는 =E2-E1이라고 입력하면 소요시간을 구할 수 있습니다.

 

표시 형식은 E열을 클릭한 다음 시간으로 변경합니다.

 

코드를 아래와 같이 시작 시간과 종료 시간을 기록하도록 수정하고 실행하면

Option Explicit

Sub 곱하기()
    Dim rng As Range, c As Range
    
    Range("e1") = Now()
    Set rng = Range(Range("a1"), Range("a1").End(xlDown))
    
    For Each c In rng
        c.Offset(0, 1) = c * 2
    Next
    Range("e2") = Now()
End Sub

 

3초가 걸립니다.

 

2. 코드 설명

 

(1) Sub 프로시저

Sub 곱하기() End Sub

일련의 동작을 한꺼번에 실행하는 것이므로 Sub 프로시저로 작성하는데,

Sub 다음에 프로시저명과 인수를 입력할 수 있는 괄호를 넣는데 인수가 없으므로 비어 있습니다.

End Sub로 종료합니다.

 

 

(2) 변수 선언

Dim rng As Range, c As Range

Dim 다음에 변수명을 입력하고 형식을 지정합니다. 형식을 지정하지 않으면 Variant 형식이 됩니다.

 

위 구문은 rng와 c를 모두 Range(범위) 형식으로 선언한 것입니다.

 

 

(3) 시작 시간 입력

Range("e1") = Now()

Now는 현재 시점의 날짜와 시간을 반환하는 함수로

보이는 형식은 아래와 같습니다.

 

 

(4) rng 범위 변수 저장

Set rng = Range(Range("a1"), Range("a1").End(xlDown))

일반 변수의 경우와 달리 범위 변수는 Set 다음에 변수명을 입력하는 것이 다릅니다.

그리고 값으로 범위를 지정하는데,

시작 셀은 Range("a1")이라고 A1셀을 지정하고,

종료 셀은 Range("a1").End(xlDown)이라고, A1셀과 연속된 맨 아래 셀을 지정합니다.

그리고, 시작 셀과 종료 셀을 묶기 위해 다시 Range를 앞에 추가합니다.

 

위 구문의 결과는 A1:A10 또는 A1:A50000 식이 됩니다.

 

 

(5) 범위 내에서 처리 반복

For Each c In rng         c.Offset(0, 1) = c * 2     Next

For Each ~ Next 반복문을 사용해서 rng 범위내에서 셀 하나씩을 옮겨가면서 반복합니다.

 

c.Offset(0,1) = c * 2 : c는 rng 범위내의 셀 하나를 가리키며,

 

offset함수는 특정 셀을 기준으로 위아래, 왼쪽오른쪽 셀을 지정하는데 0,1이므로 행은 변화가 없고, 열만 오른쪽으로 한 칸 이동한 셀을 가리킵니다.

 

c * 2는 처리 셀 값에 2를 곱하라는 것이므로 오른쪽 셀에 왼쪽 셀에 2를 곱한 값이 입력되는 것입니다.

 

 

(6) 종료 시간 입력

Range("e2") = Now()

Now()로 현재 날짜와 시간을 구하는 것은 동일한데 반복문이 종료된 다음 실행하므로 종료 시간이 E2셀에 기록됩니다.

3. 파일 저장

매크로가 있으므로 저장시 매크로가 포함된 문서는 저장할 수 없다고 하므로 '아니요'를 누른 다음

 

파일 형식을 'Excel 매크로 사용 통합문서(*.xlsm)"으로 변경한 다음 저장합니다.

 

범위와 배열간 변환1.xlsm

0.72MB