엑셀 VBA #27 / 배열 [VBA] - 배열

1. 목적 : 일반 변수 등을 사용하는 것보다 배열을 사용하면 처리 속도 향상된다.

2. 구조
  2.1. 정적배열 : 배열의 크기가 정해진 경우 사용
  Dim a(2)    '3개의 배열 방이 생성
  Dim i As Long

  For i = 0 to 2
     a(i) = i
  Next

2.2. 동적배열 : 배열의 크기가 정해지지 않은 경우 사용
  Dim aa()
  Dim i As Long, cnt As Long

  cnt = 2

  For i = 0 to cnt
     ReDim Preserve aa(i)
  Next

3. 특이사항
배열의 초기화 필요시 Next 다음에 'errase 배열명' 입력함
cf. 정적배열에 배열초기화 할 경우, 배열방은 남아 있으나 배열값은 없어짐

     동적배열에 배열초기화 할 경우, 배열방 및 값이 없어짐

https://www.youtube.com/watch?v=wSoOaVG0BxA&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=27

엑셀 VBA #26 / For Each~Next_2 [VBA] - New Collection

On Error Resume Next
For Each c In rng
      dc.Add Trim(c), CStr(Trim(c))
Next
On Error GoTo 0

For i = 1 To dc.Count
      Cells(i + 11, "A") = dc(i)
      Cells(i + 11, "B") = Application.SumIf(rc, dc(i), rs)

Sub For_Each_Next_and_New_Collection_고유값합계()

Dim dc As New Collection
Dim rng As Range
Dim c As Range
Dim i As Long
Dim rc As Range, rs As Range

Set rng = Range("A2:A" & Range("A2").CurrentRegion.Rows.Count)
'=Set rng = Range("A2", Cells(Rows.Count, "A").End(xlUp))


On Error Resume Next
For Each c In rng
      dc.Add Trim(c), CStr(Trim(c))
Next
On Error GoTo 0

Range("A11") = "고유목록별 합계"

Set rc = Range("A2:A9")
Set rs = Range("B2:B9")

For i = 1 To dc.Count
      Cells(i + 11, "A") = dc(i)
      Cells(i + 11, "B") = Application.SumIf(rc, dc(i), rs)
      
Next
End Sub

핵심정리

On Error Resume Next
For Each c In rng
      dc.Add Trim(c), CStr(Trim(c))
Next
On Error GoTo 0

https://www.youtube.com/watch?v=BC_O6sVx4SA&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=26

How to use VBA Collections in Excel

엑셀 VBA #25 / For Each~Next_1 [VBA] - 시트숨기기

For Each~Next문으로 시트 숨기기

Sub For_Each_Next로_시트_숨기기1()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
      If sh.Name = "메인" Then
      ElseIf sh.Name = "외우기" Then
      Else
            sh.Visible = False
      End If
Next

End Sub

For Each~Next문 & 동적배열로 시트 숨기기

Sub For_Each_Next_and_동적배열로_시트_숨기기()

Dim sh As Worksheet
Dim s()      '시트이름이 '메인''외우기'가 아닌 시트 이름을 각각 보관할 배열방..동적배열적용
Dim i As Long

For Each sh In ThisWorkbook.Worksheets
      If sh.Name = "메인" Then
      ElseIf sh.Name = "외우기" Then
      Else
            ReDim Preserve s(i)
            s(i) = sh.Name   '각각의 s방에 숨길 시트 이름 보관
            i = i + 1
      End If
Next

Sheets(s).Visible = False

End Sub

For Each~Next문에 동적배열을 같이 적용하면 속도가 향상된다.

https://www.youtube.com/watch?v=HIlFL8yniM4&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=25

VBA의 반복문

- For ~ Next

- For Each ~ Next

- Do ~ Loop

엑셀 VBA #21 / For~Next_1 [VBA] 기본

1. 구조
     For 변수 = 시작값 To 끝값 Step 증가값(감소값)
                       반복해서 실행할 구문
    Next

cf, 'Step 증가값(감소값)'이 없다면 'Step 1'과 같음

2.  활용

  Dim i As Long
  Dim cnt As Long

  cnt = Range("A1").CurrentRegion.Rows.Count
  '또는 cnt=range("A1").End(xldown).row

  For i = 2 To cnt
        If Cells(i, "A") >= 90 Then
            Cells(i, "B") = "A"
        ElseIf Cells(i, "A") >= 80 Then
            Cells(i, "B") = "B"
         ElseIf Cells(i, "A") >= 70 Then
            Cells(i, "B") = "C"
        Else
            Cells(i, "B") = "F"
        End If
  Next

https://www.youtube.com/watch?v=-5ofG6Un73A&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=21

엑셀 VBA #20 / Like 연산자_예제2 [VBA]

Like함수 구조 :
If 문자셀 Like 패턴 Then.......

Sub Like연산자_예제2()

Dim i As Long, j As Long
Dim cn As Long

cn = Range("a1").CurrentRegion.Rows.Count
 For i = 2 To cn
      For j = 1 To Len(Cells(i, "a"))
            If Mid(Cells(i, "a"), j, 5) Like "D####" Then
                  Cells(i, "b") = Mid(Cells(i, "a"), j, 5)
            End If
      Next
Next

End Sub

https://www.youtube.com/watch?v=OG7qR-9IsNA&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=20

엑셀 VBA #19 / Like 연산자_예제1 [VBA]

Len(Range("a2"))
a2셀 값의 글자 개수를 알려줘!!!

Mid(Range("a2"), 1, 1)
mid함수 지정 셀 값의 왼쪽에서부터 첫번째 위치한 글자에서부터 글자 하나만 보여줘!!!

 Mid(Range("a2"), 4, 4)
a2셀의 값에서 왼쪽에서 4번째부터 시작하여 4글자만 보여줘!!!

Sub Like연산자_예제()

Dim i As Long
Dim lT As String

For i = 1 To Len(Range("A2"))
      If Mid(Range("a2"), i, 1) Like "[가-힣]" Then
            lT = lT & Mid(Range("a2"), i, 1)
      End If
Next

Range("b2") = lT
'If Len(lT) then Range("b2")=lT  
'위 코드보다 이 코드 좋다는데....
'If Len(lT) then 의미 : lT에 값이 있다면!

End Sub

VBA#37강 Len구문 사용례 참고

    If Len(Cells(i, "B")) Then          ' Cells(i, "B")에 값이 있다면 아래 구문 반복해

https://www.youtube.com/watch?v=SumpOywJJqY&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=19

엑셀 VBA #18 / Like 연산자 [VBA]

Like함수 구조 :
If 문자셀 Like 패턴 Then.......

If 문자셀 Like [A-Z] Then.......     대문자 영문이라면
If 문자셀 Like [a-z] Then.......     소문자 영문이라면
If 문자셀 Like [가-힣] Then.......     한글이라면
If 문자셀 Like [!가-힣] Then.......     한글 아니라면
If 문자셀 Like [0-9] Then.......     숫자라면
If 문자셀 Like "A*" Then.......     대문자A로 시작하는 모든 글자라면
If 문자셀 Like "A?" Then.......     대문자A로 시작하면서 글자 하나가 따라오면
If 문자셀 Like "A#" Then.......     대문자A로 시작하면서 숫자 하나가 따라오면
If Not 문자셀 Like "A*" Then.......     대문자A로 시작하는 모든 글자가 아니라면

A2셀 값이 "A1" 이 맞으며 글자수가 2개라면 메시지박스에 "OK"가 나오는 코드

Sub Like연산자1()

If Range("A2") = "A1" And Len(Range("A2")) = 2 Then MsgBox "OK"
'len함수 : 문자의 개수 파악하는 함수

End Sub

A3셀의 값이 위 그림의 규칙에 부합하면 메시지박스에 "OK"가 나오는 코드

Sub Like연산자2()

If Range("A3") Like "###-[A-D][A-D][A-D]###" Then MsgBox "OK"

End Sub

https://www.youtube.com/watch?v=bnUIgnBITL0&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=19

엑셀 VBA #17 / If 조건문 [VBA]

Sub If조건문()

구문1.
If IsNumeric(Range("A2")) Then MsgBox "숫자입니다."
'If문을 한 줄로 작성하면 End If 필요없음


구문2.
If IsNumeric(Range("A2")) Then
      MsgBox "숫자입니다."
End If

구문3.
If IsNumeric(Range("A2")) Then
      MsgBox "숫자입니다."
Else
      MsgBox "숫자가 아닙니다."
End If

구문4.(다중If문)
If Range("A2") >= 8 Then
      Range("B2") = "합격"
ElseIf Range("A2") >= 6 Then
      Range("B2") = "재시험"
Else
      Range("B2") = "탈락"
End If


End Sub

https://www.youtube.com/watch?v=wlvWfsA91Lc&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=17