Code run time differing

[Noz2k]

I have a bit of code which calls several different procedures. The only thing is that it is differing quite considerably in the amount of time it takes to execute. Anything from 8 seconds to 1 minute.

What reason/s could there be for this happening? Occasionally it will also cause Excel to not respond for a period (obviously this is when the run time is longest).

 

[Sektor]

1. Insert Application.ScreenUpdating = False at the top of procedure.
2. Avoid selecting.

 

[Noz2k]

I don't think that I use .select in any of the procedures, and Application.ScreenUpdating is set as False at the top of the code which calls all the procedures (and back to True at the end).

Would I need to put this line in at the top of each sub procedure that is called?

 

[Sektor]

If you call some subs inside "master" sub, then there's no need to insert it into those called subs.

 

[CharlesChuckieCharles]

I have a bit of code which calls several different procedures. The only thing is that it is differing quite considerably in the amount of time it takes to execute. Anything from 8 seconds to 1 minute.

What reason/s could there be for this happening? Occasionally it will also cause Excel to not respond for a period (obviously this is when the run time is longest).

Is your workbook on a network location?

if you transfer the spreadsheet on to a local drive (C ( along with any files it might use if possible ) does it speed up?


Are you running windows 7 ?

 

[Noz2k]

Yes I am using Windows 7, and yes it is on a network location. It will always be used on a netwok location, as I'm not building it for my own use.

Would it just be the network that slows it down then?

It seems to be slower when I first run the code on opening, and then get quicker the more I run it, this is even the case when I double the amount of sheets it has to look through (which effectively almost doubles the amount of loops the sub procedures have to go through)

 

[CharlesChuckieCharles]

But the Point of doing the local test is to assure yourself that the network is the problem not the Code/data

 

[Noz2k]

I did save to my desktop and then try on there, seemed to take a similar amount of time.

But the code runs sql at points which utilises the database and therefore the network, so I can't run it in complete isolation to the network

 

[CharlesChuckieCharles]

So yes it sounds as if the network load, and possibly the Database load as well, which is causing variation in execution times.

There maybe way's to change the type of Database retrieval used, as in switch to snapshot data ( can't remember the SQL server term of temporary local datasets )

 

[Noz2k]

Yeah, it's not too big a problem if it takes a minute to run (especially if it's not all the time). The only annoyance really is the fact that it comes up excel is not responding sometimes, and the screen messes up if you click the mouse.

It still gets there in the end, I just know that it will frustrate a lot of the users though.