Scripting Dictionary

You can search more detail about it through Google.com or go there.

very nice and interesting article <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 8.25pt" type="#_x0000_t75" href="http://msdn.microsoft.com/workshop/management/planning/msdnchronicles2.asp" target="newwindow" alt="External Link" o:button="t"><v:imagedata src="file:///C:\DOCUME~1\AYAZ~1.AKH\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif" o:href="http://www.kamath.com/images/leave_site.gif"></v:imagedata></v:shape>
Dictionary Object<o></o>
Object that stores data key, item pairs.<o></o>
<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=Picture_x0020_1 style="VISIBILITY: visible; WIDTH: 0.75pt; HEIGHT: 0.75pt" type="#_x0000_t75" alt="http://i.msdn.microsoft.com/Global/Images/clear.gif" o:spid="_x0000_i1025"><v:imagedata o:title="clear" src="file:///C:\DOCUME~1\AYAZ~1.AKH\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape> Remarks <o></o>
A Dictionary object is the equivalent of a PERL associative array. Items can be any form of data, and are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.<o></o>
What is it and why use it?
Visual Basic and VB Script were never any good working with large arrays and lookup tables. So the Microsoft ASP team decided to create a component that will be the equivalent to the Perl hash, or associative array. It first came out in 1996 as part of VB Script 2 and was added to the VB Scripting run-time library (scrrun.dll) to enable VBScript programmers to use associative arrays. Now you can develop in Visual Basic or VB Script just as you would in JScript and Perl. For more information read this very nice and interesting article <v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_i1025 style="WIDTH: 12pt; HEIGHT: 8.25pt" type="#_x0000_t75" href="http://msdn.microsoft.com/workshop/management/planning/msdnchronicles2.asp" target="newwindow" alt="External Link" o:button="t"><v:imagedata src="file:///C:\DOCUME~1\AYAZ~1.AKH\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.gif" o:href="http://www.kamath.com/images/leave_site.gif"></v:imagedata></v:shape>
OK, but what is it actually used for?
I was given a problem, not too long ago, that required me to compare two very large sets of data. They were both over 300,000 lines.
No problem, I said, give me a couple of minutes, and I will then give you the differences between the two lists. The data I needed to compare was a simple one, a key and a value. I was required to first report of any keys found in one list and not in the other, and if the key was there, I then had to make sure that the values were also the same.
I quickly created a multidimensional array for each list, populated the arrays, which took a while, but then adding 300,000 values into a multidimensional is not a quick process, and started the comparison. Needless to say, it didn’t return me any results – my machine died on me before any results were displayed.
I went back and said that it will take me a little longer, and that I needed more memory, when I remembered about the dictionary object. This method was not only quicker, but also easier (for me that is) to use, and I’m not talking about the MS Word dictionary here.
A dictionary holds two sets of information, a unique key and a value associated with the key, so we use the key to retrieve the item. The key can be anything except a variant, but it’s should usually be an integer or a string. The item can be of any type.

tony567 said:

When we should use scripting dictionary?

Click to expand...

I'd say that your use of "should" would be better termed as "could". Different programmers have different styles and there are so many circumstances with a development project that a "should" for a scripting dictionary is just not an unambiguously definable set of circumstances. Personally I almost never use a scripting dictionary, because it has never made sense for me to programmatically create an object whose purpose is to be eliminated. There are better methods to accomplish the same task. But that's me, you'll find others who disagree and say it works for them, and so the world goes...some prefer vanilla ice cream and others prefer chocolate.

Thank you Ayaz, i just wanna know about others ref. in here.
Tom, would you like to explain about:
1. "because it has never made sense for me to programmatically create an object whose purpose is to be eliminated", i dont know about object and eliminate what, keys or item or what
2. There are better methods to accomplish the same task, would you like to mention that task, 1 example is more than enough.

tony567 said:

Tom, would you like to explain about:
1. "because it has never made sense for me to programmatically create an object whose purpose is to be eliminated", i dont know about object and eliminate what, keys or item or what
2. There are better methods to accomplish the same task, would you like to mention that task, 1 example is more than enough.

Click to expand...

See this link, especially the last 3 posts (#7, #8, and #9) as an example you asked for, regarding a discussion I had on this topic 4 years ago. Notice the length of code using scripting dictionary versus the two alternatives I offered that were much less memory-intensive, and the person who used scripting dictionary said in that final post that it was "not adequate" to use the dictionary object in that case, which was my point to begin with.
http://www.mrexcel.com/forum/showthread.php?t=191447

The object "scripting dictionary" is not always neccessary,but always useful and quickly.
An example:

Run the following codes to generate 3*65536 numbers in column A:C

Then write codes to find numbers appear in all 3 columns and write them to column D.

I'll show you a fast code with "scripting dictionary" later.

Best Regards
Northwolves

OK Northwolves,

Something like this But of course I'd be interested to see your fast one. How fast?

I think I could do one of at least comparable speed using an approach based on sort, but I tend to generally prefer the Scripting Dictionary.

It's a useful Excel tool which I don't think Tom has so far adduced good reasons for not using.

Some useful links http://www.mrexcel.com/forum/showthread.php?t=226576

rugila said:

It's a useful Excel tool which I don't think Tom has so far adduced good reasons for not using.

Click to expand...

Did you read the posts on that link I provided...even the user of the scripting dictionary alternative said it was not an adequate application in that example.

rugila said:

OK Northwolves,

Something like this

Code:

Sub sameinthreecols()
t = Timer
Dim a, n, i, j, c(), p
a = Cells(1).CurrentRegion
n = UBound(a, 1)
ReDim c(1 To n, 1 To 1)
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To n: .Item(a(i, 1)) = 1: Next i
For j = 2 To 3
    For i = 1 To n
        If .Exists(a(i, j)) Then
            If .Item(a(i, j)) = j - 1 Then
                .Item(a(i, j)) = j
                If j = 3 Then
                    p = p + 1
                    c(p, 1) = a(i, j)
                End If
            End If
        End If
    Next i
Next j
If p > 0 Then Cells(1, "d").Resize(p, 1) = c
End With
MsgBox "code took " & Format(Timer - t, "0.00") & " secs"
End Sub

But of course I'd be interested to see your fast one. How fast?

I think I could do one of at least comparable speed using an approach based on sort, but I tend to generally prefer the Scripting Dictionary.

It's a useful Excel tool which I don't think Tom has so far adduced good reasons for not using.

Click to expand...

Thanks for your reply.Test the following codes,please.

Regards
Northwolves