Slow "comparison/replace" script

CoolBlue · #1 06-17-2014, 06:36 AM

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

whatsup · #2 06-17-2014, 08:23 AM

Quote:

..just to see what happened and the object was released, according to VBE anyway...

CoolBlue, how did you do that, to check whether memory is freed or not? I tried to come up with an example, but so far I can't think of a method to access memory to look up if the key is removed or not.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Wierd "script code" in a downloaded .doc file	CNBarnes	Word	2	10-18-2012 02:07 AM
replace data from variable with "sub and super script" from excel to word by vba	krishnaoptif	Word VBA	9	06-22-2012 05:08 AM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM
Rules and Alerts: "run a script"?	discountvc	Outlook	0	06-15-2010 07:36 AM
An "error has occurred in the script on this page"	decann	Outlook	8	09-03-2009 08:54 AM