Excel VBA ReDim 사용하지 않고 동적 배열변수 & Type문 사용하기

이전 글에서 VBA 동적 배열 변수에 대해 알아보았습니다. 동적 배열 변수를 사용할 때 배열 변수를 선언한 후 ReDim을 이용하여 크기를 선언해줘야 했습니다. 이번에는 이 ReDim을 사용하지 않고 동적 배열 변수를 사용할 수 있는 방법에 대해 알아보고, 더하여 Type 문으로 사용자 정의 데이터 형식을 선언하고 사용하는 방법에 대해 알아보겠습니다.

 

엑셀 VBA 다차원 배열변수 및 동적 배열변수 알아보기

엑셀 VBA 다차원 배열변수 및 동적 배열변수 알아보기

 

 

 

 

1. ReDim 사용 없이 동적 배열 변수 사용

 동적 배열 변수의 크기를 설정하는 ReDim을 사용하지 않고 동적 배열 변수를 사용하려면 변수의 데이터 형을 Variant 형식으로 선언하는 것입니다. 사용 방법은 다음과 같습니다.

 

   Dim 변수명( ) As Variant

 

Dim 명령을 사용하여 Variant 형식의 변수명이란 이름으로 변수를 선언합니다.

Variant 형식은 모든 숫자나 문자열을 할당할 수 있는 데이터 타입이므로 선언된 동적 배열 변수에는 모든 숫자나 문자열이 입력될 수 있습니다.

 

동적 배열 변수는 변수 선언만 한 상태로 프로그램이 진행되고 프로그램 중에서 배열 변수에 값이 입력되면 그때 동적 배열 변수의 크기가 결정됩니다. 그러므로 실제 동적 배열 변수의 크기를 바로 알아보기는 힘이 듭니다. 또한 변수명 뒤의 괄호는 생략할 수 있는데 생략하게 되면 일반 변수인지 배열 변수인지 바로 구별이 되지 않습니다.

 

그래서, 배열의 처음 인덱스 번호와 마지막 인덱스 번호를 확인하는 것이 필요합니다.

배열 변수의 첫 번째 인덱스 번호를 알아내는 함수는 LBound( ) 함수이고, 마지막 인덱스 번호를 알아내는 함수는 UBound( )함수입니다.

 

다음과 같이 VBE 코딩 창에 입력합니다.

 

   Public Sub Array_F()
       Dim r As Variant
       Dim i As Integer

       r = Range("A1:A5")
       i = UBound(r) - LBound(r) + 1

       MsgBox "동적 배열 변수의 방 갯수 : " & i
   End Sub

 

Array_F라는 Sub 프로시저를 선언하고 Variant 형식의 동적 배열 변수 r을 선언하였고 정수형 변수 i를 선언하였습니다.

그리고, r에는 A1:A5 셀 범위를 할당하였습니다.

 

UBound(r)로 동적 배열 변수 r의 마지막 방의 인덱스 번호를 알아내고, LBound(r)로 r의 첫 번째 방의 인덱스 번호를 확인하여 그 값의 차에 1을 더함으로 동적 배열 변수 r에 할당된 전체 방 수를 계산하여 정수형 변수 i에 입력하였습니다.

 

[그림 1] 동적 배열 변수 코딩 결과

코딩을 실행하시면 [그림 1]과 같이 동적 배열 변수 r에 할당된 방의 개수가 5라는 메시장이 출력됩니다.

 

 

 

2. Type 문 사용

 VBA는 사용자 정의 데이터 형식 기능을 제공합니다. 이것은 마치 함수를 선언하여 함수 내에서 사용할 변수를 선언하는데 이 변수 선언하는 부분만을 모은 것과 비슷하다고 생각하시면 될 것 같습니다.

 

사용자 정의 데이터 형식을 사용하는 방법은 다음과 같습니다.

 

   Type 사용자 정의 데이터 형식 이름

        변수명1 As 데이터 형식

        변수명2 As 데이터 형식

                    :

                    :

        변수명n As 데이터 형식

   End Type

 

마치 사용자 정의 데이터 형식 이름의 개체의 속성으로 변수를 선언한 모습입니다. 정한 이름 내에 사용자가 정하고 싶은 데이터 형식의 변수를 지정하면 됩니다. 마지막에 End Type으로 Type 문을 종료합니다.

 

Type 문으로 정의된 데이터 형식을 사용하는 방법도 마치 개체 속성을 사용하듯이 사용자 정의 데이터 형식 이름.변수명1과 같이 사용합니다.

 

이제 예제를 통해 자세히 알아보겠습니다.

 

우선 엑셀 시트 B2 셀에는 '조조', C2 셀에는 '서울'이라 입력하고, VBE 창을 엽니다.

VBE 창을 여는 방법은 아래 링크를 확인하시기 바랍니다.

 

엑셀에서 VBA 프로그램 작성하기

엑셀에서 VBA 프로그램 작성하기

 

혹시 엑셀 메뉴에 VBE 창을 열 수 있는 개발 도구 메뉴가 없다면 아래 글을 참조하시기 바랍니다. 

 

엑셀 매크로 저장 및 사용하기 I

엑셀 매크로 저장 및 사용하기 I

 

열린 VBE 창 메뉴에서 삽입-모듈을 클릭하여 모듈을 생성한 후 다시 삽입-프로시저를 클릭하여 프로시저를 생성합니다. 코딩은 아래와 같이 입력합니다.

 

   Type 주소록
       이름 As String
       거주지 As String
       전화번호 As String
   End Type

   Public Sub 사용자정의()
       Dim 고객(10) As 주소록

       고객(0).이름 = Range("B2").Value
       고객(0).거주지 = Range("C2").Value
       고객(0).전화번호 = Range("D2").Value
    
       MsgBox 고객(0).이름 & "님은 " & 고객(0).거주지 & "에서 살고 있습니다."
   End Sub

 

코드를 입력하고 실행을 하면 엑셀 시트에서 [그림 2]와 같은 메시지 창이 화면에 출력됩니다.

 

[그림 2] Type 문 코딩 실행 결과