엑셀 VBA 기본기능 정리 - 변수·자료형·배열·컬렉션 선언 / 반복문

VBA의 부족한 기능성을 보완하기 위해 배열을 자주 쓸 것 같지만....

배열도 지원하는 기능이 너무 적어 생각보다 쓰지 않게되네요.

그래도 중복값제거때문에 컬렉션은 가끔 쓰게됩니다.

 

 

 

엑셀 VBA 기본기능 정리 - 변수·자료형·배열·컬렉션 선언 / 반복문

• 변수, 자료형 선언
• 전역/지역 설정
• 개요 : 서브프로시저 내부에서 설정한 변수는 해당 프로시저를 벗어나면 사용할 수 없다. 즉 subA의 변수 a, b는 subA에서 functionB 호출시 활용할 수 없다. 따라서 사용할 지역에 따라 선언방법을 구분해야 한다.
• 지역의 구분 및 선언 방법 : Modules > Module > Subroutine
• 모든 모듈에서 공유 가능한 변수의 선언 : 활용할 서브프로시저가 작성된 module의 최상단 라인(option explicit 라인)에 변수를 Global 혹은 Public으로 선언한다.ex) Global rng as Range
• 모듈 내부에서 서브프로시저끼리 공유 가능한 변수 선언 : 활용할 서브프로시저가 작성된 module의 최상단 라인(option explicit 라인)에 변수를 Dim 혹은 자료형으로 선언한다.ex) Dim rng as Range
• 서브프로시저 내부에서만 사용하는 변수 선언 : 서브프로시저 내부에서 Dim 혹은 자료형으로 선언한다.
• 자료형 설정
• 만능자료형 : Dim 변수명 // (Variant로 선언됨)
• 정수 : Int / Long(추천)
• 부동소수형 : Float / Double
• 배열
• 선언 : Dim arr(Index)
• 크기변경 : ReDim Preserve arr(Index) (예를 들면 arr(1 to 5)에 대해, Redim arr(1 to 10) 가능)
• 가장 ‘마지막 차원’의 ‘UBound’만 변경 가능하다
• 시작, 중간 차원의 크기 혹은 LBound를 변경하려면 배열을 신규로 선언해야 한다.
• Preserve 없이 Redim을 사용하면 배열을 초기화하며, Preserve사용시 기존값들은 잔존한다.
• 모두삭제 : Erase arr
• 크기확인 : UBound(arr)-LBound(arr)+1※ UBound는 끝점의 위치, LBound는 시작점의 위치이다. 배열이 반드시 0부터 시작하지는 않으므로 두 메서드를 모두 활용해야 정확한 값이 나옴에 주의
• 기존 내용 유지하며 사용하기(for문 등에서 사용) : ReDim Preserve arr()
• 활용
• arr = Range(범위).Value를 통해 값들을 쉽게 배열로 옮길 수 있다.
• Range(범위) = arr()을 통해 특정 범위로 값들을 옮길 수 있다. * 이때, 값들은 arr(Row, Column)의 순서로 채워 넣어진다. 즉, Z순서로 입력, 출력된다. 또한, 안타깝게도 배열의 차원을 감소시키거나 합산하는 별도의 명령어는 없다.
• 주의사항
• VBA 배열은 크기 확인, 원소 여부 확인 등의 기능이 없는 등 기본성능이 빈약하다.
• 컬렉션과 달리 중간에 무언가를 삽입하거나 삭제하려면 새로운 크기의 배열을 선언하고, 일시적인 배열에 복사 및 원본에의 삽입/삭제하는 동작을 반복해야 한다.
• 선언시 Index를 비운채 선언하면, 동적배열로 선언한다.
• 범위의 값을 배열로 옮길 때, 자동으로 2차원의 형태로 입력된다. 출력 역시 마찬가지로 이뤄지므로, 동일한 크기의 범위가 아니라면 배열의 일부는 출력되지 않거나 오류로 표기된다.
• 배열값을 범위로 옮길 때, 범위가 배열의 크기보다 작다면 범위의 크기만큼만 복사되며 별도의 오류는 없다. 그러나 범위가 더 클 경우, 배열의 크기보다 큰 공간에는 #N/A가 표기된다.
• 컬렉션 선언
• 선언 : Dim myCol As New Collection
• 사용
• 추가 : myCol.Add "삽입데이터", "키 값", Before:=3 (혹은 After:="10번항목")
• 삭제 : myCol.Remove(index)
• 모두삭제 : Set myCol = Nothing
• 출력 : myCol.Item(index) 혹은 myCol.Key(Index)
• 활용예 – 중복값 제거

Public Sub 중복값제거by컬렉션()             			'컬렉션개체를 이용한 중복값 제거기능
Dim uCollection As New Collection           			'컬렉션 선언
Dim c As Range
On Error Resume Next                        			'중복값이 예외처리되므로 On error사용
    For Each c In Selection                 			'For Each 사용으로 중복값 반복검출
    uCollection.Add Trim(c), CStr(Trim(c))  			'Item/Key값에 동일한 값을 Trim하여 넣었다
    Next
On Error GoTo 0                             			'On Error가 사용되었으니, GoTo 0도 삽입한다
For i = 1 To uCollection.Count
    Cells(i, Selection(1).Offset(, 1).Column) = uCollection(i)		'선택장소의 옆 column에 고유값입력
Next
End Sub

• 반복문
• For – Next : for 변수 = 시작값 to 끝값 Step 증가값(감소값) ~ (실행문) ~ Next
  * 변수값은 next를 거칠때마다 자연증가하므로, 별도의 ‘변수 = 변수+1’등의 계산이 필요 없음
• For Each 변수 In 범위 ~ Next
• 범위에 있는 모든 개체를 순회하며, 변수에 해당하는 개체를 조작하는 반복문
• 변수값이 자연적으로 변하지 않으므로, 별도의 변수선언 및 순회에 따른 증감 정의 필요
• 다음으로 넘기기 : VBA에 continue는 따로 없으므로, next 문구 이전에 continue_1: 등의 마커를 만들고 특정 조건 불만족시 goto continue_1 등의 형태로 next 직전으로 이동하는 형태를 사용해야 한다.