이 글의 목적은 엑셀 VBA에서 Dictionary와 Collection 객체 사용 중 발생하는 참조 오류와 키 관련 예외를 체계적으로 진단하고, 실무 코드에서 바로 적용 가능한 표준 오류 처리 패턴과 안전 래퍼 함수를 제공하는 것이다.
1. 개요: 왜 Dictionary·Collection에서 오류가 자주 나는가
Dictionary와 Collection은 키-값 저장과 순회에 최적화된 핵심 도구이나, 참조 미설정, 존재하지 않는 키 조회, 중복 키 추가, 해제 누락 등으로 런타임 오류가 빈번히 발생한다. 특히 Dictionary.Exists 부재(Collection에는 없음)와 On Error의 오용이 복합되어 디버깅이 어려워지는 경우가 많다. 본 가이드는 원인-대응-검증 흐름으로 정리하여 재발을 방지하는 코딩 습관을 확립하는 데 초점을 둔다.
2. 오류 유형 요약표
| 오류 코드 | 증상 | 주요 원인 | 즉시 대응 | 근본 해결 |
|---|---|---|---|---|
| 91 | Object variable or With block variable not set | Set 미실행, 생성 실패 | Set dict = New Scripting.Dictionary 또는 CreateObject 확인 | 생성 래퍼 도입, Nothing 검증 가드 |
| 5 | Invalid procedure call or argument | 존재하지 않는 키 조회, 잘못된 인수 | Exists 선조회(Dictionary), Collection은 트랩 | TryGet 패턴, 안전 접근 함수 |
| 457 | Key already associated with an element | 중복 키 .Add | If Not dict.Exists(k) Then dict.Add k, v | SafeAdd 패턴, 키 정규화 |
| (없음/5) | Collection 인덱서 실패 | 키/인덱스 부재 | On Error Resume Next 후 Err 검사 | Collection용 Exists 대체 함수 구현 |
| (누수) | 종료 후 메모리 점유 | 참조 해제 누락 | Set dict = Nothing | Finally 유사 패턴, 정리 루틴 표준화 |
3. 참조 방식 선택: Early Binding vs Late Binding
Dictionary는 Microsoft Scripting Runtime 참조를 추가하면 Early Binding으로 컴파일 타임 검사와 IntelliSense를 활용할 수 있다. 배포 환경이 불균일하면 Late Binding으로 후퇴한다. 두 방식을 병행하는 생성 래퍼를 제공하면 재사용성이 높아진다.
Option Explicit
' 표준 모듈: Dictionary 생성 래퍼
Public Function NewDict() As Object
' 우선 Early Binding 시도
On Error Resume Next
Dim d As Scripting.Dictionary
Set d = New Scripting.Dictionary
If Err.Number = 0 Then
Set NewDict = d
Exit Function
End If
Err.Clear
' 실패 시 Late Binding
Set NewDict = CreateObject("Scripting.Dictionary")
End Function
주의 : Early Binding을 쓰려면 VBE 메뉴 > Tools > References에서 Microsoft Scripting Runtime을 체크해야 한다.
4. 표준 오류 처리 템플릿
VBA에는 Try..Catch..Finally가 없으므로 레이블을 이용한 패턴을 표준화해야 한다.
Public Sub ExampleTemplate() On Error GoTo EH
' [1] 입력 검증
' ...
' [2] 핵심 처리
' ...
CleanExit:
' [3] 정리 (Finally 대체)
' Set obj = Nothing
On Error GoTo 0
Exit Sub
EH:
' [4] 오류 로깅 및 메시지
Debug.Print "Err"; Err.Number, Err.Source, Err.Description
Resume CleanExit
End Sub
5. Dictionary 안전 접근 패턴
5.1 키 존재 확인 후 접근
Public Function TryGet(ByVal d As Object, ByVal key As Variant, ByRef outValue As Variant) As Boolean ' d: Scripting.Dictionary If d Is Nothing Then Exit Function If d.Exists(key) Then outValue = d.Item(key) TryGet = True End If End Function 5.2 중복 키 안전 추가
Public Sub SafeAdd(ByVal d As Object, ByVal key As Variant, ByVal val As Variant) If d Is Nothing Then Err.Raise 91 If Not d.Exists(key) Then d.Add key, val Else ' 필요 시 병합 규칙 ' d(key) = val ' 덮어쓰기 End If End Sub 5.3 키 제거와 누락 대응
Public Function SafeRemove(ByVal d As Object, ByVal key As Variant) As Boolean On Error GoTo EH If d.Exists(key) Then d.Remove key SafeRemove = True End If CleanExit: Exit Function EH: Resume CleanExit End Function 6. Collection에서의 존재 확인 대체(Exists 부재)
Collection에는 Exists가 없으므로, 오류 트랩 또는 탐색 루틴이 필요하다.
' 키를 문자열로 저장했다고 가정 Public Function ColContainsKey(ByVal c As Collection, ByVal key As String) As Boolean Dim v As Variant On Error Resume Next v = c.Item(key) ' 존재하지 않으면 런타임 오류(통상 5) If Err.Number = 0 Then ColContainsKey = True Err.Clear On Error GoTo 0 End Function 또는 키-인덱스 매핑을 Dictionary로 병행 관리하여 O(1) 조회를 달성한다.
Private Type ColIndex map As Object ' Dictionary bag As Collection End Type 주의 : Collection에서 숫자 인덱스와 문자열 키를 혼용할 때 모호성이 발생할 수 있으므로, 팀 규칙으로 한 가지 방식만 허용하는 것이 바람직하다.
7. 키 정규화와 충돌 방지
키는 공백·대소문자·로케일에 민감하다. 다음 규칙을 권장한다.
- 앞뒤 공백 제거:
Trim적용 - 대소문자 통일:
LCase$또는UCase$ - 유니코드 정규화 필요 시 전처리 함수 도입
Public Function NormKey(ByVal s As String) As String NormKey = LCase$(Trim$(s)) End Function Call SafeAdd(dict, NormKey(rawKey), value) 8. 초기화·해제와 수명 관리
객체 수명 관리 실패는 난해한 버그를 만든다. 생성은 한 곳, 해제는 Finally 블록에서 일괄 처리한다.
Dim dict As Object Set dict = NewDict() ' ... 사용 ... ' 종료 루틴 Set dict = Nothing 주의 : 전역(Dictionary/Collection)을 남용하면 테스트 간 누적 상태가 섞인다. 테스트마다
Set ... = Nothing으로 초기 상태를 보장한다.9. 방어적 반복과 수정 중 안전성
반복 중 항목 삭제는 인덱스 이동과 충돌을 야기한다. 안전한 패턴을 사용한다.
' Dictionary: 키 스냅샷 후 삭제 Dim k As Variant Dim keys As Variant keys = dict.Keys For Each k In keys If ShouldDelete(dict(k)) Then dict.Remove k Next k ' Collection: 역순 삭제 Dim i As Long For i = c.Count To 1 Step -1 If ShouldDelete(c(i)) Then c.Remove i Next i 10. 예외 안전 래퍼 세트(실무 복붙용)
Public Function EnsureDict(ByRef d As Object) As Object If d Is Nothing Then Set d = NewDict() Set EnsureDict = d End Function
Public Function GetOrDefault(ByVal d As Object, ByVal key As Variant, ByVal defaultValue As Variant) As Variant
If d Is Nothing Then
GetOrDefault = defaultValue: Exit Function
End If
If d.Exists(key) Then
GetOrDefault = d(key)
Else
GetOrDefault = defaultValue
End If
End Function
Public Sub AddOrUpdate(ByVal d As Object, ByVal key As Variant, ByVal val As Variant)
If d.Exists(key) Then
d(key) = val
Else
d.Add key, val
End If
End Sub
11. 로깅과 재현성 확보
오류는 즉시 기록하고 입력 스냅샷을 남겨야 재현이 가능하다.
Public Sub LogErr(ByVal proc As String) Debug.Print Now, proc, "Err", Err.Number, Err.Description End Sub Public Sub Demo() On Error GoTo EH Dim d As Object: Set d = NewDict() Call SafeAdd(d, "a", 1) Call SafeAdd(d, "a", 2) ' 457 방지됨 Dim v: If TryGet(d, "b", v) Then Debug.Print v CleanExit: Set d = Nothing Exit Sub EH: Call LogErr("Demo") Resume CleanExit End Sub 12. 입력 검증과 계약 설계
함수의 선행 조건을 명시하고, 어길 경우 즉시 오류를 발생시켜 조기 실패를 유도한다.
Public Sub RequireDict(ByVal d As Object) If d Is Nothing Then Err.Raise 91, "RequireDict", "Dictionary is Nothing" End Sub 13. 성능 고려: 대량 추가와 존재 확인
- 존재 확인은 O(1)로 빠르나, 대량 삽입 시 키 정규화 비용이 지배적이다.
- 가능하면 원시 데이터에서 중복 제거 후 배치 추가한다.
- Collection 탐색 기반
Exists대체는 O(n)이므로 대규모 데이터에는 부적합하다.
14. 테스트 하니스(매크로) 예시
Public Sub Test_DictionaryPatterns() Dim d As Object: Set d = NewDict()
' 1) AddOrUpdate
AddOrUpdate d, "k1", 10
AddOrUpdate d, "k1", 99
' 2) TryGet
Dim v
Debug.Print "TryGet k1:", TryGet(d, "k1", v), v
Debug.Print "TryGet kX:", TryGet(d, "kX", v)
' 3) Remove
Debug.Print "Remove k1:", SafeRemove(d, "k1")
' 4) 마무리
Set d = Nothing
End Sub
15. 현장에서 자주 겪는 함정과 점검 체크리스트
| 항목 | 체크 포인트 | 권장 조치 |
|---|---|---|
| 참조 설정 누락 | 컴파일 오류 없이 실행하지만 91 발생 | 생성 래퍼로 Early/ Late 자동 전환 |
| 중복 키 | 457 또는 값 덮어쓰기 | SafeAdd 또는 AddOrUpdate 채택 |
| 부재 키 조회 | 5 발생 | TryGet 또는 GetOrDefault 사용 |
| 반복 중 삭제 | 누락 또는 예외 | Dictionary는 키 스냅샷, Collection은 역순 |
| 해제 누락 | 메모리 점유 지속 | Finally 블록에서 일괄 Set ... = Nothing |
16. 배포 호환성 가이드
- 32/64비트 차이는 Dictionary/Collection에 직접 영향이 적으나, 외부 COM 참조 경로 차이가 오류를 유발할 수 있다.
- Late Binding은 배포 호환성에 유리하나 IntelliSense가 제한된다.
- 버전별 VBE 보안 설정에 따라 매크로 차단으로 인한 간접 오류가 발생하므로 서명 정책을 별도 관리한다.
17. 실전 템플릿: 안전한 키-값 저장 서비스
' 모듈 수준 상태 Private gStore As Object
Public Sub Store_Init()
Set gStore = NewDict()
End Sub
Public Sub Store_Set(ByVal key As String, ByVal val As Variant)
Call AddOrUpdate(gStore, NormKey(key), val)
End Sub
Public Function Store_TryGet(ByVal key As String, ByRef outValue As Variant) As Boolean
Store_TryGet = TryGet(gStore, NormKey(key), outValue)
End Function
Public Sub Store_Close()
Set gStore = Nothing
End Sub
18. 디버깅 팁
- 중단점과
Immediate창을 사용해dict.Count,dict.Exists("k")등을 즉시 확인한다. Err.Clear를 남발하지 말고, 처리 후 즉시 정리한다.- 핵심 경로에서는
On Error Resume Next대신 명시적 가드 코드를 선호한다.
FAQ
Dictionary를 클래스 필드로 둘 때 91 오류가 난다.
생성자를 흉내 내는 Class_Initialize 이벤트에서 Set dict = NewDict()로 초기화하면 된다. 모듈 전역 변수는 Workbook_Open 등 수명 시작 루틴에서 초기화한다.
Exists가 있는데 왜 TryGet이 필요한가?
Exists 후 Item 접근 사이의 시간차에 상태 변경이 개입할 수 있다. TryGet은 원자적 목적을 표현하여 가독성·오류율을 개선한다.
Collection에서 키 기반 접근을 계속 써야 하나?
빈번한 조회가 필요하면 Dictionary로 교체하거나, Dictionary를 보조 인덱스로 함께 사용한다.
중복 키는 덮어쓰기와 예외 중 무엇이 바람직한가?
업무 규칙에 따른다. 덮어쓰기를 표준으로 정하면 AddOrUpdate, 중복 자체를 에러로 보고 싶으면 SafeAdd와 로깅을 채택한다.
런타임 5와 457을 로그에서 구분하는 이유는?
5는 잘못된 인수·부재 키 등 원인이 넓고 457은 중복 키로 좁다. 구분 로깅은 원인 추정 속도를 높인다.