Slow "comparison/replace" script

whatsup · #10 06-16-2014, 08:55 PM

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 ...

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