PDA

View Full Version : [SOLVED] Macros take longer than usual



Daxton A.
02-17-2005, 11:37 AM
About a year ago I had medical problems and had to leave my job for a while. When I left, Office was running fine. But when I came back, my macros take forever to run and I don't know why. I mean it took about 15 seconds for my biggest macro to run through its process. Now it takes a couple of minutes to run through its process. I did notice that the biggest problem that occurs is happening when it checks to see your active cells when it switches pages. I don't know if that has anything to do with my question either. Does anyone know why this happens?

Thank You!

Zack Barresse
02-17-2005, 11:46 AM
Hi Daxton!

Selecting anything in a macro will drastically reduce the efficiency and time it takes for your routine(s) to complete. Same goes for Activating things. For any further evaluation, we'd really need to see your code or an example workbook.

Also, is it like this on multiple computers? Or is it only slow on one computer?

TonyJollans
02-17-2005, 01:01 PM
After a year your computer just got old and tired :cloud9:

Seriously, there are far too many possible variables and a lot can happen in a year. Are you working on a network? Have you had any upgrades to any software? etc., etc.

Daxton A.
02-17-2005, 01:29 PM
I'm pretty sure that I have deleted all the company info and I deleted just about all the sheets so that the file is smaller and renamed the sheets by the alphabet.

Now I did notice that switching sheets, w/o running a macro, took a little while to do. The problem is when I switch sheets it takes forever to do the built in "Calc Cells" formula. The problem is not in my macros. And I am running on a network and the only PC that I have been able to try it on elsewhere is doing the same thing. But I'm pretty sure that the problem does not lie w/in the network b/c it does the same thing when the file is on my local machine.

Daxton A.
02-17-2005, 02:25 PM
notice that it only took a while when I opened up the third page: "C". But it happens on a lot more sheets than the one that I have in that workbook. But again, I am trying to figure out why that happens?:motz2:


Does it happen on your PC's?

johnske
02-17-2005, 03:28 PM
notice that it only took a while when I opened up the third page: "C". But it happens on a lot more sheets than the one that I have in that workbook. But again, I am trying to figure out why that happens?:motz2:


Does it happen on your PC's?


Hi Daxton,

Yes, page C (and another) took longer than the others to open. I also noticed the file size was 1.36MB when I opened it, which seems way too large for the actual contents.

Using this code on the active sheet reduced it to 283kB, if there is nothing below row 83 on the original, run it on the original (all sheets) and see if that's any better:


Sub CleanUp()
Range("A83:IV65536").Delete
End Sub

Caveat: This will delete all the empty rows and Excel will automatically re-generate new rows to replace them, however, Excel only has the capacity to regenerate like this a limited number of times (I don't know exactly how many, but I found it's somewhere between 50 to 100 times or so) - after that it effectively "kills" the sheet...

HTH,
John

Zack Barresse
02-17-2005, 03:34 PM
DRJ has a KB entry for reducing the Excel file size by the same method Johnske explains ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

It is dynamic though. It is basically like the ASAP Utilities function except that it will loop through all sheets in the workbook instead of only doing the activesheet.


Caveat: This will delete all the empty rows and Excel will automatically re-generate new rows to replace them, however, Excel only has the capacity to regenerate like this a limited number of times (I don't know exactly how many, but I found it's somewhere between 50 to 100 times or so) - after that it effectively "kills" the sheet...

John, are you talking about running a procedure like you mentioned? If so, how does that "kill" the sheet? I have never heard or experienced this.

johnske
02-17-2005, 03:51 PM
John, are you talking about running a procedure like you mentioned? If so, how does that "kill" the sheet? I have never heard or experienced this.

Hi Zack,

I had a program involving multiple copy & pastes to one sheet (thousands of rows below row 200) where calculations were to be performed. After finishing with the data I'd copied and pasted the pasted data then had to be cleared for the next operation, I thought a very fast & simple way to do this was to simply delete all.

After testing extensively I noticed that everything was still working fine EXCEPT after some time it had abruptly slowed down quite dramatically. After checking, I found that all the rows below 200 had become a light gray colour. I replaced the sheet and did it again - after a while - same thing.

I then replaced the sheet and replaced the delete rows with clear contents, result - its still going fast and strong after many hundreds of repetitions :devil:

Zack Barresse
02-17-2005, 04:08 PM
Hmm, strange. I am unable to duplicate this behavior. Perhaps you have a sample file you can upload? But then again, there is nothing saying that if I can't see it now, I won't be able to see it on an uploaded file. The only thing I could do that even slightly resembled what you described is to hide the rows (say from 200:65536).

johnske
02-17-2005, 04:29 PM
Hmm, strange. I am unable to duplicate this behavior. Perhaps you have a sample file you can upload? But then again, there is nothing saying that if I can't see it now, I won't be able to see it on an uploaded file. The only thing I could do that even slightly resembled what you described is to hide the rows (say from 200:65536).


As I said, it only did it after MANY repetitions. Did you try that in a loop? With a timer to check calculation of operations after deleting? As the first thing I noticed (because rows 200 down were all unseen anyway [no - not hidden, you just had to scroll way down to see them]) was the slow-down - the greying-out could be due to something else again, but I did notice it wasn't there BEFORE the slowing down - Or, perhaps it's all due to different Excel versions (am using 2000).

It's not really practical to upload because the main book alone is almost 1MB of (almost all) coding and with the sample data included this then goes up to 3.5MB, and, i've changed it all now anyway...

John

Daxton A.
02-18-2005, 09:39 AM
I tried that workbook Diet like you mentioned but it didn't do that much.
it made it only about a Kb smaller. Was it something that I did wrong?

Old size is: 7,264 Mb
New size is: 7,263 Mb

Daxton

TonyJollans
02-18-2005, 10:04 AM
I just looked at the posted workbook and everything seemed instantaneous to me (Excel 2003, Win XP SP1, 512M RAM)

Daxton A.
02-18-2005, 12:35 PM
I myself am running Office 2000. And there is something that I left out w/this problem I'm having. My co-workers mentioned that this problem seemed to start happening after they had a virus infection. It just may be coincidence but I don't really know if re-installing Office fixes the problem. I have also been told not to install or re-install programs on the PC's...lol. So I guess that I'm up the creek without a paddle. But someone had mentioned earlier that it was slow when they selected a sheet. I was just seeing if there was a way to make it faster. I mean it literally takes about 4 or 5 minutes to just add a new customer because it has to go to each sheet in the macro and turn off the protection. Then it has to do it again at the end of the macro to protect them back. And this is something that has to be in there is the sad part. But, it only took a few secs to complete the macro when I left. And it doesn't take forever on every sheet, just certain ones. And all of the sheets or copied from the "Dummy Sheet". That is the name of the sheet by the way.

Daxton :dunno

johnske
02-18-2005, 04:00 PM
Hi Daxton,

It seems like a lot has been happening while you were away and I bet a lot of what should have been happening - didn't.

Have a look here (http://www.vbaexpress.com/forum/showthread.php?t=1727) first, the next thing to do would be to do a big clean-up and possibly defrag. If you don't already have it, install EasyCleaner from the link I've given, then open it and go to "Unnecessary", check ALL the options there, then click "Find" and let it do it's thing, then select delete all. Then click the "Registery" button, "Find" and delete all that's found.

That should improve things a bit and a defrag would probably help even more.

Regards,
John

johnske
02-21-2005, 06:59 PM
Hi Daxton,

Did this solve your problem? (as Tony mentioned that it opened instantaneously on his machine, it appeared to me to be a PC-related issue).

If it didn't, don't worry, it's not quite the end of the line yet - the next step would be to do a full surface scan of your hard-drive for bad sectors that may've corrupted files and then do a complete check of system files and replace any corrupted ones :devil:

If it did (solve the problem), did you know you can mark your own threads solved? - go to "Thread tools" above...mark solved...perform action.

Regards,
John :)

Daxton A.
03-02-2005, 10:11 AM
I clicked on the link that you posted John. It came up as an invalid link.

Zack Barresse
03-02-2005, 10:24 AM
John may have been referring you here: http://personal.inet.fi/business/toniarts/ecleane.htm

johnske
03-02-2005, 02:49 PM
Hi Daxton,

OK Yes, i see it's been removed now, it's now an article under PC Tips and Tricks http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=12
what Zack gave you above is from part of it....

John

Daxton A.
03-06-2005, 08:16 PM
So much John, I did the things that that page told me too and it works nice now. I guess that it was just by chance that the other PC I tried the spreadsheet on needed fixing as well.

Thank You Again!

Daxton

johnske
03-06-2005, 08:57 PM
Not a prob. Daxton, just glad to see you've got it working ok :thumb