LinkBack URL
About LinkBacks
My opening macro seems to have a lengthy delay compared to when I step thru it using F8. Any suggestions what one could look for causing this? Thanks.
My opening macro seems to have a lengthy delay compared to when I step thru it using F8. Any suggestions what one could look for causing this? Thanks.
Probably delay is not really due to the macro. It's probably just file opening. Of course not seeing file nor macro it's only guessing.
Best Regards,
Kaper
Thanks Kaper. Ya, I know it's difficult to answer without seeing the code, but the workbook is way too big to put on this forum. The file is actually open, all the macro does is display hidden worksheets, then hides two userforms to display the 10 or so worksheets as well as a new userform. I can probably F8 thru it faster than when it runs itself.
Is there a way to watch the macro run through the code other than manually with F8?
Hi thecdnmole
You can insert "Breakpoints" at various key lines of the Code and get a visual representation of those lines that are causing the issue.
F5 between "Breakpoints" should show you what's causing the issue.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Thanks jaslake for the suggestion, but the problem is I don't know where the issue is! If I put a line break at the start and step thru it, there is no problem, but when the macro runs, it seems to hang somewhere. But where??
Hi thecdnmole
If you wish, upload the offending File...one of us will look at it.
I would if I could, but the file is 10kb in size, too big for the forum and has sensitive material I would prefer the world not see. I realize that is the best way to get a good answer from you pros out there!Originally Posted by jaslake
Hi thecdnmole
My advice...
Create a file with less Data
Desensitize the File
Zip it if required
Upload it
One of us will try to assist.
I will see if I can as it will entail a lot of work and not sure if it will work once I do.
I just tried something, there are two userforms that appear and in the background there is about 10 worksheets being made visible. Once this happens the userforms are hidden, which the whole time from start to finish takes 36 seconds. I tried it again, but this time I unloaded the two userforms and it took 26 seconds. Does that make sense?
Hi thecdnmole
No clue...Zip the File...upload...
Well, let's take another try without a file.
in the code at the begining:then in some (few in first attempt) characteristic points insert lines like:
Please Login or Register to view this content.if something is done in a short loop which you suspect then in a loop (say control variable is i) something like:Please Login or Register to view this content.and at the end eitherPlease Login or Register to view this content.
orPlease Login or Register to view this content.or write s into some cell in your workbookPlease Login or Register to view this content.
run it and step through and compare results.
next stage you can increase density of such lines where difference occured (large one - small are normal).
Good luck!
Thanks Kaper! This helps, the little bit I've tried it does not appear the userforms are slowing things down, so that is good to know! Greatly appreciated. Still not sure if I will post spreadsheet as there would be some desensitizing to do, then after that I am not sure it would work properly.
Interesting, looks like by using vba to write formula to cells uses up a lot of time, 5.7 seconds! Is there a more effecient way to do this then? These same sheets below are used in another set of forms and the only change would be from TC331 to TC51. I am thinking just using a number or letter in a cell and an "IF" statement in "Y1", =if(AA1=1,"='Facilities(TC331)'!X1,='Facilities(TC51)'!X1)
Please Login or Register to view this content.
Last edited by thecdnmole; 03-29-2015 at 08:15 AM.
If it's really point 5 and 6 result then it's a loooong time
Switch off automatic calculation before and restore after
probably you can swith on Manual just at the beginning of macro and restore automatic near the end.Please Login or Register to view this content.
If recalculation is required on a way just force it for instance
As we touched this type of problems - do you have screenupdating set to false? it also slows down the execution.Please Login or Register to view this content.
Thanks again Kaper! Yes, I have screenupdating set to false. Where do I put your last suggestion, before or after the line I want to calculate?
Before the line where you want your spreadsheet recalculated.
So for instance before some .find or vlookup etc. or if nothing like that occurs - just before end sub/exit sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks