[엑셀 VBA #12] (배열응용) 특정 셀 영역 그대로 복사/이동하기

이전 포스팅 바로가기

[엑셀 VBA #11] (배열#1) 배열 이해하기. 배열의 차원과 정적, 동적배열

[엑셀 VBA #12] (배열응용) 특정 셀 영역 그대로 복사/ 

이동하기

이번 엑셀 VBA 강의에서는 특정 셀을 기준으로 원하는 영역을 그대로 복사 및 이동하는 법에 대해서 배워보겠습니다.

예제를 받고 따라와 주세요.

예제7.xlsm

---

이번 예제에는 두 가지 내용이 들어가 있습니다.

먼저 2차원 배열으로 구성된 셀 영역을 복사/이동하는 엑셀 VBA 코드를 알아보겠습니다.

좌측 상단 모서리에 있는 셀을 기준으로 값이 있는 모든 영역을 파란색 셀을 기준으로 이동하는 코드입니다.

예제 상에는 4x3의 배열으로 정해져 있지만, 사용자가 임의로 배열의 크기를 조정해도 문제 없도록 동적배열을 선언합니다.

Option Explicit            '변수가 선언 없이 사용되는걸 방지

Sub example_7()

Dim varArray                'varArray 배열 선언

Const n = 1                '기준으로할 셀의 행값 n 설정

Const m = 1                '기준으로할 셀의 열값 m 설정

Dim i As Integer

Dim p As Integer

i = Cells(n, m).End(2).Column - m + 1        '배열의 가로 크기 구하기

p = Cells(n, m).End(4).Row - n + 1            '배열의 세로 크기 구하기

ReDim varArray(1 To p, 1 To i)                '위에서 구한 배열의 크기로 배열 재선언

varArray = Cells(n, m).Resize(p, i)                '배열의 크기대로 셀(n,m)을 기준으로 배열 저장

Cells(5, 5).Resize(p, i) = varArray                '셀(5,5)에 저장된 배열 불러오기

End Sub

※Resize문과 End속성이 이해가 되지 않으면 관련 내용이 이전 엑셀 강의에 수록되어 있으니 확인하시고 진행하기 바랍니다.

위 코드를 실행시키면 아래와 같이 출력됩니다.

배열의 크기를 바꾸면 어떻게 출력될까요?

변화한 배열의 크기에 맞춰서 배열이 재선언되고, 크기에 맞춰서 정상적으로 복사됩니다.

---

이제 방금 배운 배열 응용법으로 간단한 회원관리 시스템을 만들어 보겠습니다.

위와 같은 회원관리 시스템에서 회원이 탈퇴했을 경우, 회원의 이름을 선택하고 매크로를 실행하면 아래 과거회원 시트로 해당된 회원 정보를 이동시키는 엑셀 VBA 매크로입니다.

option explicit

Sub example_7_2()

Dim varArray()                    '배열 varArray 선언

Dim n As Long                    '회원 이름이 포함된 셀의 행 n 선언

Dim m As Long                     '회원 이름이 포함된 셀의 열 m 선언

Dim i As Long                        '행의 길이(이동해야 할 셀의 양) 변수 선언

n = Selection.Row                '선택한 셀의 행을 n에 입력

m = Selection.Column                '선택한 셀의 열을 m에 입력

    If m <> 1 Then                    'm의 값이 1이 아닐 경우, 즉 선택한 셀이 이름이 포함된 열이 아닐경우

    MsgBox "회원 이름을 선택해야 합니다."           

    Exit Sub                            '종료

    Else

    End If

    

i = Sheet9.Cells(n, m).End(2).Column                    '이동해야 할 셀의 양 설정

ReDim varArray(1 To i)                            '이동해야 할 셀의 양에 맞춰서 배열 크기 조절

varArray = Sheet9.Cells(n, m).Resize(, i).Value                    '배열에 복사해야 할 값 지정

Sheet10.Cells(Rows.Count, 1).End(3)(2).Resize(, i) = varArray          '데이터가 들어가야 하는 위치를 찾고 배열을 입력 

Selection.EntireRow.Delete                    '과거로 이동한 인원 자료 현재회원에서 제거

End Sub

※만약 Resize 부분과 End(3)(2) 부분이 이해가 되지 않는다면 제가 이전에 올린 Resize와 End속성의 강의를 찾아보세요!

---

눈치가 빠르신 분은 눈치채셨을 수도 있습니다.

사실 위의 두 예제는 배열 선언이 필요 없습니다!

회원관리시스템 예제의 보라색 부분을 배열을 사용하지 않고 아래와 같이 바꿔도 똑같은 결과가 출력됩니다.

Sheet10.Cells(Rows.Count, 1).End(3)(2).Resize(, i) = Sheet9.Cells(n, m).Resize(, i).Value

그렇다면 굳이 배열을 사용하는 이유는 뭘까요?

먼저, 출력되는 값은 같지만, 두 가지 코드는 분명히 다릅니다.

첫 번째 코드는 배열을 선언 후, 복사해야 할 값을 배열(메모리)에 저장한 후 뿌려주었습니다.

두 번째 코드는 단순히 같은 값을 다른 셀에 똑같이 넣었습니다.

배열을 선언해서 메모리에 저장하게 되면, 셀의 값은 이제 배열의 값으로 복제가 되고, 복사하려는 셀이 사라지거나 수정이 되어도 그 값은 그대로 남아있게 됩니다.

반면에, 두 번째 코드처럼 메모리에 값을 저장하는 과정을 건너뛰게 되면 복사하고자 하는 셀의 값이 사라지거나 수정되는 경우 출력에 오류가 생기게 됩니다.

간단하게 예를 들어보겠습니다.

회원관리 예제의 초록색으로 표시된 부분을 보라색 코드 사이에 넣어보겠습니다.

varArray = Sheet9.Cells(n, m).Resize(, i).Value                

Selection.EntireRow.Delete 

Sheet10.Cells(Rows.Count, 1).End(3)(2).Resize(, i) = varArray  

과거 학생 시트로 값이 입력되기 전에 복사하고자 하는 값들을 엑셀 시트에서 삭제했음에도 불구하고, 출력되는 결과는 이전과 동일합니다. 이미 메모리에 배열이 저장되었기 때문에, 엑셀 시트상의 값이 사라진다고 해도 메모리에 저장된 값에는 변동이 없기 때문입니다.

또한 메모리를 사용하는 작업이기 때문에 대량 작업의 경우 작업의 속도가 현저하게 빨라집니다.

따라서 배열을 선언하는 습관을 갖고 있는 것은 정말 중요합니다.

---