VBA 배열(array)은 변수 타입이며 동일한 타입의 변수 여러개를 하나의 변수에 저장하는데 사용 됩니다. 이해를 쉽게 하기 위해 아래 예제를 살펴 보도록 하겠습니다. VBA에서 일반적인 변수는 아래와 같이 한번에 하나의 값만 저장할 수 있습니다.
' 한 번에 하나의 값만 저장할 수 있음
Dim Student1 As Long
Student1 = 55
만일 다른학생의 점수를 저장하기 위해서는 두번째 변수를 만들어야 합니다. 하지만 다음 처럼 학생 5명의 점수가 있다고 가정해 봅시다.
이제 부터 우리는 위의 학생들의 점수를 읽어 Debug.Print를 이용해 '직접 실행 창'에 써보도록 하겠습니다.
NOTE : Debug.Print함수는 '직접 실행 창'에 결과를 출력합니다. '직접 실행 창'은 메뉴에서 '보기 -> 직접 실행 창'을 통해 찾을 수 있습니다. 단축키는 Ctrl + G 입니다.
먼저 아래 예제 처럼 동일한 코드를 다섯번 반복해보도록 하겠습니다.
Public Sub StudentScore()
Dim ws As Worksheet
'워크시트 읽기. https://kukuta.tistory.com/278 참고
Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim Student1 As Long
Dim Student2 As Long
Dim Student3 As Long
Dim Student4 As Long
Dim Student5 As Long
' 학생들 점수 읽기
Student1 = ws.Range("B" & 2).Value
Student2 = ws.Range("B" & 3).Value
Student3 = ws.Range("B" & 4).Value
Student4 = ws.Range("B" & 5).Value
Student5 = ws.Range("B" & 6).Value
' 학생들 점수 출력
Debug.Print "학생 점수"
Debug.Print Student1
Debug.Print Student2
Debug.Print Student3
Debug.Print Student4
Debug.Print Student5
End Sub
' OUTPUT
' 학생 점수
' 89
' 67
' 77
' 42
' 70
학생당 하나의 변수를 사용할 때의 문제는 각 학생당 하나의 변수가 필요하고 변수가 추가 될때 마다 추가 코드가 필요하다는 것입니다. 위의 예에서 만일 1000명의 학생이 있다면 최소 3000줄의 코드가 필요합니다. 하지만 다행히도 우리에게는 우리의 삶을 더 윤택하게 해주는 배열이 있습니다. 배열을 사용하면 동일한 데이터 항목들을 하나의 변수에 저장할 수 있습니다.
다음 코드는 앞의 예제와 동일한 기능을하는 배열을 사용한 예제입니다.
Public Sub StudentScoreArr()
Dim ws As Worksheet
'워크시트 읽기. https://kukuta.tistory.com/278 참고
Set ws = ThisWorkbook.Worksheets("Sheet1")
' 5개의 학생 정보를 저장하기 위한 배열 선언
Dim Students(1 To 5) As Long
' B2 부터 B6까지 셀들을 읽어 배열에 저장
' Offset이 1부터 시작하기 때문에 B1을 지정
Dim i As Long
For i = 1 To 5
Students(i) = ws.Range("B1").Offset(i).Value
Next i
' 학생들 점수 출력
Debug.Print "학생 점수"
For i = LBound(Students) To UBound(Students)
Debug.Print Students(i)
Next i
End Sub
' OUTPUT
' 학생 점수
' 89
' 67
' 77
' 42
' 70
이 코드의 장점, 즉 배열 사용의 장점은 학생수 따라 배열의 크기만 수정해주면 더 이상의 추가 코드 없이 작동한다는 것입니다. 앞의 예에서는 학생수에 따라 추가해주어야 하는 코드의 수가 계속 증가했습니다.
변수와 배열을 간단히 비교해 보겠습니다. 먼저 변수와 배열의 선언(declare)의 차이를 살펴 보죠.
' 변수
Dim Student1 As Long
Dim Student2 As Long
Dim Student3 As Long
Dim Country1 As String
Dim Country2 As String
Dim Country3 As String
' 배열
Dim Students(1 To 3) As Long
Dim Countries(1 To 3) As String
이제 값 할당을 비교해 보겠습니다.
' 변수에 값 할당
Student1 = .Cells(1, 1)
' 배열의 첫 번째 항목에 값 할당
Students(1) = .Cells(1, 1)
마지막으로 변수 값을 출력해 보도록하죠.
' Print variable value
Debug.Print Student1
' Print value of first student in array
Debug.Print Students(1)
보시다시피 변수와 배열을 사용하는 것은 매우 유사합니다.
배열이 인덱스를 사용하여 각 항목에 액세스한다는 사실이 중요합니다.이는 For 루프를 사용하여 배열의 모든 항목에 쉽게 액세스할 수 있음을 의미합니다.
배열이 유용한 이유에 대한 배경 지식을 얻었으므로 이제 배열을 단계별로 살펴보겠습니다.
두 종류의 배열 타입
VBA 배열은는 두 가지 유형이 있습니다.
정적 배열(Static Array) – 고정 길이의 배열입니다.
동적 배열(Dynamic Array) – 런타임에 길이가 설정되는 배열입니다.
두 배열의 주요한 차이점은 배열의 생성 방법입니다. 두 배열 타입의 값에 접근하는 것은 정확히 동일한 방법입니다.다음 섹션에서는 이 두 가지 유형을 모두 살펴 볼 것입니다.
VBA 배열 초기화
정적 배열(static array)를 초기화하는 방법은 아래와 같습니다.
Public Sub DeclareArrayStatic()
' 위치가 0,1,2,3인 배열 생성
Dim arrMarks1(0 To 3) As Long
' 배열 시작의 기본값은 0에서 시작. 3이라고만 쓰면 0,1,2,3을 의미
Dim arrMarks2(3) As Long
' 위치가 1,2,3,4,5인 배열 생성
Dim arrMarks3(1 To 5) As Long
' 위치가 2,3,4인 배열 생성(이것은 거의 사용되지 않습니다)
Dim arrMarks4(2 To 4) As Long
End Sub
보시다시피 길이는 정적 배열을 선언할 때 지정됩니다.이 방식의 문제는 필요한 길이를 미리 알수 없는 경우가 있다는 것입니다.매크로를 실행할 때마다 필요한 길이가 다를 수 있습니다.
만일 여러분이 모든 배열 요소들을 사용하지 않는다면 불필요한 리소스가 낭비되고 있는 것입니다. 여러분이 더 많은 배열 요소가 필요한 경우 ReDim을 이용할 수 있지만 이는 이는 본질적으로 새로운 정적 배열을 생성하는 것입니다.
동적 배열에는 이러한 문제가 없습니다.선언할 때 길이를 지정하지 않습니다.따라서 필요에 따라 확장 및 축소할 수 있습니다.
Public Sub DecArrayDynamic()
' 동적 배열 선언
Dim arrMarks() As Long
' 동적으로 배열의 길이를 지정
ReDim arrMarks(0 To 5)
End Sub
동적 배열은 ReDim 문을 사용할 때까지 실제 할당되지 않습니다. 이를 통해 필요한 배열의 길이를 확실하게 알 때까지 리소스 할당을 지연할 수 있다는 장점이 있습니다. 여러분이 정적 배열을 사용했다면 길이를 미리 명시해야 합니다.
예를 들어.당신이 학생 점수의 워크시트를 읽고 있다고 상상해보십시오.동적 배열을 사용하면 워크시트의 학생 수를 세고 배열을 해당 길이로 설정할 수 있습니다. 하지만 정적 배열을 사용하면 배열의 길이를 예상되는 최대 학생 수로 설정해야 합니다.
배열에 값 할당하기
배열의 요소에 값을 할당하기 위해 정수 타입의 인덱스를 사용합니다. 값을 할당하는 방법은 정적 배열과 동적 배열 모두 동일합니다.
Public Sub AssignValue()
' 인덱스가 0,1,2,3인 배열 선언
Dim arrMarks(0 To 3) As Long
' 인덱스 0의 값을 설정
arrMarks(0) = 5
' 인덱스 3의 값을 설정
arrMarks(3) = 46
' 이 배열에는 4번 요소가 없으므로 에러 발생
arrMarks(4) = 99
End Sub
2차원 배열 사용하기
지금까지 살펴본 배열은 1차원 배열이었습니다.이것은 배열이 하나의 항목 목록임을 의미합니다.
2차원 배열은 배열을 요소로 가지고 있는 배열입니다. 설명을 쉽게하기 위해 스프레드 시트를 예로들어 보도록 하겠습니다. 단일 스프레드시트 행을 1차원으로 생각하면 둘 이상의 열이 있다면 2차원 입니다.실제로 스프레드시트는 2차원 배열과 같습니다.행과 열을 2차원으로 보면 됩니다.
한 가지 주목해야 할 부분은 Excel에서 1차원 배열을 스프레드시트에 쓰는 경우 행으로 처리한다는 것입니다.즉, 배열arr(1 to 5)는스프레드시트에 값을 쓸 때arr(1 to 1, 1 to 5) 와 같습니다.
다음 이미지는 두 개의 데이터 그룹을 보여줍니다.첫 번째는 1차원 레이아웃이고 두 번째는 2차원 레이아웃입니다.
첫 번째 데이터 세트(1차원)의 항목에 액세스하려면 행(예: 1,2, 3 또는 4)을 지정하기만 하면 됩니다.
두 번째 데이터 세트(2차원)의 경우 행과 열을 지정해야 합니다.따라서 1차원은 여러 열, 한 행 및 2차원은 여러 행과 여러 열로 생각할 수 있습니다.
참고:배열에 2개 이상의 차원이 있을 수 있습니다만 거의 필요하지 않습니다.3+ 차원 배열을 사용하여 문제를 해결하는 경우 더 나은 방법이 있을 수 있습니다.
2차원 배열을 선언하는 방법은 다음과 같습니다.
Dim ArrayMarks(0 To 2,0 To 3) As Long
다음 예제에서는 배열의 각 항목에 대해 임의의 값을 만들고 해당 값을 직접 실행 창에 인쇄합니다.
Public Sub TwoDimArray()
' Declare a two dimensional array
Dim arrMarks(0 To 3, 0 To 2) As String
' Fill the array with text made up of i and j values
Dim i As Long, j As Long
For i = LBound(arrMarks) To UBound(arrMarks)
For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
arrMarks(i, j) = CStr(i) & ":" & CStr(j)
Next j
Next i
' Print the values in the array to the Immediate Window
Debug.Print "i", "j", "Value"
For i = LBound(arrMarks) To UBound(arrMarks)
For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
Debug.Print i, j, arrMarks(i, j)
Next j
Next i
End Sub
9라인과 17라인의LBound및UBound의 두번째 인자에 2가 지정된것에 주목해주세요. 이것은 두 번째 배열을 가리킨4다는 의미입니다. 위 코드는 arrMarks의 2차원 배열의 가장 작은 인덱스와 가장 큰 인덱스를 리턴합니다.그것이j의 시작과 끝 위치입니다.
LBound와 UBound의 두번째 인자의기본값은 1이므로 8라인과 16라인의i루프에 대한 LBound와 UBound에는 명시적으로 지정할 필요는 없습니다(하지만 명확성을 위해 지정해도 상관 없습니다).
첫 번째 루프 내에서 두 번째 For 루프를 사용하여 모든 항목에 액세스하는 것을 볼 수 있습니다. 예제의 출력은 다음과 같습니다.
배열의 길이
VBA에는 다른 프로그래밍 언어 처럼 배열의 길이를 직접적으로 가져오는 함수는 없습니다. 대신 가장 하위 인덱스를 리턴하는 LBound와 최상위 인덱스를 리턴하는 UBound함수를 이용해 배열을 길이를 알아 올 수 있습니다.
Function ArrayLength(arr As Variant) As Long
On Error Goto eh '에러 발생시 eh 태그로 점프
' 루프는 다차원 배열에 사용됩니다.
' 루프는 "아래 첨자가 범위를 벗어남" 오류가 발생하면 종료 됩니다.
Dim i As Long, length As Long
length = 1
' 더 이상 배열의 차원이 없을 때까지 순회
Do While True
i = i + 1
' 배열에 항목이 없으면 이 줄에서 오류가 발생 합니다.
Length = Length * (UBound(arr, i) - LBound(arr, i) + 1)
ArrayLength = Length
Loop
Done:
Exit Function
eh:
If Err.Number = 13 Then ' Type Mismatch Error
Err.Raise vbObjectError, "ArrayLength" _
, "ArrayLength 함수에 전달된 인수가 배열이 아닙니다."
End If
End Function
' ArrayLength 사용
Sub TEST_ArrayLength()
' 아이템이 하나도 없는 다이나믹 배열
Dim arr1() As Long
Debug.Print ArrayLength(arr1)
' 10개 아이템
Dim arr2(0 To 9) As Long
Debug.Print ArrayLength(arr2)
' 0 ~ 5 부터 6개 아이템을 가진 배열이 1 ~ 3부터 3개
' 6 x 3 = 18개 아이템
Dim arr3(0 To 5, 1 To 3) As Long
Debug.Print ArrayLength(arr3)
' Option Base : 0 으로 설정 시
' 0 ~ 1(2) x 0 ~ 5(6) x 0 ~ 5(6) x 0 ~ 1(2)
' 2 x 6 x 6 x 2 = 144개 아이템
' Option Base : 1 으로 설정 시
' 1 ~ 1(1) x 1 ~ 5(5) x 1 ~ 5(5) x 0 ~ 1(2)
. 1 x 5 x 5 x 2 = 50개 아이템
Dim arr4(1, 5, 5, 0 To 1) As Long
Debug.Print ArrayLength(arr4)
End Sub
' OUTPUT
' 0
' 10
' 18
' 144
NOTE VBA 프로그래밍을 하다 보면 '아래 첨자'가 잘 못되었다는 오류를 종종 볼 수 있다. 여기서 아래 첨자란 영어로 'subscript'인데 배열의 요소를 가리키는 인덱스(index)의 또 다른 이름이기도 하다. 즉, 배열이나 셀에 접근 할 때 올바르지 않은 인덱스 또는 요소를 나타내는 지시자를 사용했다는 뜻이다.
위 예제는 배열의 차원이 얼마든 끝날때 까지 순회하면서 배열의 길이를 알아내는 함수 입니다. 위에서 주의 깊게 보아야 할 부분은 UBound와 LBound를 사용하는 부분 입니다.
' 0, 1, 2, 3, 4. 5개 아이템
Dim arr(0 To 4) As Long
Debug.Print "UBound:" & UBound(arr)
Debug.Print "LBound:" & LBound(arr)
Debug.Print UBound(arr) - LBound(arr) + 1
'OUTPUT
'UBound:4
'LBound:0
' 5
UBound의 결과로 arr에서 가장 큰 요소의 인덱스인 4가 리턴 되고 LBound의 결과로는 0이 리턴 됩니다. 4에서 0을 빼면 4지만 배열의 길이에는 0번 요소도 포함이므로 더하기 1을 해서 배열의 길이가 5임을 구할수 있습니다.
이전 예제와 다른 부분은 UBound와 LBound에 두번째 인자를 사용하지 않고 있다는 것입니다. 두번째 인자는 차원을 나타내는 것으로써 이 예제에서는 1차원 배열을 사용하고 있기 때문에 굳이 지정을 하지 않았습니만 아래 처럼 작성하는 것도 동일한 의미를 가집니다.
Dim arr(0 To 4) As Long
Debug.Print "UBound:" & UBound(arr, 1) ' 배열의 1차원 값에 접근
Debug.Print "LBound:" & LBound(arr, 1)
Debug.Print UBound(arr, 1) - LBound(arr, 1) + 1
'OUTPUT
'UBound:4
'LBound:0
' 5
Array 함수와 Split 함수
Array 함수를 사용하여 배열을 채울 수 있습니다. 이때는 반드시 배열을 Variant 형으로 선언해야 합니다.
Dim arr1 As Variant
arr1 = Array("Orange", "Peach","Pear")
Dim arr2 As Variant
arr2 = Array(5, 6, 7, 8, 12)
모듈 상단에서 Option Base 1을 사용하지 않는한 Array함수에 의해 생성되는 배열은 인덱스 0에서 부터 시작 합니다.
Split 함수는 구분 기호를 기반으로 문자열을 배열로 분할하는데 사용합니다. 구분 기호는 항목을 구분하는 쉼표 또는 공백과 같은 문자입니다.
아래의 코드는 하나의 문자열을 네개의 요소를 가진 배열로 분할 합니다.
Dim s As String
s = "Red,Yellow,Green,Blue"
Dim arr() As String
arr = Split(s, ",")
배열에 For 루프 사용하기
For 루프를 사용하면 배열의 모든 요소에 빠르게 접근할 수 있습니다. 배열에 루프를 이용하면 10개의 데이터에 접근하든 10000개의 데이터에 접근하든 동일한 코드를 사용할 수 있습니다. 앞의 '배열의 길이'섹션에서 살펴본 LBound와 UBound라는 두 함수가 있습니다. 이 함수는 배열에서 가장 작은 인덱스와 가장 큰 인덱스를 반환 합니다.
다음 예제는 LBound와 UBound를 이용하여 배열의 처음 부터 끝까지 순회하며 난수를 할당하고 두번째 루프에서 할당 된 난수들을 출력합니다.
Public Sub ArrayLoops()
' 배열 선언
Dim arrMarks(0 To 5) As Long
' 배열에 난수 할당
Dim i As Long
For i = LBound(arrMarks) To UBound(arrMarks)
arrMarks(i) = 5 * Rnd
Next i
' 할당된 난수 출력
Debug.Print "Location", "Value"
For i = LBound(arrMarks) To UBound(arrMarks)
Debug.Print i, arrMarks(i)
Next i
End Sub
배열에 For Each 루프 사용하기
For Each 루프는 인덱스를 사용하지 않고 배열의 처음 부터 끝까지 순회할 수 있습니다. 인덱스를 사용하지 않기 때문에 LBound, UBound와 같은 함수를 사용할 필요가 없습니다.
For Each 루프의 가장 큰 특징은 읽기 전용이라는 것입니다. For Each 루프 안에서 반복자를 이용해 배열의 값을 변경할 수는 없습니다. 아래 예제에서 mark의 값은 변경 될 수 있지만 원본 배열에는 아무런 영향을 미치지 않습니다.
For Each mark In arrMarks
' 배열의 값을 변경하지는 않습니다
mark = 5 * Rnd
Next mark
For Each 루프의 주된 목적은 배열을 순회하며 읽는 것입니다.
Dim mark As Variant
For Each mark In arrMarks
Debug.Print mark
Next mark
2차원 배열을 읽는 코드를 비교하여 살펴 보도록 하겠습니다.
' For 루프를 이용하면 2차원 배열을 읽기 위해 2중 For문을 써야 합니다.
Debug.Print "i", "j", "Value"
For i = LBound(arrMarks) To UBound(arrMarks)
For j = LBound(arrMarks, 2) To UBound(arrMarks, 2)
Debug.Print i, j, arrMarks(i, j)
Next j
Next i
이제 For each 루프를 사용하여 다시 작성해 보겠습니다.루프가 하나만 필요하므로 다음과 같이 작성하는 것이 훨씬 쉽습니다.
' For Each 루프는 하나면 충분합니다.
Debug.Print "Value"
Dim mark As Variant
For Each mark In arrMarks
Debug.Print mark
Next mark
For Each 루프를 사용하면 LBound에서 UBound까지 단방향으로만 읽을 수 있지만 대부분의 경우 충분합니다.
Erase 함수
Erase 함수는 동적 배열(Dynamic Array)인지 정적 배열(Static Array)인지에 따라 다르게 수행 됩니다.
정적 배열의 경우 Erase 함수는 모든 값을 기본값으로 재설정합니다.배열이 Long 타입으로 구성된 경우 모든 값은 0으로 설정됩니다.배열이 문자열이면 모든 문자열이 ""로 설정됩니다.
동적 배열의 경우 지우기 기능은 메모리를 할당을 해제합니다.즉, 배열을 삭제합니다.다시 사용하려면ReDim을 사용 하여 메모리를 할당해야 합니다.
정적 배열의 예를 살펴보겠습니다.이 예제는 값을 설정한 후 Erase를 사용합니다.값이 출력되면 모두 0이 됩니다.
Public Sub EraseStatic()
' Declare array
Dim arrMarks(0 To 3) As Long
' Fill the array with random numbers
Dim i As Long
For i = LBound(arrMarks) To UBound(arrMarks)
arrMarks(i) = 5 * Rnd
Next i
' ALL VALUES SET TO ZERO
Erase arrMarks
' Print out the values - there are all now zero
Debug.Print "Location", "Value"
For i = LBound(arrMarks) To UBound(arrMarks)
Debug.Print i, arrMarks(i)
Next i
End Sub
이제 동적 배열에 동일한 예제를 실행 해보도록 하겠습니다.Erase를 사용한 후 배열의 모든 요소가 삭제되었습니다.어레이를 다시 사용하려면 ReDim을 사용해야 합니다. 이 배열의 멤버에 액세스하려고 하면"아래 첨자 사용이 잘 못 되었습니다."오류가 발생합니다(이 뜻은 인덱스 참조가 잘 못 되었다는 뜻입니다).
Public Sub EraseDynamic()
' Declare array
Dim arrMarks() As Long
ReDim arrMarks(0 To 3)
' Fill the array with random numbers
Dim i As Long
For i = LBound(arrMarks) To UBound(arrMarks)
arrMarks(i) = 5 * Rnd
Next i
' arrMarks is now deallocated. No locations exist.
Erase arrMarks
Debug.Print arrMarks(0)
End Sub
VBA 배열 길이 늘리기
기존 배열에서 ReDim을 사용하면 배열과 그 내용이 삭제됩니다. 다음 예에서 두 번째ReDim문은 완전히 새로운 배열을 생성합니다.원래 배열과 그 내용이 삭제됩니다.
Sub UsingRedim()
Dim arr() As String
' Set array to be slots 0 to 2
ReDim arr(0 To 2)
arr(0) = "Apple"
' Array with apple is now deleted
ReDim arr(0 To 3)
End Sub
배열의 내용을 보존하며 배열의 길이를 확장하려면Preserve키워드를 사용할 수 있습니다. Redim Preserve를 사용할 때새 배열은 동일한 시작 인덱스를 지정해야 합니다. (0 to 2)의 배열을 (1 to 3)까지 또는 (2 to 10)으로 재할당 한다면 시작 인덱스가 다르기 때문에 내용을 보존할 수 없습니다.
다음 코드에서는ReDim을 사용 하여 배열을 만든 다음 배열을 과일 이름으로 채웁니다. 그런 다음원본 내용을 잃지 않도록Preserve 를 사용하여 배열의 길이를 확장합니다.
Sub UsingRedimPreserve()
Dim arr() As String
' Set array to be slots 0 to 1
ReDim arr(0 To 2)
arr(0) = "Apple"
arr(1) = "Orange"
arr(2) = "Pear"
' Reset the length and keep original contents
ReDim Preserve arr(0 To 5)
End Sub
아래 스크린샷에서 배열의 원래 내용이 "보존"되었음을 알 수 있습니다.
NOTE 대부분의 경우 위의 예 처럼 배열의 크기를 조정할 필요가 없습니다. 만일 동적으로 배열의 크기를 조정해야 할 필요가 있다면 배열 대신 Collection을 사용하는 것이 좋습니다.
2차원 배열에 Preserve 사용하기
Preserve는 상위 배열..그러니까 배열 선언시 뒤에 위치한 배열에 대해서만 작동합니다. 예를 들어 아래와 같은 이차원 배열이 있는 경우 두 번째 차원에 대해서만 Preserve를 적용할 수 있습니다.
Sub Preserve2D()
Dim arr() As Long
' Set the starting length
ReDim arr(1 To 2, 1 To 5)
' Change the length of the upper dimension
ReDim Preserve arr(1 To 2, 1 To 10)
End Sub
하위 배열에 대해 Preserve를 적용하려고하면 "아래 첨자 사용이 잘 못 되었습니다." 오류가 발생합니다. 아래 예제는 오류가 발생하는 것을 보여주고 있습니다.
Sub Preserve2DError()
Dim arr() As Long
' Set the starting length
ReDim arr(1 To 2, 1 To 5)
' "Subscript out of Range" error
ReDim Preserve arr(1 To 5, 1 To 5)
End Sub
엑셀 시트의 Range에서 배열로 읽을 때 열이 하나만 있더라도 자동으로 2차원 배열을 만듭니다. 여기에도 동일한 Preserve 적용 규칙이 적용됩니다.이 예에서 볼 수 있듯이 상위 배열에만 Preserve를 사용할 수 있습니다.
Sub Preserve2DRange()
Dim arr As Variant
' Assign a range to an array
arr = Sheet1.Range("A1:A5").Value
' Preserve will work on the upper bound only
ReDim Preserve arr(1 To 5, 1 To 7)
End Sub
VBA 배열 정렬
VBA에는 배열을 정렬하는 함수가 없습니다.워크시트 셀을 정렬할 수 있지만 데이터가 많으면 속도가 느려질 수 있습니다. 아래의 QuickSort 함수를 사용하여 배열을 정렬할 수 있습니다.
Sub QuickSort(arr As Variant, first As Long, last As Long)
Dim vCentreVal As Variant, vTemp As Variant
Dim lTempLow As Long
Dim lTempHi As Long
lTempLow = first
lTempHi = last
vCentreVal = arr((first + last) \ 2)
Do While lTempLow <= lTempHi
Do While arr(lTempLow) < vCentreVal And lTempLow < last
lTempLow = lTempLow + 1
Loop
Do While vCentreVal < arr(lTempHi) And lTempHi > first
lTempHi = lTempHi - 1
Loop
If lTempLow <= lTempHi Then
' Swap values
vTemp = arr(lTempLow)
arr(lTempLow) = arr(lTempHi)
arr(lTempHi) = vTemp
' Move to next positions
lTempLow = lTempLow + 1
lTempHi = lTempHi - 1
End If
Loop
If first < lTempHi Then QuickSort arr, first, lTempHi
If lTempLow < last Then QuickSort arr, lTempLow, last
End Sub
위 QuickSort 함수는 아래 처럼 사용 할 수 있습니다.
Sub TestSort()
' Create temp array
Dim arr() As Variant
arr = Array("Banana", "Melon", "Peach", "Plum", "Apple")
' Sort array
QuickSort arr, LBound(arr), UBound(arr)
' Print arr to Immediate Window(Ctrl + G)
Dim i As Long
For i = LBound(arr) To UBound(arr)
Debug.Print arr(i)
Next i
End Sub
배열을 함수의 인자로 전달하기
때로는 배열을 함수 인자로 전달해야 합니다.동적 배열을 선언하는 방법과 유사하게 괄호를 사용하여 매개변수를 선언합니다. ByRef를 사용하여 함수에 전달한다는 것은 배열의 참조를 전달한다는 의미입니다.따라서 함수 내에서 배열을 변경하면 리턴 될 때 변경 되어 있습니다.
NOTE 배열을 매개변수로 사용하는 경우 ByVal을 사용할 수 없으며 ByRef를 사용해야 합니다.
Public Sub PassToProc()
Dim arr(0 To 5) As String
' Pass the array to function
UseArray arr
End Sub
Public Function UseArray(ByRef arr() As String)
' Use array
Debug.Print UBound(arr)
End Function
함수에서 배열 반환
함수에서 배열을 반환하기 위해선 배열은 반드시 동적 배열로 생성되어야 합니다.
Public Sub TestArray()
' Declare dynamic array - not allocated
Dim arr() As String
' Return new array
arr = GetArray
End Sub
Public Function GetArray() As String()
' Create and allocate new array
Dim arr(0 To 5) As String
' Return array
GetArray = arr
End Function
Excel 시트의 'Range'로 부터 배열로 읽기
Excel의 Cell과 Range에 대한 이전 게시물을 읽었다면 VBA가 Cell Range에서 배열로 혹은 그 반대로 읽거나 쓰는데 매우 효율적이라는 것을 알게 될겁니다.
Public Sub ReadToArray()
' Declare dynamic array
Dim StudentMarks As Variant
' Read values into array from first row
StudentMarks = Range("A1:Z1").Value
' Write the values back to the third row
Range("A3:Z3").Value = StudentMarks
End Sub
앞의 '2차원 배열 사용하기' 섹션에서 이야기 했듯이 엑셀에서 Range를 통해 읽어 들인 값은 (단 한줄만 읽더라도) 2차원 배열로 만들어 집니다. 다음 예제는 Sheet1의 C3:E6에서 아래 샘플 데이터를 읽고 직접 실행 창에 출력합니다.
Public Sub ReadAndDisplay()
' Get Range
Dim rg As Range
Set rg = ThisWorkbook.Worksheets("Sheet1").Range("C3:E6")
' Create dynamic array
Dim arr As Variant
' Read values into array from sheet1
arr = rg.Value
' Print the array values
Debug.Print "i", "j", "Value"
Dim i As Long, j As Long
For i = LBound(arr) To UBound(arr)
For j = LBound(arr, 2) To UBound(arr, 2)
Debug.Print i, j, arr(i, j)
Next j
Next i
End Sub
결과는 아래와 같습니다.
보시다시피배열 의 첫 번째 차원(i 를 사용하여 액세스)은 행을 나타내고 두 번째 차원은(j 를 사용하여 액세스) 열을 의미합니다. 행이란 엑셀 시트에서 1, 2, 3과 같이 번호로 구분 되고, 열은 A, B, C와 같이 알파벳으로 구분되고 있음을 행과 열을 이해하는데 참고해주세요.
매크로를 초고속으로 실행하는 방법
만일 여러분이 작성한 매크로의 실행 속도가 매우 느리다면 이번 섹션이 도움이 될 수 있습니다. 특히 많은 양의 데이터를 처리하는 경우 다음의 충고가 확실히 도움이 될 수 있습니다.
셀의 값을 직접 업데이트하는것 보다 배열에 쓰고 업데이트 후 다시 셀에 쓰는 것이 훨씬 빠릅니다
지난 섹션에서 셀 그룹에서 배열로 또는 그 반대로 쉽게 읽는 방법을 보았습니다.많은 값을 업데이트하는 경우 다음을 수행할 수 있습니다.
셀의 데이터를 배열로 복사합니다.
배열의 데이터를 변경합니다.
업데이트된 데이터를 어레이에서 다시 셀로 복사합니다.
예를 들어 다음 코드는 그 아래의 코드보다 훨씬 빠릅니다.
Public Sub UpdateCellsFastway()
' Read values into array from first row
Dim StudentMarks As Variant
StudentMarks = Range("A1:Z20000").Value
Dim i As Long
For i = LBound(StudentMarks) To UBound(StudentMarks)
' Update marks here
StudentMarks(i, 1) = StudentMarks(i, 1) * 2
'...
Next i
' Write the new values back to the worksheet
Range("A1:Z20000").Value = StudentMarks
End Sub
Public Sub UpdateCellsSlowway()
Dim c As Variant
For Each c In Range("A1:Z20000")
c.Value = ' Update values here
Next c
End Sub
한 셀 세트에서 다른 셀 세트로 할당하는 것도 복사 및 붙여넣기를 사용하는 것보다 훨씬 빠릅니다.
' 할당 - 더 빠릅니다
Range( "A1:A10" ).Value = Range( "B1:B10" ).Value
' 복사 붙여넣기 - 더 느립니다
Range( "B1:B1" ).Copy Destination:=Range( "A1:A10" )
