엑셀 VBA 배열 완벽 가이드: 개념부터 실전 활용법까지

VBA는 오랜 기간 마이크로소프트 오피스 제품군에 포함되어 왔습니다. 완전한 VB 애플리케이션만큼의 기능과 성능을 갖추고 있지는 않지만, VBA는 오피스 사용자에게 여러 오피스 제품을 통합하고 반복적인 업무를 자동화할 수 있는 유연성을 제공합니다.

VBA에서 가장 강력한 도구 중 하나는 데이터 범위 전체를 '배열(array)'이라 불리는 단일 변수에 담을 수 있는 기능입니다. 이렇게 데이터를 로드해 두면 해당 범위의 데이터를 다양한 방식으로 조작하거나 계산할 수 있습니다.

그렇다면 VBA 배열이란 무엇일까요? 이 글에서는 그 질문에 답하고, 직접 VBA 스크립트에서 배열을 활용하는 방법까지 소개합니다.

VBA 배열이란 무엇인가?

엑셀에서 VBA 배열을 사용하는 것 자체는 매우 간단하지만, 배열을 처음 접한다면 그 개념을 이해하는 데 다소 시간이 걸릴 수 있습니다.

배열을 내부에 칸이 나뉘어 있는 상자라고 생각해 보세요. 1차원 배열은 한 줄로 칸이 나뉜 상자이고, 2차원 배열은 두 줄로 칸이 나뉜 상자입니다.

이 '상자'의 각 칸에는 원하는 순서대로 데이터를 자유롭게 넣을 수 있습니다.

VBA 스크립트 시작 부분에서 배열을 정의함으로써 이 '상자'를 먼저 만들어야 합니다. 예를 들어 하나의 데이터 집합(1차원 배열)을 담을 수 있는 배열을 만들려면 다음과 같이 작성합니다.

Dim arrMyArray(1 To 6) As String

프로그램 뒷부분에서는 괄호 안에 칸 번호를 지정하여 이 배열의 원하는 위치에 데이터를 넣을 수 있습니다.

arrMyArray(1) = "Ryan Dube"

2차원 배열은 다음과 같이 만듭니다.

Dim arrMyArray(1 To 6,1 to 2) As Integer

첫 번째 숫자는 행을, 두 번째 숫자는 열을 나타냅니다. 따라서 위 배열은 6행 2열 크기의 범위를 저장할 수 있습니다.

배열의 어떤 요소에든 다음과 같이 데이터를 로드할 수 있습니다.

arrMyArray(1,2) = 3

이 코드는 셀 B1에 3을 입력합니다.

배열은 일반 변수와 마찬가지로 문자열, 불리언, 정수, 실수 등 모든 유형의 데이터를 담을 수 있습니다.

괄호 안의 숫자는 변수여도 됩니다. 프로그래머들은 흔히 For 루프를 사용해 배열의 모든 칸을 순회하면서 스프레드시트의 데이터 셀 값을 배열에 로드합니다. 구체적인 방법은 이 글 뒤에서 살펴보겠습니다.

엑셀에서 VBA 배열 프로그래밍하기

스프레드시트에서 정보를 읽어 다차원 배열에 로드하는 간단한 프로그램을 살펴보겠습니다.

예를 들어 제품 판매 스프레드시트에서 영업사원 이름, 품목, 총 판매액을 추출한다고 가정해 보겠습니다.

VBA에서 행이나 열을 참조할 때는 왼쪽 위를 1로 하여 행과 열을 셉니다. 따라서 영업사원 열은 3번째, 품목 열은 4번째, 총액 열은 7번째입니다.

11개 행에 걸쳐 이 세 개의 열을 로드하려면 다음과 같은 스크립트를 작성해야 합니다.

Dim arrMyArray(1 To 11, 1 To 3) As String
Dim i As Integer, j As Integer

For i = 2 To 12
   For j = 1 To 3
      arrMyArray(i-1, j) = Cells(i, j).Value
   Next j
Next i

헤더 행을 건너뛰기 위해 첫 번째 For 루프의 행 번호는 1이 아닌 2에서 시작해야 합니다. 따라서 Cells(i, j).Value로 셀 값을 배열에 로드할 때는 배열의 행 값에서 1을 빼 주어야 합니다.

VBA 배열 스크립트를 삽입하는 위치

엑셀에서 VBA 스크립트를 작성하려면 VBA 편집기를 사용해야 합니다. 리본 메뉴의 개발 도구(Developer) 탭에서 컨트롤 섹션의 코드 보기(View Code)를 선택하면 열 수 있습니다.

메뉴에 개발 도구 탭이 보이지 않는다면 먼저 추가해야 합니다. 파일(File) > 옵션(Options)을 선택해 엑셀 옵션 창을 엽니다.

'명령 선택' 드롭다운을 모든 명령(All Commands)으로 변경합니다. 왼쪽 목록에서 개발 도구(Developer)를 선택하고 추가(Add) 버튼을 눌러 오른쪽 창으로 옮긴 뒤, 체크박스를 선택해 활성화하고 확인(OK)을 눌러 마칩니다.

코드 편집기 창이 열리면 왼쪽 창에서 데이터가 있는 시트가 선택되어 있는지 확인합니다. 왼쪽 드롭다운에서 Worksheet를, 오른쪽 드롭다운에서 Activate를 선택하면 Worksheet_Activate()라는 새 서브루틴이 생성됩니다.

이 함수는 스프레드시트 파일이 열릴 때마다 실행됩니다. 이 서브루틴 안의 스크립트 창에 앞서 작성한 코드를 붙여넣으면 됩니다.

이 스크립트는 12개 행을 순회하면서 3번째 열에서 영업사원 이름을, 4번째 열에서 품목을, 7번째 열에서 총 판매액을 로드합니다.

두 개의 For 루프가 모두 종료되면 2차원 배열 arrMyArray에는 원본 시트에서 지정한 모든 데이터가 담기게 됩니다.

엑셀 VBA에서 배열 조작하기

이번에는 모든 최종 판매 가격에 5% 판매세를 적용한 후, 전체 데이터를 새 시트에 출력한다고 가정해 보겠습니다.

이는 첫 번째 루프 뒤에 또 다른 For 루프를 추가하고, 결과를 새 시트에 기록하는 명령을 넣으면 됩니다.

For k = 2 To 12
   Sheets("Sheet2").Cells(k, 1).Value = arrMyArray(k - 1, 1)
   Sheets("Sheet2").Cells(k, 2).Value = arrMyArray(k - 1, 2)
   Sheets("Sheet2").Cells(k, 3).Value = arrMyArray(k - 1, 3)
   Sheets("Sheet2").Cells(k, 4).Value = arrMyArray(k - 1, 3) * 0.05
Next k

이렇게 하면 전체 배열이 Sheet2로 '언로드'되며, 세금 금액에 해당하는 총액의 5% 값이 담긴 추가 열까지 함께 작성됩니다.

결과 시트는 다음과 같습니다.

이처럼 엑셀의 VBA 배열은 매우 유용하며, 다른 엑셀 기법 못지않게 다재다능하게 활용할 수 있습니다.

위 예제는 배열의 아주 기본적인 활용 사례일 뿐입니다. 훨씬 더 큰 배열을 만들어 저장된 데이터에 대해 정렬, 평균 계산 등 다양한 연산을 수행할 수도 있습니다.

좀 더 창의적으로 활용하고 싶다면 서로 다른 두 시트의 셀 범위를 각각 담은 두 개의 배열을 만들고, 배열 요소 간에 계산을 수행하는 것도 가능합니다.

활용의 폭은 오직 여러분의 상상력에 의해서만 제한됩니다.