[4주차] VBA 4주차 스터디 노트

1. 열심히 공부한 흔적이 담긴 사진을 남겨주세요!

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

• Collection과 Array의 차이점
• DynamicRange 함수와 UniqueTextJoin 함수로 유효성검사(목록) 매크로 만들기
• 코딩은 눈으로 공부하는 것이 아니라, 직접 보고 따라해야 하고, 목적이 분명해야 함(주제를 정하고 프로그램 만들기)

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

• 4주 동안 강의 진행하시느라 고생하셨습니다.
• 저에게 의미 있는 스터디 수업이었습니다.
• 다른 특별 스터디 강의가 있으면 또 듣도록 하겠습니다.

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

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

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

For Each R In Rng

If R.Value = Criteria Then

i = i + 1

End If

Next

'힌트3) MyCountIf 함수의 결과값으로 i를 반환합니다.
'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 까지 돌아가며..

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

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

For i = 1 To Rng.Count

If Rng(i) = Criteria Then

Result = Result + Sum_Range(i)
End If

Next

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

MySumIf = Result

End Function

 

Sub test()

'collection 테스트 명령문
'새로운 collection 만들기

Dim Coll As Collection

Set Coll = New Collection

' collection에 값을 추가하여 특정 순번에 있는 값을 불러온다
'collection.add "값","key"

Coll.Add "사과", "Fruit1"
Coll.Add "배", "Fruit2"
Coll.Add "포도", "Fruit3"
'Key 값은 중복으로 설정할 수 없다

MsgBox Coll("Fruit1")

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

'Stop으로 중간에 매크로를 중단할 수 있다

'collection 조회하기
Dim v As Variant

For Each v In Coll
MsgBox v

Next

End Sub

 

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)
'3주차 강의에서 이용했던 dynamicrange 사용

Delimiter = ","

Set Coll = New Collection

'② Coll 을 하나씩 돌아가며 고유값만 추가하기
'collection 편의성이 높지만 속도는 떨어짐(개체만 만드면 add로 추가) array는 속도는 빠름(값지정)

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) - 1)

MsgBox Result

End Sub

 

Function UniqueTextJoin1(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)
'3주차 강의에서 이용했던 dynamicrange 사용
'Delimiter = ","

Set Coll = New Collection

'② Coll 을 하나씩 돌아가며 고유값만 추가하기
'collection 편의성이 높지만 속도는 떨어짐(개체만 만드면 add로 추가) array는 속도는 빠름(값지정)

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) - 1)

'MsgBox Result

UniqueTextJoin1 = 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, , , "과일, 채소, 육류"
Rng.Validation.Add xlValidateList, , , UniqueTextJoin1(UniqueRng)

End Sub

 

Sub showform()

frmAddProduct.Show

End Sub