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]

They may be in direct contradiction because they're from different people?

 

[AnkitGuru]

These two quotes are in direct contradiction.

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

Only one of them is my quote.

 

[number kruncher]

Apologies, but my point is still valid, is this post about time management, not excel !

 

[AnkitGuru]

Thanks a lot to all of you guys for concern.
I am trying to use a midway by diving the complete task
into small tasks for time being as i have time limitations but will continue to optimize this, will post if i get a solution. I'm leaving he thread open in case I find a solution.

 

[Michael M]

You won't find a solution, unless you tell us what the real problem is !!
Your first post says you have created a macro that takes a long time to run...
Without expanding on this you won't get any help !!

 

[AnkitGuru]

The problem involves merging and formatting a workbook that has more than 50 sheets. Each sheet has more than 2000 lines. For every line I have to merge different no of cells. and provide borders and background formatting. Column resize. You can think of it like a menu tree. I have manage to get a speed of around 10-15 lines per second.
That's why I said I don't require help in programming.
The problem arises as soon as the user who has executed the code tries to open some other file let it be a song or browser etc. When some other application comes to foreground windows generally prioritizes that application as being being normal multitasking activity. But with 4 cores the speed should not be this much low. Now I need a way to bypass that and let the macro run with same priority.
If forcefully and manually I do that it will hamper the execution and efficiency of other application which is unacceptable for obvious reasons. Also again, I can't post the code because of restriction. But I think I've explained the problem quite in detail.

 

[jaybee3]

User should not be sitting idle while this macro is running he has to indulge in some other activities...

...open some other file let it be a song or browser etc.

lol no comment :P

You could always open up task manager and give excel.exe a higher priority, I dunno what stability implications that would have though.

 

[AnkitGuru]

lol no comment :P

You could always open up task manager and give excel.exe a higher priority, I dunno what stability implications that would have though.

that was just an example :D though a pretty bad one!!!!!!!! hehehehe

 

[number kruncher]

IMHO you under estimate Excel's resource and memory usage.

You mention 4GB of RAM, probably because you have 32bit system, and an i3 quad core. This does not make a power house machine.

I run a custom built i7 chip @3.07Ghz with 64bit Win7 OS with 12GB of RAM.

Although Excel can not use all my RAM, it allow my OS to multitask with other applications with spreadsheet equally as big or greater than the you you mention, whilst routines are running.

Point.. If you want speed.. Get a Ferrari not a Fiesta !!

 

[JackDanIce]

Ankit,

Couple of pointers:

1) Your code maybe confidential but you could change a few words to make it generic - this is something I do all the time or post the portion of code causing the problem

2) Using VBA with merged cells is always problematic and causes problems: http://www.officearticles.com/excel/merged_cells_in_microsoft_excel.htm

3) Relating back to 1, see this all the time when people post questions with very little detail or clarity and expect the reader to be psychic and understand exactly what the problem is, how the spreadsheet layout and workbook set up is and be able to solve the problem effortlessly. The more detail and specifics you provide, the easier it is to suggest solutions. You say your code currently takes ~1sec to process 10-15 lines but without posting the code, how do you know that it is the most efficient way to achieve what you want? I'm assuming you're not an Excep expert relative to the MVPs on this site, so it's not saying that you need any help in programming but it may be that someone can suggest a method that you've not come across before or are aware of?

4) I have macro's scheduled to run via ontime events and never have problems with speed when I'm working on other workbooks in seperate Excel window sessions or other applications

I think it's likely to be a problem with using VBA to merge cells