매크로 - 범위가 아닌 배열로 처리하기

범위와 배열간 변환1.xlsm

0.72MB

 
 
 
이 글에서는 범위로 처리했는데, 배열로 처리하는 것을 다뤄보겠습니다.
 
 

1. 코드 작성 및 실행

먼저 개발 도구 - Visual Basic을 누르고,
삽입 - 모듈을 클릭해서 모듈을 추가합니다.
 
그리고, 코드 창에 아래 내용을 복사해서 붙여 넣습니다.

Sub 배열로곱하기()
    Dim rng As Range, c As Range
    Dim val
    Dim i As Long
    
    Range("e1") = Now()
    Set rng = Range(Range("a1"), Range("a1").End(xlDown))
    
    val = rng
    
    For i = 1 To UBound(val)
        val(i, 1) = val(i, 1) * 2
    Next
    
    Range("b1:b" & UBound(val)) = val
    Range("e2") = Now()
End Sub

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

 
아래와 같이 A열 값에 2를 곱한 값이 B열에 입력되고, 범위로 처리한 경우에는 3초가 걸렸는데, 0초로 순식간에 처리가 완료됩니다.

 
맨 아래 줄인 1,048,576까지 처리하는데도 1초 정도뿐이 안 걸립니다.

범위로 처리하는 경우 5만 건을 처리하는데 3초 정도 걸린 것과 비교할 때 엄청 빠른 속도입니다.

2. 코드 설명

 
(1) Sub 프로시저

Sub 배열곱하기() End Sub

일련의 동작을 한꺼번에 실행하는 것이므로 Sub 프로시저로 작성하는데,
Sub 다음에 프로시저명과 인수를 입력할 수 있는 괄호를 넣는데 인수가 없으므로 비어 있습니다.
End Sub로 종료합니다.
 
 
(2) 변수 선언
 

Dim rng As Range     Dim val     Dim i As Long

범위로 처리할 것이 아니므로 범위로 처리 시 사용했던 범위 형식 변수 c는 필요 없으며, 그 대신 배열 인덱스로 사용하기 위해 i 변수를 Integer 형식(-32,768 ~ 32,767)이 아니라 같은 정수이지만 크기가 큰 Long 형식(-2147483648 ~ 2147483647)으로 선언합니다.
 
그 대신 배열을 처리하기 위해 배열 변수 val를 선언하는데
일반적인 배열 변수 선언 시와는 달리 괄호도 없고, 크기도 없습니다.
 
만약 일반적인 변수 형식으로 괄호를 추가해서 배열을 선언하면 Redim 문으로 크기를 지정해야 합니다.

Dim val()     Dim i As Long          Range("e1") = Now()     Set rng = Range(Range("a1"), Range("a1").End(xlDown))     ReDim val(rng.Rows.Count, 1)          val = rng

 
그리고 Dim val() as Long이라고 정수 형식으로 선언하면
rng는 범위 형식이기 때문에 '형식이 일치하지 않는다'는 에러가 발생합니다.

 
위 화면에서 디버그 버튼을 누르면 val = rng에서 실행을 멈춥니다.

 
표준도구 모음에서 재설정 아이콘을 눌러 매크로 실행을 종료합니다.

 
그리고, 표준 도구 모음에서 실행 취소 아이콘을 여러 번 클릭해서 종전 코드로 되돌립니다.

 
 
(3) 시작 시간 입력과 rng 변수 저장은 범위로 처리할 때와 동일합니다.

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

 
 
(4) 범위 변수를 배열에 저장

val = rng

위와 같이 val = rng라고 하면
rng 변수의 값이 val 배열에 저장됩니다.
정말 편합니다.
 
 
(5) 배열로 처리
For ~ Next 반복문 왼쪽 기둥을 마우스로 클릭하거나 F9를 눌러 중단점을 설정한 후 
보기 - 지역 창을 클릭해서 val의 값과 형식을 살펴보면 
Variant/Variant(1 to 50000, 1 to 1)이라고 Variant 형식으로 1차원은 1부터 5만까지이고, 2차원은 1로 1개이며(2차원 배열),
val(1,1)의 값은 1입니다.

rng 범위도 2차원 구조입니다.

 
따라서 1부터 50000까지 반복하는데, 5만이 변할 수 있으므로 Ubound함수를 이용해 인덱스 상한 값을 구합니다.
Ubound(val)은 50000입니다.
 
직접 실행창에서 ?val라고 입력한 후 엔터키를 누르면
50000 임을 확인할 수 있습니다.

 
val(i, 1) = val(i, 1) * 2 : val 배열의 값을 2를 곱한 값으로 대체합니다.
 
이렇게 val 배열의 값을 모두 2배로 만듭니다.
 
 
(6) 배열을 범위에 일괄 입력

Range("b1:b" & UBound(val)) = val

 
범위를 배열에 저장한 것처럼 한꺼번에 배열 변수를 범위에 입력할 수 있습니다.
 
Range("b1:b" & UBound(val))는 b1부터 b에 Ubound(val)의 값을 합한 값의 범위,
위의 경우는 B1:B50000이 됩니다.
 
그리고 = val이라고 입력하면
B1:B50000에 val 배열의 값이 일괄해서 들어갑니다.
 
 
(7) 종료 시간 입력

Range("e2") = Now()

Now()로 현재 날짜와 시간을 구하는 것은 동일한데 반복문이 종료된 다음 실행하므로 종료 시간이 E2셀에 기록됩니다.
 
 
(8) 다른 이름으로 저장
파일 탭을 누른 후 '다른 이름으로 저장'을 누른 후 
'범위와 배열간 변환2'로 저장합니다.
 
이미 파일 형식은 *.xlsm으로 되어 있으므로 변경할 필요가 없습니다.
 
 
(9) 매크로 실행
워크시트로 돌아온 후
개발 도구 - 매크로를 누르고, 매크로 이름 목록에서 '배열로곱하기'를 클릭 후 실행 버튼을 눌러 실행할 수도 있습니다.

PERSONAL.XLSB!가 많은 것은 '개인용 매크로 통합 문서'에 매크로로 많이 만들어 놔서 그렇습니다.

 

범위와 배열간 변환2.xlsm

0.72MB