Code using dictionary class is too slow- need it work faster if possible.

**strud** · 02-04-2014, 07:11 AM

I have come up with a code using three scripting dictionaries. It draws from data in a worksheet that currently contains upwards of 12,000 rows.

It also inserts a formula (value of result) in a column for each row of this data.

Currently, when I run it excel almost crashes. Eventually it gets there, so it works, but it takes quite a while to work.

I could run each element of the code in separate Subs, but this is far from ideal as a solution.

So I am looking for a way to speed up the current code if possible.

here is the code:

Please Login or Register  to view this content.

**swoop99** · 02-04-2014, 07:20 AM

For a start.....at the top of the macro put:

Please Login or Register  to view this content.

and at the end put

Please Login or Register  to view this content.

**strud** · 02-04-2014, 07:32 AM

Thanks swoop99 but that seemed to slow it down then crash excel!

**nilem** · 02-04-2014, 07:44 AM

Hi Strud
could you upload a file with an example of your original data and the desired result

**Izandol** · 02-04-2014, 08:04 AM

I do not know why you loop through vData twice:

Please Login or Register  to view this content.

**strud** · 02-04-2014, 08:27 AM

Because with the second dic the keys will be different- as they are added when 'Parent' is NOT any of the criteria:

Please Login or Register  to view this content.

**strud** · 02-04-2014, 08:29 AM

Originally Posted by nilem

Hi Strud
could you upload a file with an example of your original data and the desired result

Thanks nilem.

I always try to upload a sample workbook but desensitising the data would take far too long and building a sample wouldn't really adequately show what I'm trying to achieve.

So unfortunately can't do so here.

**Izandol** · 02-04-2014, 08:33 AM

I think you misunderstand. Those criteria are simply the Else clause of the first criteria. If Parent matches one of the criteria add to dic; if it does not, add to dic2. There is no use to loop twice through everything.

**strud** · 02-04-2014, 08:44 AM

Ah I see.

Sorry I missed that part. Thanks Izandol that does speed it up a little bit.

**Izandol** · 02-04-2014, 08:46 AM

I am not sure the code is at fault - if you comment the formula line, is the speed much better?

**strud** · 02-04-2014, 08:48 AM

comment the formula line? Not sure I follow what you mean...

**Izandol** · 02-04-2014, 08:58 AM

Change this part:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

then try the code.

**strud** · 02-04-2014, 10:07 AM

Yeah the speed is much, much better.

Almost instantaneous. So it must be the formula part.

**strud** · 02-04-2014, 10:07 AM

oddly when I put the formula in manually and double click to fill down it works relatively quickly.

Any ideas how I might do it quicker via VBA?

**Izandol** · 02-04-2014, 10:14 AM

I am not certain without a file - are contents of first two dictionaries going into the named ranges referred in the formula?

**strud** · 02-04-2014, 10:33 AM

yeah thats right, the contents of the first two dictionaries go into the named ranges within the formula: 'Multiples_Valid_List' and 'Multiples_Invalid_List'.

**strud** · 02-04-2014, 10:37 AM

Yeah that's right

**Izandol** · 02-04-2014, 11:17 AM

Do you wish to do exact comparison with column H or will values be contained within other text? If it is exact, I think it may be quicker to loop through the column and test each dictionary with Exists.

**strud** · 02-04-2014, 11:25 AM

It is not an exact comparison, I had to use this formula because I am looking for a very distinct type of match:

Please see my earlier post:

http://www.excelforum.com/excel-form...ml#post3564622

**Izandol** · 02-04-2014, 11:31 AM

Will one list be much shorter than the other, or will they be similar number of items each one?

**strud** · 02-04-2014, 11:35 AM

The valid list will always be a lot shorter

**Izandol** · 02-04-2014, 11:44 AM

In this case it may help to short circuit the formula when the valid items are not found - unless they will usually be present?

Please Login or Register  to view this content.

**strud** · 02-04-2014, 11:53 AM

Good thinking!

That does make it a a lot faster. There's still a fair bit of processing time, but excel doesn't start 'Not Responding' and it is definitely quicker.

Thanks for you patience with helping Izandol.

Much appreciated

**strud** · 02-04-2014, 11:54 AM

I guess it is always going to take a certain amount of time- so I think that's probably as streamlined as it may be- so I'll mark it as solved- thanks again

**Izandol** · 02-04-2014, 12:09 PM

You are welcome.

Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Re: Code using dictionary class is too slow- need it work faster if possible.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Running VBA in excel is too slow, how to make it faster?

Bookmarks

Bookmarks

Posting Permissions