How To Speed up macro

[AnkitGuru]

Hi Guys!

I've created a vba macro that formates the excel sheets in some defined format.
It runs quite smoothly. But then as soon as I shift the focus to some other application
the performance drastically reduces. Normal run time of 30 minutes is extended to over
2 hours. The application is quite optimized so no need to discuss about "application.screenUpdating"
and all. Please help me getting over this issue.
The problem can be of "thread priorities" (just a guess).

 

[jaybee3]

Make sure Excel keeps focus as performance on anything running in the background is exponentially slower?

Is there a reason why something else would take focus other than the user clicking elsewhere while it's running?

AKAIK, there is no way to change priorities for Excel within the code.

 

[number kruncher]

If you are opening / using other Applications, then maybe it's your PC that you need to look at?

Can you streamline your PC resources, or improve the hardware?

HTH

P.S. Welcome to the board.

 

[Michael M]

Maybe you could post the code you are using.
It might be that, rather than the PC.
Having said that though, the comments by jaybee3 are fairly relevent, why should the user be clicking soemewhere else....unless of course they were getting sick of waiting !

 

[AnkitGuru]

@jaybee3 you are quite right about the priorities of background processes.
@number kruncher What I'm using is a quad core processor (Intel i3) with 4 GB RAM so hardware should not be an issue.

Also we can manually assign processors to provide max priorities to programs or background processes. Default is programs that is why this problem is occurring, for that I need a solution, if i can modify my code somehow.

Here in my case I'm handling a recursive program working on multiple worksheets > 50
having > 15000 lines.Can't post code because of restrictions.Anyway you guys should not need code since this problem is related to logic rather than syntax manipulation.
Thanx for you quick response. But the problem is still not solved

 

[AnkitGuru]

Also forgot to mention one more thing,
User should not be sitting idle while this macro is running
he has to indulge in some other activities that's why the
problem of shifting focus arise.

 

[number kruncher]

If you can get your 'user' to let the macro run for 30 mins, this may be more efficient than to have them work simultaneously on other applications, which extends the delay to > 2 hours, per your original post.

Or, have a dedicted pc run these 'long' macros?

Better of two evils??

 

[Michael M]

Anyway you guys should not need code since this problem is related to logic rather than syntax manipulation.

How do you know that ??
Logic is dictated by syntax !

 

[jaybee3]

Or, have a dedicted pc run these 'long' macros?

That's what we do, there's always a spare machine lying around.

 

[number kruncher]

That's what we do, there's always a spare machine lying around.

But then as soon as I shift the focus to some other application
the performance drastically reduces. Normal run time of 30 minutes is extended to over
2 hours.

These two quotes are in direct contradiction.

If you have a dedicated PC do these long tasks, where is the problem?