VBA Tips

VBA Tips

 

2024년 12월 17일 화

Sub and Function

'Sub >Sub Procedure > 어떤 동작을 수행할 때(실행문)
'Function > 어떤 값을 결과로 출력해야 할 때
'VLOOKUP, SUMIF, SUM, IF함수
'=VLOOKUP(찾을값, 범위, 열번호, 일치옵션)...

 

 

Function name(함수명)

함수명은 대소문자를 구분하지 않느나 중간중간 대문자를 썩어주는 오탈자 방지가 된다.

 

 

마지막 행과 마지막 열 찾기

    Set StartCell = Me.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

 

마지막 행찾기에서 위에서 내려오는 방법에서 문제점
중간에 셀이 비어 있으면 멈춰버림. 따라서 마지막셀을 찾으려면 시트의 마지막셀에서 위로 올라오는 방법을 사용
행의 갯수는 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