1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
| ''
' Dictionary v1.2.0
' (c) Tim Hall - https://github.com/timhall/VBA-Dictionary
'
' Drop-in replacement for Scripting.Dictionary on Mac
'
' @author: tim.hall.engr@gmail.com
' @license: MIT (http://www.opensource.org/licenses/mit-license.php
'
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ '
Option Explicit
' --------------------------------------------- '
' Constants and Private Variables
' --------------------------------------------- '
#Const UseScriptingDictionaryIfAvailable = True
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
' KeyValue 0: FormattedKey, 1: OriginalKey, 2: Value
Private pKeyValues As Collection
Private pKeys() As Variant
Private pItems() As Variant
Private pCompareMode As CompareMethod
#Else
Private pDictionary As Object
#End If
' --------------------------------------------- '
' Types
' --------------------------------------------- '
Public Enum CompareMethod
BinaryCompare = vbBinaryCompare
TextCompare = vbTextCompare
DatabaseCompare = vbDatabaseCompare
End Enum
' --------------------------------------------- '
' Properties
' --------------------------------------------- '
Public Property Get CompareMode() As CompareMethod
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
CompareMode = pCompareMode
#Else
CompareMode = pDictionary.CompareMode
#End If
End Property
Public Property Let CompareMode(Value As CompareMethod)
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
If Me.Count > 0 Then
' Can't change CompareMode for Dictionary that contains data
' http://msdn.microsoft.com/en-us/library/office/gg278481(v=office.15).aspx
ERR.Raise 5 ' Invalid procedure call or argument
End If
pCompareMode = Value
#Else
pDictionary.CompareMode = Value
#End If
End Property
Public Property Get Count() As Long
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Count = pKeyValues.Count
#Else
Count = pDictionary.Count
#End If
End Property
Public Property Get Item(Key As Variant) As Variant
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Dim KeyValue As Variant
KeyValue = GetKeyValue(Key)
If Not IsEmpty(KeyValue) Then
If IsObject(KeyValue(2)) Then
Set Item = KeyValue(2)
Else
Item = KeyValue(2)
End If
Else
' Not found -> Returns Empty
End If
#Else
If IsObject(pDictionary.Item(Key)) Then
Set Item = pDictionary.Item(Key)
Else
Item = pDictionary.Item(Key)
End If
#End If
End Property
Public Property Let Item(Key As Variant, Value As Variant)
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
If Me.Exists(Key) Then
ReplaceKeyValue GetKeyValue(Key), Key, Value
Else
AddKeyValue Key, Value
End If
#Else
pDictionary.Item(Key) = Value
#End If
End Property
Public Property Set Item(Key As Variant, Value As Variant)
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
If Me.Exists(Key) Then
ReplaceKeyValue GetKeyValue(Key), Key, Value
Else
AddKeyValue Key, Value
End If
#Else
Set pDictionary.Item(Key) = Value
#End If
End Property
Public Property Let Key(Previous As Variant, Updated As Variant)
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Dim KeyValue As Variant
KeyValue = GetKeyValue(Previous)
If Not IsEmpty(KeyValue) Then
ReplaceKeyValue KeyValue, Updated, KeyValue(2)
End If
#Else
pDictionary.Key(Previous) = Updated
#End If
End Property
' ============================================= '
' Public Methods
' ============================================= '
''
' Add an item with the given key
'
' @param {Variant} Key
' @param {Variant} Item
' --------------------------------------------- '
Public Sub Add(Key As Variant, Item As Variant)
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
If Not Me.Exists(Key) Then
AddKeyValue Key, Item
Else
' This key is already associated with an element of this collection
ERR.Raise 457
End If
#Else
pDictionary.Add Key, Item
#End If
End Sub
''
' Check if an item exists for the given key
'
' @param {Variant} Key
' @return {Boolean}
' --------------------------------------------- '
Public Function Exists(Key As Variant) As Boolean
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Exists = Not IsEmpty(GetKeyValue(Key))
#Else
Exists = pDictionary.Exists(Key)
#End If
End Function
''
' Get an array of all items
'
' @return {Variant}
' --------------------------------------------- '
Public Function Items() As Variant
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
If Me.Count > 0 Then
Items = pItems
Else
' Split("") creates initialized empty array that matches Dictionary Keys and Items
Items = Split("")
End If
#Else
Items = pDictionary.Items
#End If
End Function
''
' Get an array of all keys
'
' @return {Variant}
' --------------------------------------------- '
Public Function Keys() As Variant
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
If Me.Count > 0 Then
Keys = pKeys
Else
' Split("") creates initialized empty array that matches Dictionary Keys and Items
Keys = Split("")
End If
#Else
Keys = pDictionary.Keys
#End If
End Function
''
' Remove an item for the given key
'
' @param {Variant} Key
' --------------------------------------------- '
Public Sub Remove(Key As Variant)
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Dim KeyValue As Variant
KeyValue = GetKeyValue(Key)
If Not IsEmpty(KeyValue) Then
RemoveKeyValue KeyValue
Else
' Application-defined or object-defined error
ERR.Raise 32811
End If
#Else
pDictionary.Remove Key
#End If
End Sub
''
' Remove all items
' --------------------------------------------- '
Public Sub RemoveAll()
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Set pKeyValues = New Collection
Erase pKeys
Erase pItems
#Else
pDictionary.RemoveAll
#End If
End Sub
' ============================================= '
' Private Functions
' ============================================= '
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Private Function GetKeyValue(Key As Variant) As Variant
On Error Resume Next
GetKeyValue = pKeyValues(GetFormattedKey(Key))
ERR.Clear
End Function
Private Sub AddKeyValue(Key As Variant, Value As Variant, Optional Index As Long = -1)
If Me.Count = 0 Then
ReDim pKeys(0 To 0)
ReDim pItems(0 To 0)
Else
ReDim Preserve pKeys(0 To UBound(pKeys) + 1)
ReDim Preserve pItems(0 To UBound(pItems) + 1)
End If
Dim FormattedKey As String
FormattedKey = GetFormattedKey(Key)
If Index > 0 And Index <= pKeyValues.Count Then
Dim i As Long
For i = UBound(pKeys) To Index Step -1
pKeys(i) = pKeys(i - 1)
If IsObject(pItems(i - 1)) Then
Set pItems(i) = pItems(i - 1)
Else
pItems(i) = pItems(i - 1)
End If
Next i
pKeys(Index - 1) = Key
If IsObject(Value) Then
Set pItems(Index - 1) = Value
Else
pItems(Index - 1) = Value
End If
pKeyValues.Add Array(FormattedKey, Key, Value), FormattedKey, before:=Index
Else
pKeys(UBound(pKeys)) = Key
If IsObject(Value) Then
Set pItems(UBound(pItems)) = Value
Else
pItems(UBound(pItems)) = Value
End If
pKeyValues.Add Array(FormattedKey, Key, Value), FormattedKey
End If
End Sub
Private Sub ReplaceKeyValue(KeyValue As Variant, Key As Variant, Value As Variant)
Dim Index As Long
Dim i As Integer
For i = 0 To UBound(pKeys)
If pKeys(i) = KeyValue(1) Then
Index = i + 1
Exit For
End If
Next i
' Remove existing value
RemoveKeyValue KeyValue, Index
' Add new key value back
AddKeyValue Key, Value, Index
End Sub
Private Sub RemoveKeyValue(KeyValue As Variant, Optional ByVal Index As Long = -1)
Dim i As Long
If Index = -1 Then
For i = 0 To UBound(pKeys)
If pKeys(i) = KeyValue(1) Then
Index = i
End If
Next i
Else
Index = Index - 1
End If
If Index >= 0 And Index <= UBound(pKeys) Then
For i = Index To UBound(pKeys) - 1
pKeys(i) = pKeys(i + 1)
If IsObject(pItems(i + 1)) Then
Set pItems(i) = pItems(i + 1)
Else
pItems(i) = pItems(i + 1)
End If
Next i
If UBound(pKeys) = 0 Then
Erase pKeys
Erase pItems
Else
ReDim Preserve pKeys(0 To UBound(pKeys) - 1)
ReDim Preserve pItems(0 To UBound(pItems) - 1)
End If
End If
pKeyValues.Remove KeyValue(0)
End Sub
Private Function GetFormattedKey(Key As Variant) As String
GetFormattedKey = CStr(Key)
If Me.CompareMode = CompareMethod.BinaryCompare Then
' Collection does not have method of setting key comparison
' So case-sensitive keys aren't supported by default
' -> Approach: Append lowercase characters to original key
' AbC -> AbC__b, abc -> abc__abc, ABC -> ABC
' Won't work in very strange cases, but should work for now
' AbBb -> AbBb__bb matches AbbB -> AbbB__bb
Dim Lowercase As String
Lowercase = ""
Dim i As Integer
Dim Ascii As Integer
Dim Char As String
For i = 1 To Len(GetFormattedKey)
Char = VBA.Mid$(GetFormattedKey, i, 1)
Ascii = Asc(Char)
If Ascii >= 97 And Ascii <= 122 Then
Lowercase = Lowercase & Char
End If
Next i
If Lowercase <> "" Then
GetFormattedKey = GetFormattedKey & "__" & Lowercase
End If
End If
End Function
#End If
Private Sub Class_Initialize()
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Set pKeyValues = New Collection
Erase pKeys
Erase pItems
#Else
Set pDictionary = CreateObject("Scripting.Dictionary")
#End If
End Sub
Private Sub Class_Terminate()
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
Set pKeyValues = Nothing
#Else
Set pDictionary = Nothing
#End If
End Sub
|
相关文章：VBA 35
