Hello Experts,
I have multiple modules in this workbook which are giving me the expected result when I press the button to play the code. The only problem is that this particular module is taking 80% of the total time to display the result. I am hoping that an expert to edit the code wherever required and reduce the time to as minimum as possible.
Thank you.
Last edited by RAJESH SHAH; 06-02-2023 at 09:40 AM. Reason: #Solved by JohnnyL
"Where there is a will there is a way".
What is the difference between this file and the original file? I get this:In the original data the time taken is
K_B2B completed in 194.8125 seconds.
K_B2B completed in 0.6640625 seconds.
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Hello Jeff. This is just a sample data. The original data has around 32,000 rows. As per the status bar, the columns with formulas take the maximum time. The step1 is created by a code which has around 1,50,000 rows takes less time to display the result. just 7 seconds.
But this module it takes K_B2B completed in 315.078125 seconds.
Last edited by RAJESH SHAH; 05-19-2023 at 09:22 AM.
Every time I check a different data the time taken is more in creating B2B sheet. As per the status bar the sumifs formulas in columns K to O is taking the maximum time in each case.
First, 32,000 is not huge!
Post your full size file as there is no reason why the SUMIFS you refer to should be slower than any other of the SUMIFS.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
John Topley. The size of the full file is 154 MB. Is it against the rule if I share the link of the file in private.? Sorry, I misunderstood what you said. Will share the file with the full rows in some time.
Last edited by RAJESH SHAH; 05-19-2023 at 01:35 PM.
No rules broken BUT respondents (myself included) are relulctant to access files other than through the forum.
Can yot restrict file data to "Step1" and "E-invoices" as the file seems very large for 32000 rows.
This is the full file. Please notice the status bar where you will notice which line is taking the most time to evaluate. I have saved the file as xlsb.
When the code is running and as it takes a long time, when I try to work on something else in the system, the code stops running (not responding) and when I stop working and view the sheet with the code by clicking on it it again starts running. (which I hope could be normal not sure).
I "suspect" the issue is that SUMIFS is comparing data on 2 sheets , namely "Step1" data with criteria from "B2B". If this supposition is correct, then it may only be resolved by reading data into arrays (memory), calculating results, and writing results back to "B2B".
See what response(s) you get from other contributors.
Yes. I was hoping that someone could do that. I am not good at all at arrays and setting Scripting.dictionary.
Another approach is place the SUMIFS etc in "Step1" and then use VLOOKUP to retrieve the sata in "B2B".
The attached has this solution which took 45 seconds on my computer vs 135 seconds for the original: better but still too long
So it is likely you will need a VBA coding solution to reduce the time significantly.
Upload of file fails - not sure why as it not a size problem.
Please Login or Register to view this content.
Last edited by JohnTopley; 05-20-2023 at 03:47 AM.
Are you still on XL2007?
I have 2019 but I need to create all my applications compatible to run with older version of excel too.
Hopefully I will come across the solution to reduce the time from someone. 🤞Hopefully someone is already on it. 😜
See commercial section above and demands you can make lol
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
Not demanding, looking for possibilities to create something by not exploiting a person but exploiting his knowledge. I don't do this for remuneration but just to have fun with excel and share my applications and even teach people how to use it for free. The mind is the biggest asset in us. Till we live our lives, better use it and exploit it to the maximum extent rather than keep it idle.
Last edited by RAJESH SHAH; 05-20-2023 at 12:07 PM.
I added code like thisto several strategic places throughout the procedure, then used a watch on TotalTimer to see how long different segments of the code took to execute. I see two major bottlenecks.Please Login or Register to view this content.
1) The part of the code writing the SUMIFS() functions into K:O.
2) The part of the code where Table1 is .Unlist[ed].
On my machine, bottleneck 2 was consistently longer than bottleneck 1.
I was thinking something similar to JohnTopley. Perform the conditional sums outside of (before) the procedure, then simply extract the sums from the original database. I wonder if his solution has adequately handled the conditional sum bottleneck, and what is remaining in his testing is the .unlist bottleneck.
Originally Posted by shg
Yes. The status bar is also displaying the same thing. columns K:O.
Forum does not allow me to upload a file !) so do the following:
in "Step1"
in AA2
=D2&L2
in AB2
=SUMIFS(Step1!$H:$H,Step1!$D:$D,$D2,Step1!$L:$L,$L2)
in AC2
=SUMIFS(Step1!$Q:$Q,Step1!$D:$D,$D2,Step1!$L:$L,$L2)
inAD2
=SUMIFS(Step1!$R:$R,Step1!$D:$D,$D2,Step1!$L:$L,$L2)
in AE2
=SUMIFS(Step1!$S:$SQ,Step1!$D:$D,$D2,Step1!$L:$L,$L2)
in AF2
=SUM(AC2:AE2)
copy down for the 32000 rows and you can copy/paste values
in code K_B2B
Please Login or Register to view this content.
add this code as new macro
run the "Extract B2B" and note times - about 46 seconds on my computer): then comment out the "Call test" and run the "Test" macro separately - it runs MUCH faster than when called from the "K_B2B" module i.e ,<1 seconds vs 14 second on my computerPlease Login or Register to view this content.
no idea why!
Last edited by JohnTopley; 05-21-2023 at 09:15 AM.
Thank you John Topley. Trying it out now.
John Topley. The call test is not making much of a difference. If I understood your steps right this is what I did. I have to add columns from AA to AF and fill them with formulas > Paste special and then add the test code to a module call the test module and run the code.
Original code - K_B2B completed in 185.9453125 seconds.
code after editing - K_B2B completed in 158.91015625 seconds.
If I edit the code of step1 and add the code for the additional column headings and formulas in column AA to AF, the timer of the step1 will increase and in that case again the total time taken will be the same as before.
I think it is possible to reduce the time by changing the code of Extract B2B by converting the data into an array or something which I am not good at.
I have more than 200 formulas in various modules. With some help, I wish, If I could convert them into array the code will display the result in seconds instead of around 4-5 minutes depending on the number of rows in the data.
You must have a very slow processor as your file completed in 43 seconds on mine.
Run the "Test" macro (stand alone) and see the time- on my computer it completes in less than 1 second, which illustrates the improvement that could be made by using VBA array processing BUT when "Test" is called from "K_B2B" it takes 14 seconds and I don't understand why. You got nearly a 30 second improvement if I understand your figures.
You would get considerable improvements using VBA array processing but this a lot of rework. Part of the problem (as I alluded in a post some time ago) is that the solution has been developed incrementally without (again, as far as I can judge) a full analysis of the end-to-end processing so there is much repetitive code/formula..
Once I complete this project, I will try to understand how to convert the data into array. I know it is a long process but a professional can do it in less time. It will take a month for me once I understand at least one array correct. I have this formula converted into array for a single formula. Check the number of lines involved but this helped me in one of my modules to reduce the time from 514 seconds to just 7 seconds.
This is just for one formula.Please Login or Register to view this content.
I have found that "native" array processing can be faster than using scripting dictionary.
To provide a "professional" solution would require a complete specification of your multi-stage processing in order to design out unneccesrary repetition of code and the many reformatting processes that are currently being undertaken.
A task for another day!
Yup. A task for another day. Right now I need to complete this one project where I am once again stuck with something new. Will post it in a new thread tomorrow as it will take a lot of time to create a sample data and explain my requirement.
Your last reply is a perfect example of the lack of a complete specification and hence design.as it will take a lot of time to create a sample data and explain my requirement.
You started with a 4 seater car but now discover you need a 6 seater ........
I am way out of your league or the other members. I am class 1 student trying to understand class 10 lessons. When playing in a bigger league with professionals, you learn a lot just by observing.
I understand : last words ... there have been VBA contributions from a number of us (Sintek, Marc, JohnnyL ....) , each with our style/techniques, so this cannot make it easy for you (or anyone) to readily learn VBA.
Await your new thread
Regarding the new thread, still trying to figure out how to explain. I will need the help of an expert good with arrays, loop, etc., I have the picture in my mind and how it will work with a code, but the difficult part is to explain. If I explain step by step it will take +#100 posts to make someone understand. I wish someone in the group was an expert in reading minds.藍
Await your new thread
If it is so difficult to explain, then you REALLY have a problem!
Stop looking at it from a coding point of view and explain in words what is required from a business perspective.
.......I will need the help of an expert good with arrays, loop, etc
..... that is for respondents to decide.
This might have nothing to do with your problem but perhaps you are the victim of size bloat]?
Also try to hit Ctrl+End (backup first). If that gets you far below your data you have a problem. Select the row under your data, press Ctrl+End, right-click and select "Delete" ( not the "Delete" key on the KB). Check if your file size is smaller
Last edited by Pepe Le Mokko; 05-22-2023 at 10:23 AM.
I have tried to explain to the best of my knowledge and posted the query. I have even written some assumptions about creating an array and variables referring the old codes. If any one can understand them then it is a walk over for them. I have my hopes finger crossed. Let's see.
Have you read post #33?
Pepe Le Mokkp. I had misunderstand the lines. I was asked to share only the sheets connected to this code and I have done so.
I am only astonished at the size of your file ( which could explain slow processing ) and suggested some ways to check it ( after backups of course) and to eventually correct as suggested
When I run the code in 365 in the office it takes less time to display the result. In 2019 it takes more time. If 3 minute in my system then it takes less than 2 minutes in the office system.
I will test and try it out.
I opened one of the sheets, pressed ctrl + end, the cursor moved to the last row in the sheet(Line 1048576). I selected the row under my data, pressed ctrl+shift +end, right clicked and seleted delete, entire row. Again when I pressed ctrl + end from the first row the cursor move to the end of the sheet. So, what really happened I don't know. But I will try with all the other sheets and check the size of the file as you suggested.
After selecting the first empty row and if I press ctrl+end and right click it will delete only one row.
Wow. Only one sheet was moving to the end of the sheet. After doing the steps you suggested the size of the file reduced from 154 MB to 84.3 MB. Amazing and very useful tip. Thanks Pepe Le Mokko.
After selecting the first empty row, I have to press control + shift + End. I hope that is right. The number of bytes too has reduced from 16,22,01,516 to 8,84,28,544 bytes.
Last edited by RAJESH SHAH; 05-22-2023 at 11:20 AM.
Yes it definitely is Ctri+Shift+End, my bad. Does the code work any faster now? ( I know next to nothing about VBA...)
It didn't affect the time but reduced the size of the file drastically.
Ok. Good luck with the rest
Re: Code is very slow with huge data
« If you can’t explain it simply, you don’t understand it well enough … » Albert Einstein
Marc L. I am not talking about this post. This post contains one of the modules of the application which is working and completed. The only problem with this application is it is taking 4-5 minutes to display the final results. I just wanted someone to help me edit the code and get the result in seconds. Sintek has done this earlier in one of my other applications. So, I thought why not this too.
The post I was referring to is posted in a new thread. I was able to understand it well enough but trying to explain and make someone else understand would be difficult. This is what I meant.
Re: Code is very slow with huge data
Whatever the thread …
« What we conceive well is clearly stated, and the words to say it come easily. » Nicolas Boileau
« Often those that criticize others reveal what he himself lacks. » Shannon L. Alder
Re: Code is very slow with huge data
Was just an advise - so again very not a critic - how the initial post must be
as if the subject is not well understood and / or data badly designed few chance to get the 'dream' result …
After looking at the code for B2B, I totally agree with that. Those are the two main bottlenecks for the B2B code. I think that module will need to be rewritten to fix those bottlenecks that account for 80% - 90% of the entire time of the module.I added code like thisto several strategic places throughout the procedure, then used a watch on TotalTimer to see how long different segments of the code took to execute. I see two major bottlenecks.Please Login or Register to view this content.
1) The part of the code writing the SUMIFS() functions into K:O.
2) The part of the code where Table1 is .Unlist[ed].
...
It took more than a month to created this code referring the old codes. I agree that the code is slow just like me. 😝 At least, till it is working and giving me the expected result which is more important, I will continue using this code for now. Like John Topley said "A task for another day".
So are you saying you don't want me to pursuit making the code quicker?
No. I meant was I would not like to re write the code for now. Most of the part I have forgotten the steps. I will have to have a free mind to go through the process once again.
If you have understood the code you are most welcome to try it and I would really appreciate it. How can I not want the code to be quicker....
Well as you stated, the B2B section is the first section that needs help. I will address that as soon as I get some sleep. I have already made changes to make it almost twice as fast, but I am not satisfied with that, like I said, I will look at again after some sleep.
Okies. Good Night.
Lesson here: start by writing (written word NOT code) a requirement specification in (for example) a WORD document of what you require the application to do. This should be a COMPLETE "start to finish" description of the total reqiuirement, rather than the piecemeal approach you have taken which has resulted in a long, and somewhat tortuous prcoess, to get a working application.Most of the part I have forgotten the steps. I will have to have a free mind to go through the process once again.
Comments in code are very helpful but do NOT replace a well-written specification which greatly assists in designing and achieving proficient code.
Last edited by JohnTopley; 06-01-2023 at 03:32 AM.
Thanks John Topley. That is what I have been doing the whole day. I have written my requirement from start to finish in excel sheet itself. This time I have not written in pieces. Just going through the notes once more to see if I have missed anything. Will post tonight after reviewing the whole exercise once again.
(Posted in a new thread to get the values - I was unable to get the values of 2 columns with a formula but how to get it with a code I have written the details)
This code should work much faster for the K_B2B subroutine.
FYI: run time of 38 seconds vs 43 seconds on earlier code (posts #20-24) so faster (but much faster?)
Yes. The new code is much faster comparitively.
The old code took K_B2B completed in 193.9326171875 seconds.
The new code timed K_B2B completed in 44.796875 seconds.
Thanks JohnnyL.
Yup ... much faster!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks