오빠두엑셀 라이브 특강 VBA 강의 7월 2일-7월 23일 매주 토요일 9시에 시작합니다.
스터디 신청한 사람만 라이브 강의 일주일 다시 듣기 가능합니다.
사전미션 1. 나만의 Sequence (순서) 매크로 만들기
[사전미션] 시트에서 실행하고 VBA 편집기(alt+ F11)에서 모듈1에 입력함
Column로 시작열을 지정하고, Count로 순번 개수를 지정하면
시작열의 1행부터 순번을 출력하는 매크로를 작성한다.
우선, 변수를 만들어주어야한다.
Sub SequenceNumber()
Dim Column As String
( column= 시작열 , string=문자 / 예) "A", "B", "C", ...)
Dim Count As Long
( count= 출력할 순번 개수, Long = 정수 / 예) 5, 10,15 ...)
Dim i As Long
( i = 숫자변수 / long = 정수 / For문 변수이용)
값에 대한 것이므로 SET 사용X
Column = "A"
Count = 10
For문은 반복이 필요할 때 사용O (출력셀에 입력됨)
For i = 1 To Count
Range(Column & i).Value (값) = i
NEXT
End Sub
Sub SequenceNumber()
Dim Column As String
Dim Count As Long
Dim i As Long
Column = "A"
Count = 10
For i = 1 To Count
Range(Column & i).Value (값) = i
NEXT
End Sub
사전 미션 2. 나만의 Sequence (순서) 매크로를 동적으로 만들기
InitCell로 시작셀을 지정하고, Count로 출력할 순번 개수를 지정하면
InitCell을 기준으로 한칸씩 내려가며 순번을 출력하는 매크로를 작성한다.
Sub DynamicSequence()
Dim InitCell As Range
( InitCell = 시작셀, Range =범위 / 예) Range("A1") )
Dim Count As Long
( Count =출력할 순번 개수, Long =정수 / 예) 5, 10 15 ..)
Dim i As Long
( i = 숫자변수 / long = 정수 / For문 변수이용 )
Set InitCell = Range ("C5")
Count = 10
반복을 위한 For문과
한칸씩 내려가는 데 사용하는 OFFSET(행, (열))이용
For i = 1 To Count
InitCell.Offset(i-1).Value = i
NEXT
End Sub
주의 ! ) offset(i)면 한칸 내려간 6행부터 시작하고 원하는 5부터 하려면 i-1해야함
Sub DynamicSequence()
Dim InitCell As Range
Dim Count As Long
Dim i As Long
Set InitCell = Range ("C5")
Count = 10
For i = 1 To Count
InitCell.Offset(i-1).Value = i
NEXT
End Sub
사전미션 3. 나만의 TextJoin(문자병합) 함수 만들기
[품목 검색] 시트에서 실행함.
'문자를 병합할 범위'를 Rng로 입력하고, 구분자를 지정하면
Rng의 각 셀을 돌아가며 구분자로 병합하는 함수를 작성한다.
Optional은 VLookup함수에서의 일치 옵션처럼 생략 가능하고
맨 뒤에 배치해야한다.
Function MyTextJoin(Rng As Range, _
Optional Delimiter As String = ",")
Rng : 값을 병합할 범위
Delimiter : [선택인수] 구분자이며 기본값은 쉼표(,)
Dim r As Range
Dim Result As String
( 결과로 출력할 문자열 )
For each문은 안에 있는 R을 할당하는 것이 아니라 돌아가면서 보는 것!!
For Each r in Rng
If r.value <>"" Then
(r의 값이 빈칸이 아니라면)
Result = Result & r.value & Delimiter(구분자(쉼표(,))
End If
Next
디버깅을 위해 Result = Result & r.value & Delimiter에 F9를 눌러 중단시키고
시트에 MyTextJoin 함수이용해서 하면 중단된 부분에서 멈춤
F8키를 누르면 다음 End If에 활성화되는 노란색 화살표를 바로 위에 있는 If r.value <>"" Then 에
가져와 Next까지 F8 계속 반복해서 누르면 지역창에 하나씩 추가된다.
출력되는 마지막 단어에 쉼표(,)를 지우기가 필요
MyTextJoin = Left(Result, len(result)-1)
또는
Ien(Result)-len(Delimiter)
를 사용하면 구분자 길이 상관없이 더욱 완벽하게 동작함
End Function
Function MyTextJoin(Rng As Range, _
Optional Delimiter As String = ",")
Dim r As Range
Dim Result As String
For Each r In Rng
If r.Value <> "" Then
Result = Result & r.Value & Delimiter
End If
Next
MyTextJoin = Left(Result, Len(Result) - Len(Delimiter))
End Function
1. 범위 마지막 셀 찾는 매크로 만들기
1-1. Sub 명령문으로 마지막 셀 찾는 매크로 만들기
Sub DynamicRange()
변수선언
Dim WS As Worksheet
Dim Column As String
Dim i As Long
Dim Address As String
(추가) Dim initRow As Long
( initRow 시작 행번호)
Set WS = Sheet1
(개체이므로 SET쓰임)
또는
Set WS = ThisWorkbook.Worksheets("품목검색")
Column = "C"
[데이터 입력된 셀 마지막 가는 방법]
매크로 녹화 클릭 후 젤 마지막 셀을 클릭 하고
데이터 입력된 부분의 마지막(Ctrl+↑) 하고 중단 버튼 누르면 기록됨.
i = WS.Range(Column & "1048576").End(xlUp).Row
매크로 기록으로 알 수 있는 부분
"1048576": 마지막 셀 번호
End(xlUp): 위로 이동해서 마지막부분
Row: 행번호
(initRow 추가시) initRow = 2
Address =Column & i
(initRow 추가시) Address = Column & initRow & ":" & Column & i
MsgBox Address
End Sub
Sub DynamicRange()
Dim WS As Worksheet
Dim Column As String
Dim i As Long
Dim Address As String
Dim initRow As Long
Set WS = Sheet1
Column = "C"
i = WS.Range(Column & "1048576").End(xlUp).Row
initRow = 2
Address = Column & initRow & ":" & Column & i
MsgBox Address
End Sub
1-2. Sub 명령문에서 Function으로 마지막 셀 찾는 매크로 만들기
불러오기 쉽게 Function으로 바꿔주는 게 필요함.
함수의 인수 받기
Function DynamicRange(WS As Worksheet, column As String, initRow As Long) As Range
함수의 인수 받아온 것 제외하고 입력
Dim i As Long
Dim Address As String
i = WS.Range(Column & "1048576").End(xlUp).Row
if i < initRow then i = initRow
(시작행보다 더 위에 있을때)
Address = column & initRow & ":" & column & i
결과값은 개체로 가져옴
Set DynamicRange = WS.Range(Address)
End Function
Function DynamicRange(WS As Worksheet, column As String, initrow As Long)
Dim i As Long
Dim Address As String
i = WS.Range(column & "1048576").End(xlUp).Row
Address = column & initrow & ":" & column & i
Set DynamicRange = WS.Range(Address)
End Function
1-3. 범위 안내 메시지 박스 만들기
Sub Test()
MsgBox DynamicRange(Sheet1, "C", 2).Address
End Sub
2. 나만의 자동필터 매크로 만들기
2-1. 자동필터 매크로 생성
Sub FilterItems()
GroupRng(구분)의 조건을 비교해서,
구분에 해당하는 제품과 가격을 표시
Dim GroupRng As Range
( 필터링 할 구분 범위 (동적으로 설정!) )
Dim r As Range '
( GroupRng를 For Each로 하나씩 참조할 셀 )
Dim FilterVal As String
( 비교할 조건)
Dim i As Long
( r의 값이 조건과 같을 경우, 1씩 증가할 정수 )
Set GroupRng = DynamicRange(Sheet1, "A", 2)
FilterVal = Sheet1.Range("E2").Value
i = 2
For Each r In GroupRng
If r.Value = FilterVal Then
Sheet1.Range("G" & i).Value = r.Offset(0, 1).Value
Sheet1.Range("H" & i).Value = r.Offset(0, 2).Value
(행은 그대로고 오른쪽으로 1,2 이동함)
i = i + 1
End If
Next
End Sub
주의 !) 만약에 i=i+1가 End if 밖으로 나오면 무조건 더해지는 문제가 생김
Sub FilterItems()
Dim GroupRng As Range
Dim r As Range
Dim FilterVal As String
Dim i As Long
Set GroupRng = DynamicRange(Sheet1, "A", 2)
FilterVal = Sheet1.Range("E2").Value
i = 2
For Each r In GroupRng
If r.Value = FilterVal Then
Sheet1.Range("G" & i).Value = r.Offset(0, 1).Value
Sheet1.Range("H" & i).Value = r.Offset(0, 2).Value
i = i + 1
End If
Next
End Sub
2-2. Change 매크로
① SelectionChange
[매크로 편집기]- [Sheet1 ( 품목 검색)] 에서 Worksheet 선택하면 SelectionChange 자동 뜸
SelectionChange은 셀을 클릭하면 바로 반응하게 만듦
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
End Sub
( 클릭하면 바로 반응하여 셀의 주소 메시지창이 뜸)
②Change
Change는 값이 바뀌면 반응함
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub
Change 이벤트 안에 제공된 코드를 넣으면
주의 ! ) 실행하기 위해 필요한 명령문 -무한으로 가지 않게 도와줌
ScreenUpdating은 선택상황
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False (속도 빠르게 해주는)
Application.EnableEvents = False (끝나는걸 빠르게 해주는)
If Not Intersect(Target, Range("E2")) Is Nothing Then
(초기화 추가시)ClearChange
FilterItems (실행할 명령문)
MsgBox Target.Value (또는 FilterItem)
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Range("E2")) Is Nothing Then
ClearRange
FilterItems
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
주의 ! ) 계속 실행되는 문제점 있음 (따라하면 오류 생김)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.offset(1,0).value =1
End Sub
2-3. 범위 초기화 매크로
이전에 필터한 내역이 남아있기 때문에 범위 초기화가 필요하다.
Sub ClearRange()
DynamicRange(Sheet1, "G", 2).ClearContents
DynamicRange(Sheet1, "H", 2).ClearContents
또는
Dim i As long
i = Sheet1.Range( "G1048576").End(xlUp).Row
If i >1 Then
또는
If i Then Exit Sub
Sheet1.Range("G2:H" &i).ClearContents
End if
End Sub
미션!!! (강의에서 다룬 내용)
첫번째 미션 MyTextJoin함수 만들기
두번째 미션 동적범위 DynamicRange 함수 만들기
세번째 미션 필터링 함수 만들기
[강의를 마치며]
처음에 들었던 것보다는 지속적으로 반복하다보니 if, For문 등을 쓸때 약간은 헤갈리는 부분은 있지만 익숙해진 것 같아요. 오타가 생기면 확실히 오류가 생기기 쉬워서 자세히 봐야겠더라고요::: DynamicRange에서 i =i+1이라고 쓰는 걸 1+1이라고 써서 한참 오류 찾아내느라 필터할때도 오류가 연쇄적으로 발생해서 애먹었어요. 선생님이 말하신 학습 곡선대로 학습 효과 나타나서 나중엔 잘 했으면 좋겠네요. 아직까지는 속성들 이용하는 거는 금방 금방 안되더라고요::
'IT > 엑셀' 카테고리의 다른 글
| VBA 4주 완성 마무리_오빠두엑셀 (0) | 2022.08.05 |
|---|---|
| VBA 스터디 4강_오빠두엑셀 (0) | 2022.07.28 |
| VBA스터디2강_오빠두엑셀 (0) | 2022.07.10 |
| VBA스터디 1강_오빠두엑셀 (0) | 2022.07.03 |
