엑셀 VBA 배열 에러 null 값 변환 치환('13' 런타임 오류가 발생하였습니다 : 형식이 일치하지 않습니다)

배열을 컨트롤 하다 보면 데이터형식이 맞지 않아서 나오는 13 런타임 오류가 있다.

 

string 형식 배열에 integer를 넣는다거나 그 반대거나 또는 기타 데이터형식 정의가 잘못되었을 경우에 나오는 오류인데 이 부분은 정확한 데이터형식으로 정의해주거나 Variant 형식으로 정의해주면 쉽게 해결 할수 있다.

 

하지만 엑셀 내의 데이터가 아닌 외부데이터를 다이렉트로 끌어다가 배열로 넣을때 심심찮게 Null 값이 들어가는 경우가 종종 있는데 이렇게 배열에 Null 값이 들어가 있을 경우 aryData(1,2) 처럼 값에 접근 또는 기타 배열 컨트롤 함수를 쓰려 할때 역시 '13'런타임 오류가 나온다.

 

위와 같은 Null값 문제의 경우 강제로 Null 값을 vbvbNullString 으로 모두 치환 해주면 해당 오류는 쉽게 해결 가능하다.

 

다음과 같은 Funcion을 만들어 놓고 쓰면 편하다

Function 배열널값변환(배열 As Variant) As Variant

Dim i As Integer

For i = LBound(배열) To UBound(배열)
        If IsNull(배열(i)) Then 배열(i) = vbNullString
Next

배열널값변환 = 배열

End Function

2차원 배열일 경우

Function 이차원배열널값변환(배열 As Variant) As Variant

Dim i, j As Integer

For i = LBound(배열, 1) To UBound(배열, 1)
    For j = LBound(배열, 2) To UBound(배열, 2)
        If IsNull(배열(i, j)) Then 배열(i, j) = vbNullString
    Next
Next

이차원배열널값변환 = 배열

End Function

 

예제

Sub 오류코드()

Dim aryData As Variant

'어떤 외부데이터의 배열을 변수에 넣었을 때
aryData = 외부데이터배열

'이상할것 없는 코드인데 이 부분에서 13 런타임 오류가 날 경우
Debug.Print aryData(1, 2)

'위에 만들어둔 Function을 사용하여 Null값을 VBA가 인식할수 있는 형식으로 변환해준다
aryData = 이차원배열널값변환(외부데이터배열)

'그러면 13 런타임 오류가 해결 될 것이다.
Debug.Print aryData(1, 2)

End Sub