오빠두엑셀 라이브 특강 VBA 강의 7월 2일-7월 23일 매주 토요일 9시에 시작합니다.
스터디 신청한 사람만 라이브 강의 일주일 다시 듣기 가능합니다.
엑셀 파일은 제가 직접 창작으로 제공한게 아니다보니 그냥 여기다 올리기 애매해서
오빠두엑셀에서 작성한 엑셀 완성본이 있는 스터디 노트 링크 올립니다.
그김에 오빠두엑셀 홈페이지에 있는 선생님이 올리신
유익한 엑셀 내용들도 보실 수 있으세요.
https://www.oppadu.com/study-note/?uid=40504&mod=document&pageid=1
[4주차] VBA 스터디 노트 4강
1. 열심히 공부한 흔적이 담긴 사진을 남겨주세요! (위쪽 카메라 버튼을 클릭해서 이미지를 삽입할 수 있습니다) 2. 이번 스터디에서 새롭게 알게되거나 유익했던 내용을 3가지로 요약해주세
www.oppadu.com
사전미션 1. 나만의 CountIF 함수 만들기
CountIf는 인수 2가지가 있을 때 이용해서 만드는 함수이다.
Function MyCountIf(Rng As Range, Criteria As Variant) As Long
( Rng As Range 범위, Criteria As Variant 조건을 만족하는 개수 )
Dim R As Range
Dim i As Long (i는 정수)
( 변수를 숫자로 만들면 기본값은 0으로 적용하여 0으로 초기화하지 않아도 됨 )
For Each R In Rng
( Rng의 셀을 하나씩 돌아가며 적용될 때는 For Each )
If R.Value = Criteria Then (범위의 값이 조건과 같으면)
i = i + 1
End If
Next
MyCountIf = i ( 결과값을 i으로 반환함 )
End Function
Function MyCountIf(Rng As Range, Criteria As Variant) As Long
Dim R As Range
Dim i As Long
For Each R In Rng
If R.Value = Criteria Then
i = i + 1
End If
Next
MyCountIf = i
End Function
사전 미션 2. 나만의 SumIF 함수 만들기
Rng 범위의 값이 Criteria와 같을 경우, Sum_Range의 합계를 반환합니다.
Function MySumIf(Rng As Range, Criteria As Variant, Sum_Range As Range) As Long
(Rng As Range, Sum_Range As Range인 구분 범위, 합계 범위는 절댓값으로 )
Dim i As Long
Dim Result As Double
( Double 쓰는 이유는 정수뿐만 아니라 소수점 포함할 수 있기 때문에)
For i = 1 To Rng.Count ( i = 1부터 Rng.Count 까지 돌아감)
(For Each를 쓰면 R의 위치가 몇 번째인지 알기 쉽지 않아 Count를 써서 순번을 직접 만듦 )
If Rng(i) = Criteria Then
(Rng에서 i번째 위치한 값이 조건과 동일하다면)
Result = Result + Sum_Range(i)
( 기존 Result에 Sum_Range(i) 번째 값을 더함)
End If
Next
MySumIf = Result
End Function
Function MySumIf(Rng As Range, Criteria As Variant, Sum_Range As Range) As Long
Dim i As Long
Dim Result As Double
For i = 1 To Rng.Count
If Rng(i) = Criteria Then
Result = Result + Sum_Range(i)
End If
Next
MySumIf = Result
End Function
내가 필요한 값들만 어디 한 곳에 모아서 편하게 추가하고 사용하는 좋은 방법이 없을까?
Collection, Dictionary, Array
Collection VS Array 다른 점은 무엇인가?
| Collection (또는 Dictionary) | Array | |
| 비유(통장) | 입/ 출금 한도에 제한없는 자유 입출금 통장 | 입금 예정 금액을 반드시 정해야 하는 청약/ 적금 통장 |
| 비유(이자) | 낮음 | 높음 |
| 속도 | 느림 | 빠름 |
| 편의성 | 높음 | 낮음 (사용할 배열 크기를 미리 정해야함 미리 많이 만들면 공간 낭비이고 찾기가 어렵다) |
| 속성 | O (값만 있는 것 아님) | X (값만 있음) |
| 형식예시 | Dim Coll As Collection Set Coll = Next Collection Coll.Add "사과" Coll.Add "배" Coll. Add "포도" |
Dim Arr As Vriant Redim Arr(0 to 1) (배열안에 2개만 넣는) Arr(0)= "사과" Arr(1) =" 배" "포도",,,? (포도는 다른 방법으로 넣어야함) |
실습에 사용되는 형식
#새로운 Collection 만들기
Dim Coll As Collection
Set Coll = New Collection
#collection에 값 추가하기
Coll.Add 값
Coll.Add 값, Key
#Collection의 값 제거하기
Coll.Remove(Key)
#Collection 값 돌아가며 확인하기
Dim v As Variant
For Each v In Coll
Debug.Print v
Next
1. Collection 테스트 명령문 만들기
Sub Test()
#새로운 Collection 만들기
Dim Coll As Collection
(coll이라는 변수를 Collection으로 쓴다는)
Set Coll = New Collection
# collection에 값을 추가
Collection.Add "값","키(실무에서는 특정 코드를 쓰므로)" 입력
Coll.Add "사과", "Fruit1"
Coll.Add "배", "Fruit2"
Coll.Add "포도", "Fruit3"
( Key는 중복 입력하면 오류남 )
#Collection 값 지우기
Coll.Remove ("Fruit3")
#Collection 조회하기
Dim v As Variant
For Each v In Coll
MsgBox v
Next
MsgBox Coll("Fruit1")
End Sub
Sub Test()
Dim Coll As Collection
Set Coll = New Collection
Coll.Add "사과", "Fruit1"
Coll.Add "배", "Fruit2"
Coll.Add "포도", "Fruit3"
Coll.Remove ("Fruit3")
Dim v As Variant
For Each v In Coll
MsgBox v
Next
MsgBox Coll("Fruit1")
End Sub
주의!) 매크로 편집기에서 직접 명령문 입력 시 소문자로 작성 이유는 오타를 발견하기 쉽기 때문이다.
2. UniqueTextJoin 테스트 명령문 만들기(Sub)
고유값을 취합하는 함수
2-1. 변수 선언 및 할당하기
Sub UniqueTextJoin()
( Rng 범위의 고유값으로 이루어진 문자열 )
Dim Rng As Range
Dim Delimiter As String
Dim R As Range ( Rng를 For Each로 하나씩 참조할 셀 )
Dim Coll As Collection
Dim v As Variant ( Coll을 For Each로 하나씩 참조할 값 )
Dim Result As String (출력 문자열)
Set Rng = DynamicRange(Sheet1, "A", 2)
( 이전 시간 작성한 명령문에서 동적 범위 받아오는 함수 DynamiRange 이용)
Delimiter = ", " (구분자)
Set Coll = New Collection
2-2. Coll 을 하나씩 돌아가며 고유값만 추가하기
On Error Resume Next
(R.Value 값이 있어 오류 발생하기 때문에 이걸 추가하면 다음으로 넘어가게 함)
For Each R In Rng
Coll.Add R.Value, R.Value
( R.Value는 값과 키 각 각을 보여줌 고유값만 만들어진 것 )
Next
On Error GoTo 0
(오류 발생하면 오류창 뜨게 함)
Stop을 이용해 확인 작업 후 다시 Stop 삭제
2-3. 고유값으로 이루어진 문자열 만들기
For Each v In Coll
Result = Result & v & Delimiter ( 문자 결합 형태 )
Next
Result = Left(Result, Len(Result) - Len(Delimiter))
또는 Left( Result, Len(Result) -1))
2-4. 결과값 확인하기
MsgBox Result
End Sub
Sub UniqueTextJoin()
Dim Rng As Range
Dim Delimiter As String
Dim R As Range
Dim Coll As Collection
Dim v As Variant
Dim Result As String
Set Rng = DynamicRange(Sheet1, "A", 2)
Delimiter = ","
Set Coll = New Collection
On Error Resume Next
For Each R In Rng
Coll.Add R.Value, R.Value
Next
On Error GoTo 0
For Each v In Coll
Result = Result & v & Delimiter
Next
Result = Left(Result, Len(Result) - Len(Delimiter))
MsgBox Result
End Sub
2. UniqueTextJoin 테스트 명령문 만들기(Function)
2-1. 변수 선언 및 할당하기
앞에 ' 와 녹색 된 부분은 Function에서는 지움
Function UniqueTextJoin( Rng As Range, Optional Delimiter As String = "," )
'Dim Rng As Range
'Dim Delimiter As String
Dim R As Range ( Rng를 For Each로 하나씩 참조할 셀 )
Dim Coll As Collection
Dim v As Variant ( Coll을 For Each로 하나씩 참조할 값 )
Dim Result As String (출력 문자열)
'Set Rng = DynamicRange(Sheet1, "A", 2)
( 이전 시간 작성한 명령문에서 동적 범위 받아오는 함수 DynamiRange 이용)
'Delimiter = "," (구분자)
Set Coll = New Collection
2-2. Coll을 하나씩 돌아가며 고유값만 추가하기
On Error Resume Next
(R.Value 값이 있어 오류 발생하기 때문에 이걸 추가하면 다음으로 넘어가게 함)
For Each R In Rng
Coll.Add R.Value, R.Value ( R.Value는 값과 키 각 각을 보여줌 고유값만 만들어진 것 )
Next
On Error GoTo 0
(오류가 발생하면 오류창 뜨게 함)
2-3. 고유값으로 이루어진 문자열 만들기
For Each v In Coll
Result = Result & v & Delimiter ( 문자 결합 형태 )
Next
Result = Left(Result, Len(Result) - Len(Delimiter))
또는 Left( Result, Len(Result) -1))
2-4. 결과값 확인하기
UniqueTextJoin = Result
End Function
Function UniqueTextJoin( Rng As Range, Optional Delimiter As String = "," )
Dim R As Range
Dim Coll As Collection
Dim v As Variant
Dim Result As String
Set Coll = New Collection
On Error Resume Next
For Each R In Rng
Coll.Add R.Value, R.Value
Next
On Error GoTo 0
For Each v In Coll
Result = Result & v & Delimiter
Next
Result = Left(Result, Len(Result) - Len(Delimiter))
UniqueTextJoin = Result
End Sub
3. 목록 상자 매크로 만들기
3-1. 매크로 기록으로 목록 상자
왼쪽 화단 녹화 버튼이나 [개발도구] - [매크로 기록]으로 매크로1 생성하여 녹화한 후
[데이터] - [데이터 유효성]에서
제한대상: 목록
원본: 과일, 채소, 육류
실행한 후에 녹화 중지 버튼 누르고 모듈 2에 있는 명령문를 본다.
매크로 기록한 내용 중 필요한 것만 남기고 남겨야 한다.
Validation 은 유효성 검사로 삭제를 하는 건 오류가 생기므로 지워주는 게 필요하다.
같은 변수를 여러 번 작성할 때는 With문을 사용하면 편리합니다!
#With문 예제 1
Dim WS As WorkSheet
Set WS = Sheet1
WS. Range("A1").Ront.Size = 14
Ws.Range("A1").interior. color = vbRed
Ws.Range("A1").RowHeight = 15
↓
#With문 예제 2
Dim WS As WorkSheet
Set WS = Sheet1
With WS. Range("A1")
.Font.Size = 14
.interior. color = vbRed
.RowHeight = 15
End With
3-2. 목록 상자 자동화 매크로 만들기
[모듈 2에 보이는 속성에 대한 설명]
Range.Validation.Add (유효성 목록상자 추가 시)
① xlDVType : 데이터 유효성 검사 형식입니다. xlValidateList
② AlterStype : 오류 메시지 형식입니다. (기본값은 중지(Stop)입니다. 선택 인수)
③ Operator : 연산 방식입니다. (목록에서는 사용하지 않습니다. 선택 인수)
④ Formula1 : 데이터 유효성 검사로 작성할 수식 또는 목록입니다.
⑤ Formula2 : 연산 방식이 포함 또는 포함하지 않음일 경우, 두 번째로 작성하는 수식입니다.
[상세 설명 링크]
오류 메시지 형식 : https://docs.microsoft.com/en-us/office/vba/api/excel.xldvalertstyle
연산 방식 : https://docs.microsoft.com/en-us/office/vba/api/excel.xlformatconditionoperator
Validation Add 상세 설명 : https://docs.microsoft.com/en-us/office/vba/api/excel.validation.add
Sub AddValidation()
Dim Rng As Range
Dim UniqueRng As Range
Set Rng = Sheet1.Range("E2") 등록할 셀
Set UniqueRng = DynamicRange(Sheet1, "A", 2) 고유값 가져올 범위
Rng.Validation.Delete
(기존 유효성 검사 삭제해야 오류 안 뜸)
Rng.Validation.Add xlValidateList, , , UniqueTextJoin(UniqueRng)
Rng.Validation.Add : 유효성 검사 추가
xlValidateList : 목록 상자
오류 중지: 빈칸
연산 : 빈칸
UniqueTextJoin(UniqueRng) :고유값 문자열(함수(범위))
주의!) 빨간 부분 직접 입력시에는 디버그 오류 발생::
End Sub
Sub AddValidation()
Dim Rng As Range
Dim UniqueRng As Range
Set Rng = Sheet1.Range("E2")
Set UniqueRng = DynamicRange(Sheet1, "A", 2)
Rng.Validation.Delete
Rng.Validation.Add xlValidateList, , , UniqueTextJoin(UniqueRng)
End Sub
처음에 직접 입력때 괜찮다가 두 번째 때부터는 직접 구분에 음료 입력 시 데이터 유효성 제한이라고 뜨네요::
질문으로 올려봐야겠어요::
3-3. 시트 이벤트로 목록 상자 실시간 갱신하기
매크로에서 Sheet1( 품목 검색)에서 [Worksheet] -[SelectionChange]에서 명령문 작성해야 한다.
3강 나만의 자동 필터 만들기에서 Change 관련한 이벤트 시트와 같이 만들어준다.
https://noregretlife.tistory.com/4
[엑셀] VBA 스터디 3강_오빠두엑셀
오빠두엑셀 라이브 특강 VBA 강의 7월 2일-7월 23일 매주 토요일 9시에 시작합니다. 스터디 신청한 사람만 라이브 강의 일주일 다시 듣기 가능합니다. 사전미션 1. 나만의 Sequence (순서) 매크로 만
noregretlife.tistory.com
If Not Intersect(Target, Range("E2")) Is Nothing Then
AddValidation (실행할 명령문)
이 부분에서 빨간 부분만 바꿔서 응용 가능하다.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Range("E2")) Is Nothing Then
AddValidation
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
4. 사용자 정의폼 만들기
4-1. 유저폼 만들기
[매크로 편집기] - [삽입]- [사용자 정의폼] - [도구상자]
(도구상자가 안 보이면 [보기] -[ 도구상자], 속성은 F4키)
[속성에서 변경할 내용]
이름: frmAddProduct
Caption: 제품등록 (폼 위에 이름뜨는 거)
Font - [...]- 맑은 고딕, 보통 , 12
①속성 변경 내용 설정 후 A로 되어있는 레이블을 눌러
복붙하여 구분, 제품명, 가격을 만듦.
② abl 이라고 쓰인 네모인 텍스트 상자를 레이블 옆에
똑같이 복붙 하여 배치함.
③ ab 네모 상자인 명령 단추 클릭해 제품 등록이라고 만듦
( 두 번 클릭하면 매크로 편집기로 이동함 )
④ 재생 버튼을 눌러 확인
4-2. 사용자 정의폼에 명령문
①텍스트 상자의 속성창에 이름을 변경해주기
구분:txtCategory
제품명:txtProduct
가격:txtPrice
제품등록: txtSubmit
②제품등록 버튼 두 번 눌러 이벤트 명령문 작성함.
Private Sub btnSubmit_Click()
Dim i As Long
i = Sheet1.Range("A1048576").End(xlUp).Row + 1
Sheet1.Range("A" & i).Value = Me.txtCategory.Value
Sheet1.Range("B" & i).Value = Me.txtProduct.Value
Sheet1.Range("C" & i).Value = Me.txtPrice.Value
MsgBox "제품등록을 등록하였습니다."
( 등록 후 사용하기 편리하게 삭제하는 기능 추가로 )
Me.txtCategory.Value = ""
Me.txtProduct.Value = ""
Me.txtPrice.Value = ""
End Sub
4-3. 명령문 매크로 추가하기
사용자폼 나타나게 명령문하고 도형에 매크로 지정
Sub ShowForm()
frmAddProduct.Show
End Sub
Q&A 정적/ 가변 Array 차이점
Sub TestArray()
Dim Arr (o to 10) As Variant
(Variant 값으로 뭐가 들어갈지 모르기 때문에 동적으로 쓸 때)
Dim Arr (o to 10)As String
(기본값 빈칸)
Dim Arr (o to 10)As Long
(기본값 0)
End sub
강의를 마치며
막연하게 어려웠던 매크로 작성하는 법을 이해하면서 작성하다 보니 무조건 막막하진 않지만 아직도 어려운 부분이나
오류가 생길 때가 있다 보니 여러 번 반복을 해야겠다는 생각이 드네요. 이번 스터디도 강의가 알차서 너무 좋은 시간이
였습니다. 지난 4주간 강의 들으면서 어렵지만 배우고 싶었던 내용이라 보람차네요. 좀 더 효율적인 엑셀 이용을 할 수
있도록 노력해야겠다는 생각이 드네요.
'IT > 엑셀' 카테고리의 다른 글
| VBA 4주 완성 마무리_오빠두엑셀 (0) | 2022.08.05 |
|---|---|
| VBA 스터디 3강_오빠두엑셀 (0) | 2022.07.20 |
| VBA스터디2강_오빠두엑셀 (0) | 2022.07.10 |
| VBA스터디 1강_오빠두엑셀 (0) | 2022.07.03 |
