Slow "comparison/replace" script

CoolBlue · #17 06-19-2014, 01:55 AM

Quote:

Originally Posted by whatsup

Interesting:
Features: switch Set To Nothing = False, switch Throw Error = True

Code:

23:25:33:0697 Call RangeCompare2: Don't Set to Nothing:     WorkingSetSize  60.395.520  ( Reference )
23:25:33:0707 Start Timer:                 t = 0.000 sec:   WorkingSetSize  60.403.712  ( 8.192 )
23:25:33:0717 arrCompare Loaded:           t = 0.008 sec:   WorkingSetSize  61.366.272  ( 970.752 )
23:25:33:0747 objDic Loaded:                                WorkingSetSize  61.693.952  ( 1.298.432 )
 ERROR: 450: Wrong number of arguments or invalid property assignment
23:25:33:0747 after End Sub:                                WorkingSetSize  60.719.104  ( 323.584 )

The difference between Start and End (323.584) remains out of 40 runs 30 times the same, equals once, and for the rest it differs just slightly +/-
What can we make of this?

CoolBlue, I enjoy playing with your tool

Well... it's a cool tool!
but not cool enough...

To be honest, its not really any different from if the throw error switch is false. This is because the error is "handled".
The only difference is that it skips the search and the dump to the sheet and a few reports along the way.
I wasn't really happy with that part of the model so I fixed it...

I've attached a new version that throws an error that is not handled. You can crash out by selecting END in the Error Dialog and then just click anywhere other than the active cell to trigger logging the post-exception memory state. I had to fumble around a bit to get it working coz it needs user intervention to complete the process, but it gives a true windows view of the memory after crashing out of VBA.

I hear you about the memory requirements of XL 2013, it is much hungrier that XL 2010. I checked as well...
All I can say is you don't get to be that pretty without working at it!
Pity about the bugs though...

I've attached a pic showing performance graphs from process explorer of the dictionary spreadsheet in XL 2010. I inflated the memory requirements for the Dictionary to highlight whats going on. I did this by using long strings (instead of "") for the Keys. I just kept running the process over and over to see what happens. Its pretty clear that the memory is being released according to process explorer.

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