Scripting Dictionary

[tony567]

When we should use scripting dictionary?

Any website recommendation on the net with a good explanation about this, related to use it in excel?

Thank you very much

 

[Legacy 14611]

Hi Tom,

Yes I did have a look at that link, and checked out the codes you posted, apparently based on Find.

Yes, the then dictionary object user did concede that he/she had used the wrong tool in the wrong way for that particular problem. It doesn't imply the dictionary tool is universally not useful for other, maybe many other, problems, or even if used somewhat differently could have been OK for that one.

You note above that "it has never made sense for me to programmatically create an object whose purpose is to be eliminated". I think that object can serve useful purposes during it's brief existence, but like anything else may be disposed of when it's time has passed. Don't many parts of a program - definition of ranges, screen updatings turned off, etc terminate when the program completes?

Would you on similar grounds raise objections to the use of catalysts in chemical reactions, or closer to here, to the use and subsequent elimination of helper columns which may disturb data a lot more than does the scripting dictionary?

You referred to one thread to expand on your viewpoint. I was involved in a couple of quite
interesting ones myself more recently
http://www.mrexcel.com/forum/showthread.php?t=428654
and http://www.mrexcel.com/forum/showthread.php?t=424524&page=1

These threads involved quite large datasets, and it interested me that all suggested approaches other than scripting dictionary dropped out pretty quickly. If there were better approaches, I have to be curious about why they weren't forthcoming.

Coming back to the current thread, Northwolves posed a good problem and has given an excellent, very fast, solution, using the scripting dictionary. I'm right now very curious to see if there are better approaches available, as some of your comment may seem to imply ...

 

[Legacy 98055]

<i>"When we should use scripting dictionary?"</i>

Whenever the application you are creating is not responsible for national security.

My lack of education and resulting simplistic view. If it works, use it.
You can download the helpfile <A HREF="http://www.microsoft.com/downloads/details.aspx?FamilyId=01592C48-207D-4BE1-8A76-1C4099D7BBB9&displaylang=en" TARGET="_blank">here</A>. I don't quibble over milliseconds but sort of move on. If you early bind this object, it performs very well and is useful in a multitude of scenarios where the collection object fails. Just my humble opinion.

 

[tony567]

Thank you guys for your suggestion at this topic.
Highly Appreciate!

 

[tony567]

"When we should use scripting dictionary?"

Whenever the application you are creating is not responsible for national security.

Is it a joke?!...

 

[Legacy 98055]

An attempted joke...

Translation: It's just fine to use the scripting objects pretty much anytime it's convenient for you.

How's dat?

 

[Legacy 14611]

from Northwolves: Thanks for your reply.Test the following codes,please.

Yes. Interesting code. Also interesting that no other comments on it. So here's a couple.

1.What happens if your random numbers go up to 10^7 rather than 10^6?
2. Does it give a correct result if column length is less than 65536? (if you like I'll give an example where it doesn't give correct result)
3. Does it have any speed advantage (disadvantage?) over a variety of other methods (e.g. the one I gave) if the column length is around 5000 or so? Maybe a lot of users have rows not generally more than this.
4. Interesting (unusual?) in that the larger the number of rows in the problem, the faster your macro seems to operate.

 

[tony567]

still relating with scripting dictionary,

in reporting from excel database table resources, which is generate faster report, scripting dictionary or vba code with the help of pivot table

thank you

 

[Legacy 14611]

in reporting from excel database table resources, which is generate faster report, scripting dictionary or vba code with the help of pivot table

That's hard to say in general because they don't, and aren't designed to, do the same thing.

Generally speaking, the scripting dictionary plus additional code can do pretty much everything a pivot table can do, but may not do it more efficiently. For a couple of very simple examples, check out these:
http://www.mrexcel.com/forum/showthread.php?t=424937
http://www.mrexcel.com/forum/showthread.php?t=425265
But very generally, if you're looking for speed and comparing them on the same problem, I would guess that for less than about 12,000 rows the pivot table could be the faster. With a greater number of rows, and more than about 12 columns, the scripting dictionary could well be faster.

In my experience the scripting dictionary stays robust even with very large problems, whereas with such problems some of the prepackaged Excel routines such as pivot table and advanced filter may lose their edge or even be difficult to get to work at all.