'#새로운 Collection 만들기
Dim Coll As Collection
Set Coll = New Collection
'#Collection의 값 제거하기
Coll.Remove (Key)
'#Collection에 값 추가하기
Coll.Add 값
Coll.Add 값, Key
'#Collection 값 돌아가며 확인하기
Dim v As Variant
For Each v In Coll
Debug.Print v
Next
마지막 행찾기에서 위에서 내려오는 방법에서 문제점 중간에 셀이 비어 있으면 멈춰버림. 따라서 마지막셀을 찾으려면 시트의 마지막셀에서 위로 올라오는 방법을 사용 행의 갯수는 ver2003이전에는 2^16=65536행, 이후 '2^20=1048576행
변수 정의
'Dim 통합문서 As Workbook 'Set 통합문서 = ThisWorkbook '현재통합문서 'Set 통합문서 = ActiveWorkbook '현재 실행중인 또 다른 활성화된 통합문서를 할당 'Set 통합문서 = Application.Workbooks.Open(경로) '경로에 있는 특정파일을 실행해서 그 통합문서로 만들어준다.
'Dim 시트 As Worksheet 'Set 시트 = ActiveSheet 'Set 시트 = 통합문서.Worksheets("시트이름" 또는 순번) '실무에서는 이것만 알아도 된다. 'Set 시트 = Sheet1
'Dim 범위 As Range 'Set 범위 = 시트.Range("a1:a2") 'Set 범위 = 시트.Cells(행번호, 열번호) 'Set 범위 = Selection
하나의 모듈내에 실행문의 이름과 함수의 이름이 같으면 에러가 난다.
다음은 Sub DynamicRange(), Function DynamicRange(ws As Worksheet, Column As String, InitRow As Long) As Range 이렇게 같은 이름을 가진 실행문과 함수가 같은 모듈에 들어 있을때 나타나는 오류메시지이다.
Change Event사용시 주의사항
무한 반복에 빠질 수 있다.
따라서 Change Event에 아래 코드를 넣어준다.
Private Sub Worksheet_Change(ByVal Target As Range)
'Change 이벤트는 셀이 변경되었을 때 동작한다.
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Range("셀주소")) Is Nothing Then
'실행할 명령문
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Collection 알아두면 좋은 Tip
'#새로운 Collection 만들기
Dim Coll As Collection
Set Coll = New Collection
'#Collection의 값 제거하기
Coll.Remove (Key)
'#Collection에 값 추가하기
Coll.Add 값
Coll.Add 값, Key
'#Collection 값 돌아가며 확인하기
Dim v As Variant
For Each v In Coll
Debug.Print v
Next
Sub ExpandRange()
Dim Rng As Range
Dim StartCell As Range
Dim LastRow As Long
Dim LastCol As Long
' 초기 시작 셀 설정 (예: A1)
Set StartCell = Range("A1")
' 마지막 행과 마지막 열 찾기
LastRow = StartCell.Worksheet.Cells(StartCell.Worksheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastCol = StartCell.Worksheet.Cells(StartCell.Row, StartCell.Worksheet.Columns.Count).End(xlToLeft).Column
' 동적 범위 정의
Set Rng = StartCell.Worksheet.Range(StartCell, StartCell.Worksheet.Cells(LastRow, LastCol))
' 결과 출력
MsgBox "새로 정의된 범위: " & Rng.Address, vbInformation, "확장된 영역"
End Sub
코드 설명
시작 셀 지정
Set StartCell = Range("A1")를 통해 데이터가 시작되는 첫 번째 셀을 지정합니다.
마지막 행과 열 찾기
LastRow: 시작 열에서 데이터가 있는 마지막 행을 찾습니다.
LastCol: 시작 행에서 데이터가 있는 마지막 열을 찾습니다.
동적 범위 정의
Range(StartCell, Cells(LastRow, LastCol))를 사용하여 새로 확장된 영역을 동적으로 설정합니다.
결과 확인
확장된 범위를 메시지 박스로 출력하여 확인할 수 있습니다.
데이터 추가 후 자동 실행 (워크시트 변경 이벤트 활용)
데이터가 추가될 때마다 자동으로 영역이 확장되도록 하려면 워크시트 변경 이벤트를 활용할 수 있습니다.
이벤트 코드: 워크시트 변경 감지
Private Sub Worksheet_Change(ByVal Target As Range)
Dim StartCell As Range
Dim LastRow As Long
Dim LastCol As Long
Dim Rng As Range
' 초기 시작 셀 설정 (예: A1)
Set StartCell = Me.Range("A1")
' 마지막 행과 열 찾기
LastRow = Me.Cells(Me.Rows.Count, StartCell.Column).End(xlUp).Row
LastCol = Me.Cells(StartCell.Row, Me.Columns.Count).End(xlToLeft).Column
' 동적 범위 정의
Set Rng = Me.Range(StartCell, Me.Cells(LastRow, LastCol))
' 확장된 범위를 표시 (선택적)
Debug.Print "확장된 영역: " & Rng.Address
End Sub
사용 방법
해당 코드를 변경하려는 워크시트의 코드 창에 추가합니다.
VBA 편집기에서 해당 워크시트를 선택 후 추가.
데이터를 추가하거나 변경하면 자동으로 동작합니다.
결과
원래 데이터: $A$1:$B$10
새 데이터 추가: A11 또는 B11에 데이터를 추가하면 자동으로 $A$1:$B$11로 범위가 확장됩니다.
ReDim은 VBA에서 동적 배열의 크기를 변경할 때 사용되는 키워드입니다. Dim을 사용해 배열을 선언할 때 크기를 미리 지정하지 않고, 코드 실행 중 필요한 크기로 배열을 조정할 수 있습니다.
ReDim의 주요 목적
동적 배열 크기 조정: 초기에는 크기를 알 수 없는 배열의 요소 개수를 ReDim을 사용해 설정할 수 있습니다.
메모리 효율성: 필요한 크기만큼만 배열을 설정하고, 데이터 추가나 변경에 따라 배열 크기를 조정함으로써 메모리를 효율적으로 사용할 수 있습니다.
실행 중 배열 크기 변경: 배열 크기를 코드 실행 도중 변경해야 할 때 유용합니다.
사용 예시
다음은 동적 배열을 ReDim으로 설정하고 변경하는 예입니다.
Sub Example()
Dim myArray() As Integer ' 크기를 지정하지 않은 배열 선언
' 배열의 크기 설정 (초기 크기: 5)
ReDim myArray(1 To 5)
myArray(1) = 10
' 배열 크기 재설정 (변경된 크기: 10)
ReDim myArray(1 To 10)
End Sub
ReDim Preserve의 사용
기존 데이터가 있는 배열 크기를 변경하면서 데이터를 유지하려면 ReDim Preserve를 사용해야 합니다. Preserve를 붙이지 않으면 배열의 모든 요소가 초기화됩니다.
Sub ExampleWithPreserve()
Dim myArray() As Integer
ReDim myArray(1 To 5)
myArray(1) = 10
' 배열 크기 변경, 기존 데이터 유지
ReDim Preserve myArray(1 To 10)
End Sub
