For Each ~ Next

1. 정의
배열(Array) 또는 컬렉션(Collection, 오브젝트 모음)의 요소마다 실행문을 반복합니다.
Object(객체)에는 Range, Workbook, Worksheet, Shape 등이 있습니다.

2. 구문

3. 구문에 대한 설명

element	필수, 배열 또는 오브젝트 집합(Collection)의 요소를 반복하기 사용되는 변수 - 배열 : 요소(element)는 Variant 변수여야 함 - 컬렉션 : 요소는 Variant 또는 일반적이거나 특수한 객체여야 함
group	필수. 오브젝트 집합 또는 배열의 이름
statements	선택. 그룹의 각 요소마다 실행되는 하나 이상의 실행문

3. 사용 예제

(예제 1)
일정한 범위를 하나씩 돌아가면서 실행하는 구분을 만들면 아래와 같습니다.

Option Explicit

Dim rng As Range

Sub foreach1()
    For Each rng In Range("a1:a10")
        Range("b" & rng.Row) = rng * 2
    Next
End Sub

Dim rng As Range : rng를 범위 변수로 선언합니다. 그런데 sub 프로시저 밖에서 선언해서 모든 프로시저에 적용되도록 했습니다.

Sub foreach1 ~ End Sub : sub 프로시저는 sub 프로시저로 시작해서 end sub로 끝납니다. 프로시저(매크로) 명을 sub 다음에 입력하고 인수를 괄호 안에 입력하는데, 없으면 입력하지 않아도 됩니다.

For Each rng In Range("a1:a10") : A1셀부터 A10셀까지 한 셀씩 반복합니다.

Range("b" & rng.Row) = rng * 2 :
- 범위를 지정하는데 b란 문자와 rng.Row를 결합하는데 rng.Row는 처리하는 셀의 행수를 의미하므로 A1셀을 처리하고 있다면 1행이므로 Range("b" & 1)이 돼서 B1셀이 됩니다.

- rng * 2는 처리하고 있는 셀(rng)의 값에 2를 곱한 값입니다.

따라서 처리하고 있는 셀의 값에 2를 곱한 값을 B열 같은 행에 입력하는 것입니다.

위 매크로를 실행한 다음 엑셀로 돌아가서 확인해보면 A열에 1부터 10까지 입력되어 있고, B열에서 A열의 값에 2를 곱한 값이 입력됐습니다.

(예제 2)
값으로 들어간 것을 수식으로 바꾸려면 아래와 같이 하면 됩니다.

Sub foreach2()
    For Each rng In Range("a1:a10")
        Range("b" & rng.Row) = "=" & rng.Address & " * 2"
    Next
End Sub

Range("b" & rng.Row) = "=" & rng.Address & " * 2" :
수식으로 입력해야 하므로 =을 입력하고 & 결합 연산자를 이용해 rng.Address, 처리하고 있는 셀의 주소와 연결하고, 다시 * 2와 연결합니다. 따라서 모두 결합하면 =A1 * 2 가 됩니다. 그런데 기본이 절대 주소 형식이므로 =$A$1 * 2가 됩니다.

실행하고 엑셀로 돌아가서 확인하면 B1셀에 2가 아니라 =$A$1 * 2가 입력되어 있습니다.

(예제 3) 절대 주소 형식을 상대 주소 형식으로 변경하기
Address 다음에 행과 열순으로 절대 주소 형식을 지정하는데, 절대 주소 형식인 경우는 1, 상대 주소 형식인 경우는 0을 입력하며, 기본 값은 1, 절대 주소 형식입니다.

[절대 주소, 상대 주소 형식 지정의 예]
rng.Address 또는 rng.Address(1,1) : 행, 열 모두 절대 주소 형식, $A$1식이 됩니다.
rng.Address(1,0) : 행만 절대 주소 형식이므로 A$1 식이 됩니다.
rng.Address(0,1) : 열만 절대 주소 형식이므로 $A1 식이 됩니다.
rng.Address(0,0) : 행, 열 모두 상대 주소 형식, A1 식이 됩니다.

따라서 위 수식을 상대주소 형식으로 입력하려면 아래와 같이 하면 됩니다.


따라서, 위 수식을 상대 주소 형식으로 변경하려면

Sub foreach3()
    For Each rng In Range("a1:a10")
        Range("b" & rng.Row) = "=" & rng.Address(0, 0) & " * 2"
    Next
End Sub

실행 결과는 아래와 같습니다.

(예제 4)
범위를 변수로 지정 가능하며, 아래와 같이 입력하면 됩니다.

Sub foreach4()
    Dim rng2 As Range
    Set rng2 = Range("a1:a10")
    
    For Each rng In rng2
        Range("b" & rng.Row) = "=" & rng.Address(0, 0) & " * 2"
    Next
End Sub

Dim rng2 As Range : rng2 변수를 범위 변수로 선언합니다.

Set rng2 = Range("a1:a10") : 범위 변수에 범위를 대입하려면 Set 메서드를 이용하며, 그다음은 일반 변수에 값 대입하는 방식과 같습니다.

위 매크로를 실행하면 예제 3과 같은 결과가 됩니다.

(예제 5)
배열을 이용하려면 아래와 같이 해야 합니다.

Sub foreach5()
    Dim array1(0 To 9) As Integer
    Dim i As Integer, j As Variant, k As Integer
    
    For i = 2 To 11
        array1(i - 2) = i
    Next
    
    k = 1
    For Each j In array1
        Range("c" & k) = j
        k = k + 1
    Next
End Sub

Dim array1(0 To 9) As Integer : array1이란 배열을 10개 요소를 가진 정수 형식으로 선언합니다.

Dim i As Integer, j As Variant, k As Integer : 아래에서 필요한 변수를 선언하는데 i와 k는 정수 형식, j는 Variant(가변형, 여러 가지 형식이 될 수 있음) 형식으로 선언합니다.
dim i, k as integer로 선언하면 k만 정수 타입이고, i는 Variant 타입이 되기 때문에 위와 같이 각각 변수의 형식을 선언해야 합니다.

For i = 2 To 11 array1(i - 2) = i Next

i가 2부터 11까지 반복될 때 array1 배열의 (i-2) 인덱스에 i를 대입합니다.
배열의 인덱스는 0부터 시작하기 때문에 i-2로 해서 2일 때 0이 되도록 했습니다.
따라서, array1배열에는 2부터 11까지 정수가 들어가 있습니다. 인덱스는 0부터 9입니다.

k = 1 For Each j In array1 Range("c" & k) = j k = k + 1 Next

k = 1 : k란 변수에 1을 대입합니다.

For Each j In array1 : 배열의 각 요소마다 반복합니다. 다시 말해 array1의 인덱스 0부터 9까지 반복합니다.

Range("c" & k) = j : C열의 k행에 j 요소, 다시 말해 array1 배열의 요소를 입력합니다. k가 1일 때는 C1셀에 array1(0)에 해당하는 2를 입력합니다.

k = k + 1 : 아래 줄로 내려가면서 배열의 값을 입력하기 위해 k변수의 값을 1씩 증가시킵니다. 그러면 한 줄씩 내려가면서 배열의 요소, 2,3,4... 가 하나씩 입력됩니다.

위 매크로를 실행하고 엑셀로 돌아가면 아래와 같이 C열에 2부터 11까지 값이 입력되어 있습니다.

(예제 6)
아래는 시트를 하나씩 반복하면서 시트명을 d열에 입력하는 매크로입니다.

Sub foreach6()
    Dim sht As Worksheet
    
    For Each sht In Worksheets
        Range("d" & sht.Index) = sht.Name
    Next
End Sub

Dim sht As Worksheet : sht 변수를 Worksheet 형식으로 지정합니다.

For Each sht In Worksheets : 여러 개의 워크시트를 하나씩 반복합니다.

Range("d" & sht.Index) = sht.Name : sht.index는 처리하고 있는 워크시트의 인덱스 번호로 1부터 시작합니다.
sht.Name은 처리하고 있는 워크시트의 이름을 말하므로 D열에 워크시트의 이름을 행방향으로 내려가면서 입력합니다.

실행한 결과는 아래와 같습니다. D1셀에는 첫 번째 워크시트의 이름 Sheet1이, D2셀에는 두 번째 워크시트의 이름 Sheet2가 입력됐습니다.

(예제 7)
Sheet2를 선택한 다음 삽입 탭에서 도형 명령을 누르고,

직사각형, 원과 평행 사변형을 만들었습니다.

도형의 이름을 A열에 표시하려면 아래와 같이 매크로를 작성합니다.

Sub shapename()
    Dim i As Integer, shp As Shape
    i = 1
    For Each shp In ActiveSheet.Shapes
        Range("a" & i) = shp.Name
        i = i + 1
    Next
End Sub

Dim i As Integer, shp As Shape :
- i변수를 정수 형식으로 선언합니다. 위 foreach5에서도 i변수를 동일하게 선언했으므로 모듈 상단으로 이동시켜서 공통적으로 적용될 수 있도록 하는 것이 바람직합니다.

- shp 변수를 Shape 형식으로 선언합니다.

i = 1 : i변수의 초기값을 1로 선언합니다.

For Each shp In ActiveSheet.Shapes : 도형을 하나씩 돌아가면서 반복합니다.

Range("a" & i) = shp.Name : A열에 도형의 이름을 하나씩 기록합니다.

i = i + 1 : 한 행 아래에 입력하기 위해 i값을 1씩 증가시킵니다.


실행한 다음 엑셀로 돌아가서 확인해 보면 A1셀에는 Rectangle 1(직사각형 1)이라고 입력되어 있고, A1셀에는 Oval 2(타원 2), A3셀에는 Parallelogram 3(평행 사변형 3)이라고 입력되어 있습니다.

(예제 8)
도형을 모두 지우려면 아래와 같이 작성합니다.

Sub shapedel()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        shp.Delete
        
    Next
End Sub

Dim shp As Shape : shp 변수를 Shape 형식으로 선언합니다.

For Each shp In ActiveSheet.Shapes : 도형을 하나씩 돌아가면서 반복합니다.

shp.Delete : 선택된 도형을 삭제합니다.


매크로는 되돌리기(취소, Ctrl + Z)가 안되므로 먼저 저장하고 매크로를 실행합니다. 그리고, 엑셀로 돌아가면 도형이 모두 지워진 것을 알 수 있습니다.

 

foreach.xlsm

0.02MB