Quote:
Originally Posted by whatsup
Hi 
CoolBlue, as to your question:
The loop is there, though it's rather a For ... Next:
Code:
.
.
.
For lngItem = 1 To UBound(arrCompare, 1)
If Len(CStr(arrCompare(lngItem, 1))) > 0 Then
objdic(arrCompare(lngItem, 1)) = ""
End If
Next lngItem
.
.
.
You don't need the .Add method to fill the object. The problem with this method is, that the object doesn't allow enter a key that already exists. Therefore the following code will throw an error towards you on the second add:
Code:
Sub AddCausingError()
Dim objdic As Object
Set objdic = CreateObject("scripting.dictionary")
objdic.Add 1, ""
objdic.Add 1, ""
Set objdic = Nothing
End Sub
If nonetheless you will use add, you always have to check first if the key already exists:
Code:
Sub AvoidErrorAdd()
Dim objdic As Object
Set objdic = CreateObject("scripting.dictionary")
objdic.Add 1, ""
If Not objdic.Exists(1) Then
objdic.Add 1, ""
Else
MsgBox "Key already exists"
End If
Set objdic = Nothing
End Sub
Especially if Item isn't of any interest, you can directly put the key into the dictionary, and you get a list of unique key-values without worrying about errors:
Code:
Sub JustOverwrite()
Dim objdic As Object
Set objdic = CreateObject("scripting.dictionary")
objdic(1) = "1stEntry"
objdic(1) = "2ndEntry"
Debug.Print objdic.Count
Debug.Print objdic(1)
Set objdic = Nothing
End Sub
As the immediate window will show, there is just one key and the item is the last which was added to the key.
For the other part, well, Paul has provided you by now with some evidence, and there is more out there. I'm pretty aware of other opinions pointing out it's not necessary and I won't interfere if they got the convidence and made up their mind. There are also people believing it's not necessary to declare variables, pointing out that vba comes by default without "Option Explicit". Well, some day they will discover an error and wonder were it comes from ...  |
Wow! I still didn't get it so I coded it and now I think your method is even cooler...
I have worked with Collections in the past and haven't delved into Dictionaries so, thanks for opening my eyes. I love it... you just jam in the Keys by referencing by it and set the member to "". And there they all are. Very nice!
Using your method on my system it blasts through 100K lines in 1.6 to 2 sec, depending on the data type. The data I used had about 30% "Others".
My little Value2 tweak gives a big advantage to the block reads, but its swamped by savings from your Dictionary jggery-pokery, it depends on the data type:
Value: Number or Text 1.6 to 1.7 sec Currency 1.8 to 2 sec; Date 2 sec
Value2: Number or Text 1.5 sec; Currency 1.5 sec; Date 1.5 sec
And Yep. On the matter of the Set to Nothing, obviously its a personal, value choice between equally respectable options. My aim was to just balance the thread so that people can make their own choice based on both arguments. I actually tried removing your error handler and re-adding the first member to the Dictionary to throw an error, just to see what happened and the object was released, according to VBE anyway...
Cheers