how to get the same result faster? please see VBA file.

[Flora20]

please see attached file

https://1drv.ms/x/s!AlfEVNV8SKm1dzv_eCrlCJVyLHc

currently, it takes more than 20 seconds to complete a simple calculation.

i am sure that there have to be a better and faster way to get the same result. but i wish i had that big brain like you experts.

any help is appreciated.

Please Login or Register  to view this content.

also posted here

[MrShorty]

It looks an awful lot like a pivot table type of problem, and pivot tables are generally much faster than VBA or excel functions. My first suggestion would be to try using a pivot table: http://www.excel-easy.com/data-analy...ot-tables.html

[CK76]

Yep. Pivot table is the way to go for this.

I'd add helper column to source table for easy filtering.
=(LEFT(C2,1)+0>=5)*(LEFT(C2,1)+0<=7)

Then add Month to rows field (filter out 111).
Add years to columns field (filter out 2009).
Add Amount to values field and set it to Sum of.

Then add helper column to Filters field and select 1.

To make it dynamic when new data is added to data. You can either convert it to Excel Table or create dynamic named range and use that as source.

See attached.

[Zer0Cool]

There are a few things you might do to speed things up just at a glance. I cant/wont check the file from the link you provided.

Updating the status bar can slow things down. Especially when the command is in a loop and executed every iteration. Furthermore, using the value in a cell within that is likely slower then using the value stored in a variable. Its often good practice to use a counter/if to only update the status bar every x iterations.

Using range.end as the bounds of a For loop, super slow as the end get calculated each iteration. You are better off calculating the end prior to the loop and storing it in a variable, then using that variable as the bounds of the For.

Like is also very slow. The less literal your comparison (or conversely the more vague it is) the slower it is to evaluate.

You may also consider adding application.screenupdating = false/true to your procedure. False before you do anything on sheet and true at the end of the procedure. This lets Excel focus on the final result instead of devoting resources to "showing its work" as it goes. This is a frequent tool in helping to speed up code run time. You could also consider toggling workbook calculation to manual and back to automatic in a similar fashion if you have many formulas.

You also have a very long If and..and...and...

You may consider using separate nested If's instead to reduce the number of checks performed. The and's keep getting evaluated until they are all tested and true or when the first false is hit, which could be after many trues. For example, checking <> 111 first as a separate if allows skipping all other "and's" if it is 111. You should then order the and's and any unnested prceeding if's in the order of likelyhood. Ie: if 400/500 will be 111, test that first and by itself, thus skipping checking all the other criteria needlessly. On the other hand, check the values least likely to evaluate as false last.

[Flora20]

re: MrShorty
this code is part of a financial model application which i inherited from my predecessor. pivot tables are not an option.
thanks for your advise.


Re:CK76
thanks. my application do not have rooms for helper columns. i wish i could use it, but even trying that did not significantly improve the speed

Re: Zer0Cool
thanks for your wiliness to help someone in need.
i am trying to upload the file here, but there is no button to upload attachment.

this code was written by someone else. i have very limited knowledge of VBA.

i tried to decipher the language and terms you used in your comment, but seems like i am too dumb to realize how to change those loops to something faster.
i was hoping if someone could help me with this.

[Flora20]

why cannot i upload attachement in this forum? i can see that CK76 could upload an attachment, but why cannot i?


i have uploaded it here in DROPBOX

[xladept]

Here's how:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Flora20]

Thanks XLAdept

I did try to upload via the GO Advanced. i can see the icon, but when i click on it, i see nothing but a flickering white square box. i thought it could be result from chrome, so i used internet explorer and then i used microsoft edge, in all of them. i could not attach file. is there any other way i can upload or share the file?

[scottiex]

meanwhile - a really quick attempt working blind (so completely untested) generally based on Zero cools comments - see if it is faster.

Please Login or Register  to view this content.

[Flora20]

@xladept

thanks very much for your kind support.

i am stupit, how could i miss that. now i found the manage attachment icon. I have attached my workbook here.

[Flora20]

scottiex

I tried running your code, but i faced the following error.

Runtime error 1004


Method Range of Object _Global failed


the yellow debugger line stopped here and it refers to Thatrange as empty
Set Thatrange = Range(ShD.Range("a2"), lastrowa)

[scottiex]

Ok I made a change - but still it is pretty slow.
Might need to take it out into an array or something.

[Flora20]

thanks so much scottiex

[xladept]

Hi Flora,

I ran it with your program and it took just 21 seconds

Is the file you sent a shortened version? Even so it's not 20 seconds each

Here's what I ran:

Please Login or Register  to view this content.

Just run TimeIt to compare.

[Flora20]

yes xladept

it is 21 seconds and for a short macro like this to take this much time, it really sucks.

i had seen previously people using scripting dictionary object, or arrays to make the code run faster. unfortunately, i have very basic knowledge of VBA. i am trying hard to learn, but i think making this code faster is beyond the abilities of my small brain.

it is really challenging. i am hopeful someone would be able to help in this forum.

[shg]

You could just use a formula ...

	A	B	C	D	E	F	G	H	I	J	K	L
1		Years
2	Months	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020
3	1	22,734.39	26,733.28	4,923.00	9,027.00	5,879.00	8,181.00	7,502.00	8,533.00	6,667.00	6,526.00	B3: =SUMIFS(Data!$D:$D, Data!$A:$A, Main!B$2, Data!$B:$B, Main!$A3, Data!$C:$C, ">=500000000", Data!$C:$C, "<=799999999")
4	2	18,121.19	32,860.89	4,209.00	7,803.00	7,640.00	8,546.00	5,161.00	11,838.00	8,768.00	5,828.00
5	3	98,419.21	30,198.32	7,485.00	4,718.00	9,709.00	8,408.00	13,102.00	6,688.00	7,396.00	5,812.00
6	4	(133,215.41)	34,055.82	10,204.00	6,036.00	8,357.00	8,922.00	9,973.00	4,186.00	4,554.00	8,334.00
7	5	17,473.62	26,277.62	9,957.00	8,275.00	7,075.00	6,180.00	7,296.00	6,908.00	10,121.00	7,970.00
8	6	20,075.57	31,446.12	9,999.00	6,347.00	3,618.00	8,588.00	10,449.00	8,042.00	8,719.00	5,816.00
9	7	13,886.42	20,393.32	8,201.00	10,881.00	5,667.00	8,475.00	7,921.00	10,506.00	1,720.00	6,962.00
10	8	25,360.54	26,349.26	3,427.00	4,268.00	12,698.00	7,974.00	10,794.00	8,226.00	9,405.00	11,904.00
11	9	89,483.82	28,677.61	5,771.00	5,313.00	10,325.00	9,397.00	8,776.00	6,640.00	8,008.00	7,054.00
12	10	(41,365.22)	28,900.48	7,625.00	9,776.00	8,224.00	8,933.00	5,162.00	10,430.00	1,721.00	7,876.00
13	11	19,312.89	35,730.41	4,746.00	7,480.00	6,058.00	3,952.00	8,741.00	6,584.00	7,045.00	5,905.00
14	12	(220,657.20)	16,903.19	9,731.00	4,647.00	8,084.00	8,233.00	6,907.00	7,352.00	8,145.00	6,276.00

[xladept]

Hi Flora,

You're just accumulating by year and month - correct?

[Flora20]

thanks shg

but formula is not an option for this project.

there are some lines of codes that formula cannot deliver.

for example there parts of the code.

Please Login or Register  to view this content.

[Flora20]

Hi Flora,

You're just accumulating by year and month - correct?

yes xladept . but sometimes the month could not be a number. it could be ROman number or some other text for example XXII

[xladept]

Hi Flora,

Try this (.04)

Please Login or Register  to view this content.

[Flora20]

thanks very much xladept. it is 1000 times faster

i think the element of product column is missing from the code FloraD. because from the product column it should only include any product starting with 5 or 6 or 7
and exclude all other products .

in the original vba code it was handled via this line

Please Login or Register  to view this content.

in addition: this below mentioned part of the code that has important role is not in the FloraD macro. full explanation on what this part of the code does, is here

Please Login or Register  to view this content.

[xladept]

You're welcome and thanks for the rep!

I think since I initialize the Keys that the cl value will always be zero. I'm wondering how to deal with the Roman Numeral possibility and I need to address the product value - I'll take a stab at all that tomorrow - you're welcome to alter the code

[scottiex]

I'd be tempted to just sort and then exit when the number is low... Are you ok with sorting the data?
Not clear on the other issues, but I imagine xladept will be all over it anyway

Please Login or Register  to view this content.

[xladept]

Hi scottiex,

If you've got it then, I'm done with it - have you thought about the Roman Numerals??

Never anything over 799999999???

[Flora20]

scottiex,

data is not sorted. i ran the your code, it does not give the correct result. like xladept mentioned, it does not account for roman numerals, products are not limited to min or max numbers. it is just dependent on the first character from the left.

thanks

[Flora20]

thanks xladept

spent two hours trying to manipulate and see if i could make this part of the code work, but somehow i could not get it.

i tried

Please Login or Register  to view this content.

to incorporate in your code. but i could not establish the for loop into it.

I appreciate your help.

[xlnitwit]

Using range.end as the bounds of a For loop, super slow as the end get calculated each iteration. You are better off calculating the end prior to the loop and storing it in a variable, then using that variable as the bounds of the For.

Just for the record, that is not actually correct. The bounds of the loop are calculated once at the start and that's it.

[kvsrinivasamurthy]

Try this code. It is faster. For the given data it took less than 2 secs.

Assumed Column F:N are free. If it engaged suitably shift columns in the code free columns.

Code;

Please Login or Register  to view this content.

[Flora20]

thank you very much kvsrinivasamurthy.

it is indeed fast. i see that you used advanced filter for this very interesting. my columns are 149 columns and sometimes it expands and collapses, would it be ok if i move these helper columns for advance filter to the end of columns i mean columns XEY ,XEZ, XFA, XFB. would this make it slow again?

[kvsrinivasamurthy]

No. It will not slow down. Selection of columns does not effect the speed. If you find any problem let us discuss.

[Flora20]

thanks kvsrinivasamurthy

[xladept]

Hi Flora,

The only thing wrong with your code was that it wasn't referencing the worksheet array (see red code):

Please Login or Register  to view this content.

[Zer0Cool]

Just for the record, that is not actually correct. The bounds of the loop are calculated once at the start and that's it.

Learn something new every day. You are indeed right. Id still store values/calculations you intend to reuse prior, if for nothing else making it easier to read/evaluate.

[Zer0Cool]

yes xladept . but sometimes the month could not be a number. it could be ROman number or some other text for example XXII

In your sample posted I dont see any month entered as anything other than a number (in data). Is it possible to force the input to be numeric?

By te way, whats with the formula used for the named ranges, it seems awfully complex to just get dynamic ranges. I presume that the purpose of the 4 named ranges is to just get the dynamic range of entries within a given column. If so this can be drastically simplified like:

Please Login or Register  to view this content.

Didnt speed much up but should be easier to work with.

Ill keep looking at the file.

[Zer0Cool]

So I added a simple timer so I could measure the changes.

I did a hand full of dry runs with your code as was with only my timer added. I was averaging on my machine about 10.4 seconds to run.

For me disabling the status bar and/or using screenupdating false had almost no effect (maybe 1/10th a second difference which could be margin of error).

I then copied your code into another module and the only change I made was option explicit and declaring your variables. Average run time dropped to 6.7 seconds, so a 35% increase in speed just declaring your variables!

I am still reviewing to see if the code could be re-written or adjusted for further improvement. I also have a hunch that this can be done with a formula as well.

[Flora20]

Hi Flora,

The only thing wrong with your code was that it wasn't referencing the worksheet array (see red code):

Please Login or Register  to view this content.

thank you xladept. using scripting dictionary is also very interesting. i have to learn a lot and i am very enthusiastic about it.

this part of the code is not handled with your code

Please Login or Register  to view this content.

how would you incorporate this into that? if it takes too much time, then please do not worry about it.

i am curious to learn. "give a man (in my case a woman ) a fish and you feed him for a day; teach a woman to fish and you feed her for a lifetime" :-P

[Flora20]

In your sample posted I dont see any month entered as anything other than a number (in data). Is it possible to force the input to be numeric?

By te way, whats with the formula used for the named ranges, it seems awfully complex to just get dynamic ranges. I presume that the purpose of the 4 named ranges is to just get the dynamic range of entries within a given column. If so this can be drastically simplified like:

Please Login or Register  to view this content.

Didnt speed much up but should be easier to work with.

Ill keep looking at the file.

thanks zer0Cool. the downside of using COUNTA is, if i have a some cells blank in the A column then the dynamic range is messed up

[Flora20]

So I added a simple timer so I could measure the changes.

I did a hand full of dry runs with your code as was with only my timer added. I was averaging on my machine about 10.4 seconds to run.

For me disabling the status bar and/or using screenupdating false had almost no effect (maybe 1/10th a second difference which could be margin of error).

I then copied your code into another module and the only change I made was option explicit and declaring your variables. Average run time dropped to 6.7 seconds, so a 35% increase in speed just declaring your variables!

I am still reviewing to see if the code could be re-written or adjusted for further improvement. I also have a hunch that this can be done with a formula as well.

thank you zer0Cool

[xladept]

You're welcome Flora!

[Zer0Cool]

Heres a formula that gives the same results (starting in B3 on the Main sheet, just fill over and down) if you convert the month and year headers on the sheet to numbers not text. With your sample its basically instant results.

Please Login or Register  to view this content.

If you really gotta stick to a macro, you could just fill the cells in main with the results of this formula...would likely be super quick. It would of course not be the "proper" way to code it...but I am not sure there is any code that could be faster than the formula in this circumstance.

You could of course update static references to the named ranges

EDIT: by the way its my opinion that the <> 111 bit of your code isnt required as you are chekcing if the month on main = month in data, and main doesnt have 111 as a month, so you can never have 1-12 = 1-12 and also = 111

[Flora20]

thank you Zero0Cool.

the formula would not work for me. i already had a formula shared below a shorter version. but formula cannot do what VBA can.

this part of the VBA code cannot be handled by Formula

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Heres a formula that gives the same results (starting in B3 on the Main sheet, just fill over and down) if you convert the month and year headers on the sheet to numbers not text. With your sample its basically instant results.

Please Login or Register  to view this content.

If you really gotta stick to a macro, you could just fill the cells in main with the results of this formula...would likely be super quick. It would of course not be the "proper" way to code it...but I am not sure there is any code that could be faster than the formula in this circumstance.

You could of course update static references to the named ranges

EDIT: by the way its my opinion that the <> 111 bit of your code isnt required as you are chekcing if the month on main = month in data, and main doesnt have 111 as a month, so you can never have 1-12 = 1-12 and also = 111

[scottiex]

scottiex,
data is not sorted. i ran the your code, it does not give the correct result. like xladept mentioned, it does not account for roman numerals, products are not limited to min or max numbers. it is just dependent on the first character from the left.
thanks

The code is there to sort it. But if you need the current sorting then OK. You could sort it then sort it back the way you wanted if you are clear what your sorting is.
Seemed like you have numbers starting with 1,2,5 and 7 so it would work for that. I suppose you mean you may have numbers starting with 6 or 8 or 9 or with a different number of digits? If not them you would be complicating it for no reason.

I'm not sure where the roman numerals are but ideally you would fix that when you enter it in the sheet, or once at the beginning. cleaning it up in the source data seems like the better place (in that it will then be consistent/logical for other usage if needed).

All that being said seems you have a useable solution now from kvsrinivasamurthy (and maybe others in the posts subsequent)?

[Flora20]

scottiex

thanks.

yes, i have the solution from kvsrinivasamurthy which works perfectly.


xladept, also posted a very interesting code with use of scripting dictionary which almost worked. i will play with the code to see if i could figure out to add the missing part to it.

[xladept]

What's missing???

[Flora20]

What's missing???

this part

Please Login or Register  to view this content.

[Zer0Cool]

thank you Zero0Cool.

the formula would not work for me. i already had a formula shared below a shorter version. but formula cannot do what VBA can.

this part of the VBA code cannot be handled by Formula

Please Login or Register  to view this content.

Please Login or Register  to view this content.

And what is it you feel that block of code does?

I fail to see how the results via the formula and the results via the macro being the same means the formula is lacking somehow. The only way the code above may be meaningful is in a scenario that doesnt happen with your current sample that I am unaware of.

As far as I can tell its redundant code anyhow. You loop your criteria and get your total (lets say 2011 and month 1). For example lets say the result is 1000. This code then stores in mySumReported = 0 + 1000 - 0 (as this cell has yet to have a value in it). Assuming that c1 = 0 (which it is) and mySumReported has a value (it does we just set it) then we store mySumReported in the cell...which is the same as just putting the mySum result in the cell. We then reset mySumReported to 0 and loop again.

If you could point out a result that the macro gives that the formula does not I would love to see it.

O and heres a more simplified version:

Please Login or Register  to view this content.

[Flora20]

Zer0Cool

a detailed explanation of what that part of the macro does on a smaller data set example, is here

[Zer0Cool]

Zer0Cool

a detailed explanation of what that part of the macro does on a smaller data set example, is here

Im gonna try this another way and then I give up. You seem hung up on the how your task gets done and not that it gets done.

Use my formula, use your macro and tell me how the results are different. Unless there is some circumstance that you did not include in the sample, you will get the same results. The formula will take a fraction of a second to update and will update as the data changes while the macro takes 6 seconds with the variables declared to produce the same results and has to be manually run when data changes.

Again, with the sample you provided that block of code you are hung up on is redundant. It adds nothing as far as I can tell to the macro except more work. It basically says if the current cell in my 1st for loop is 0 and my total is some number then store the value (which you could do from mySum instead), otherwise do nothing (which is the same as leaving the cell 0 because it either already is or the total was 0...which is the same as just setting the cell to 0 if the total was 0 again from mySum).

As a bit of background, someplace between 99-100% of the time if you can do what needs to be done with a formula it will be faster than a macro. Formulas are threaded and macros are on a single thread among other things. Macros are meant to, at a basic level, do something that a formula or feature alone either cant do or to repeat it many times which would be time consuming to do manually. They arent a replacement for formulas and features that already take care of the job.

Your macro is basically a multi-condition SUM with some OR logic, which is exactly what the SUMPRODUCT formulas we gave you are handling. I hope this helps

[jindon]

Try

Please Login or Register  to view this content.

[Flora20]

thank you zer0cool

[Flora20]

jindon,
thanks.

you code also looks interesting, although, it does not have the component of

Please Login or Register  to view this content.

this is another thing for me to learn. i have never seen use of System.Collections.ArrayList & System.Collections.SortedList

i googled it and i found limited detail on the microsoft support page on explaining these. do you know where i can see more explanation/details on these two classes?

since i already have a working solution provided by kvsrinivasamurthy in this thread. which uses method of advanced filter to get the result needed. attached is the file.

i don't want other expert's to spend time on this, i can live with the solution by kvsrinivasamurthy using advanced filter.

but i would appreciate if you can lead me to somewhere where i can learn more about the classes you used in your code.

[jindon]

jindon,
thanks.

you code also looks interesting, although, it does not have the component of

Please Login or Register  to view this content.

I didn't look at your code and didn't read the thread, I just wrote the code from your file, before/after.

Re: how to get the same result faster? please see VBA file.

So if it doesn't work, just ignore it...

[Flora20]

thank you jindon

i learned something new from you code. very interesting use of classes.

I appreciate it.

[kvsrinivasamurthy]

Here is one more method without helper columns, which takes less than 1 second.

Code:

Please Login or Register  to view this content.

[Flora20]

thank you kvsrinivasamurthy

your first solution using advanced filter worked well for me, as it had the part mentioned below.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl see the code. Now I Incarporated the remaining portion also.

Please Login or Register  to view this content.

[Flora20]

Thank you very much Kvsrinivasamurthy. works perfectly fine.

[kvsrinivasamurthy]

Ok . Very glad to help.

[xladept]

Does that actually yield different totals?

[xladept]

Hi Flora,

I'm really confused - when I run your code (21 seconds) and my code (.035 seconds), I get the same numbers??

The total of all the numbers in both outputs is 994377??

Please Login or Register  to view this content.

In my program, by the time the logic gets there:
cl.value is zero, mySumReported is zero - so the result is always just mySum which is the value I accumulate

I don't think I'm senile yet???

[Flora20]

xladept

i have recorded 1 minute video and since i could not upload it via attachment in here (mp4 filetype was not acceptable). i upload it here at Vimeo link is https://vimeo.com/245092577

so that i could better explain what Kvsrinivasamurthy's code does and what your code does.

watch for for values in B14 and K14 on the second run of Kvsrinivasamurthy's code when the "Data" sheet value of D11 changes from 319.88 to 419.88

and then the same exercise is done with your code.
Kvsrinivasamurthy's code changes value of K14 after record changes on the second run of macro. while your code changes the B14 instead of K14 when the macro is ran for the second time.

Hi Flora,

I'm really confused - when I run your code (21 seconds) and my code (.035 seconds), I get the same numbers??

The total of all the numbers in both outputs is 994377??

Please Login or Register  to view this content.

In my program, by the time the logic gets there:
cl.value is zero, mySumReported is zero - so the result is always just mySum which is the value I accumulate

I don't think I'm senile yet???

[xladept]

Thanks Flora for going to all that trouble to explain - however the record you changed is a 2011 year record, and it's supposed to change the column B not the K - I may be more confused than ever now

[Flora20]

xladept

sorry for confusing you

does the attached file explain it better?

[xladept]

I keep getting my own numbers - I guess I'm just a stubborn old man - thanks for trying to enlighten me

[Flora20]

thanks xladept

what matters is, that i got my big problem solved in this forum by help of wonderful people like you.

Happy Holidays! December kicked in

[xladept]

Happy Holidays!