[4주차] 엑셀 VBA 기초 4주 완성 특별 스터디 - 4회 부론 스터디 노트, 미션

1. 열심히 공부한 흔적이 담긴 사진을 남겨주세요!
(위쪽 카메라 버튼을 클릭해서 이미지를 삽입할 수 있습니다)

2. 이번 스터디에서 새롭게 알게되거나 유익했던 내용을 3가지로 요약해주세요!

①array 배열의 기초에 대해서 교육

②MyCountIf, MySumIf, UniqueTextJoin 등의 function값을 작성한후 블록을 가져오는것 같이

하나씩 조합해가면서 최종적으로 완성하는방식

③유저폼을 이용한 기본적인 input, output 작성

④추후 강의나 공부를 하더라도 자신이 필요로 하는게 무엇인지 확인 후

그 필요로 하는것을 위주로 공부를 해나가는게 꾸준히 할수 있고 숙련도도 빨리오름

 

3. 이번 스터디를 진행하면서 특별히 좋았던 점이나 부족했던 점이 있다면 자유롭게 남겨주세요.

MyCountIf, MySumIf, UniqueTextJoin 등의 function 블록을 작성 후

조합하여 최종적으로 하나의 vba프로그램을 완성시키는 과정의 기초를 알수 있게됨

 

 

4. 열심히 학습한 결과파일이나 스크린샷, 코드가 있다면 자유롭게 올려주세요.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Not Intersect(Target, Range("E2")) Is Nothing Then
'값이 변경된 셀이 "E2"셀 이면
ClearRange '① 출력범위 초기화 후
FilterItems '② 필터링 결과 출력하기
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

'유효성검사를 클릭할때마다 고유값을 새로고침
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

 

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

Function MyCountIf(Rng As Range, Criteria As Variant) As Long
'---------------------------------
'■사전미션1
'나만의 CountIF 함수 만들기
'Rng 범위에서 Criteria와 같은 값의 개수를 반환합니다.
'---------------------------------
Dim R As Range
Dim i As Long

'힌트1) Rng의 셀을 하나씩 돌아가며..
'For Each R In Rng
For Each R In Rng
If R.Value = Criteria Then
i = i + 1
End If
Next

MyCountIf = i

'힌트2) R의 값이 Criteria와 같을 경우, i에서 1을 더합니다.
'If R.Value = Criteria Then..

'힌트3) MyCountIf 함수의 결과값으로 i를 반환합니다.
'MyCountIf = i

End Function

Function MySumIf(Rng As Range, Criteria As Variant, Sum_Range As Range) As Long
'---------------------------------
'■사전미션2
'나만의 SumIF 함수 만들기
'Rng 범위의 값이 Criteria와 같을 경우, Sum_Range의 합계를 반환합니다.
'---------------------------------

Dim i As Long
Dim Result As Double '소수점까지 포함 = double

'힌트1) Rng.Count 속성을 사용하면 조건범위의 개수를 확인할 수 있습니다.
'힌트2) i = 1 부터 Rng.Count 까지 돌아가며..
For i = 1 To Rng.Count
If Rng(i) = Criteria Then
Result = Result + Sum_Range(i)

End If
Next

MySumIf = Result

'힌트3) Rng(i)을 사용하면 범위에서 i번째 위치한 값을 반환합니다.
'힌트4) Rng에서 i번째 위치한 값이 Criteria와 동일할 경우..
'If Rng(i) = Criteria Then..

'힌트5) 기존 Result 에 Sum_Range(i) 번째 값을 더합니다.
'Result = Result + Sum_range(i)

'힌트3) MySumIF 함수의 결과값으로 Result를 반환합니다.
'MySumIf = Result

End Function

Sub test()

'Collection 테스트 명령문
'# 새로운 Coleection 만들기
Dim Coll As Collection
Set Coll = New Collection

'# Collection 에 값을 추가
'Coleection.Add "값", "Key" key값은 한개만 지정, 값은 중복가능
Coll.Add "사과", "Fruit1"
Coll.Add "배", "Fruit2"
Coll.Add "포도", "fruit3"

'#Collection 값 지우기
Coll.Remove ("Fruit3")

'#Coleection 조회하기
Dim v As Variant
For Each v In Coll
MsgBox v
Next

End Sub

'Function UniqueTextJoin(Rng As Rnage, Optional Delimiter As String = ",") =뭔차?
Function UniqueTextJoin(Rng As Range, Optional Delimiter As String = ",")
'------------------------------------
'■ 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)
'Delimiter = ","
Set Coll = New Collection

'② Coll 을 하나씩 돌아가며 고유값만 추가하기
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 Function

Sub AddValidation()
'------------------------------------
'■ Rng 셀에 데이터유효성 목록상자를 추가합니다.
'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
'------------------------------------
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

Sub ShowForm() '유저폼 매크로로 연결해주기

frmAddProduct.Show

End Sub

 

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
i = i + 1
End If
Next

End Sub

Sub ClearRange()

Dim i As Long

i = Sheet1.Range("G1048576").End(xlUp).Row

If i > 1 Then
Sheet1.Range("G2:H" & i).ClearContents
End If

End Sub

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