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).

 

[Michael M]

I tend to agree....with Jack
I run a bottom of the line $399 laptop.
I can download 1000's of lines of data from 37 websites, process it, graph it and table it in under a minute.....mainly, of course, with the help of everybody here.

If you can't expand, we can't contribute !!!

 

[diddi]

if i had a macro that took 30 minutes to run, i would be rewriting it. that said there are a number of observations i would make, viz.

a large workbook with many sheets makes considerable demands on hard drive access. firstly, excel does not store that whole workbook in RAM, so it is constantly caching from HDD, and also it is heavy of RAM so it tends to use the page file a lot as well.

when you run other applications such as media players, they stress an already overworked drive which causes considerable problems with the page file access.

how do you know your code is optimised? have you compiled it as a .dll and called it from excel? (that gives you about 15X speed bump) have you sought any opinions about the algorithm? have you run the code over external files to save memory load? i am never too proud to ask for advice on my code.

from my knowledge of intel hardware, i was not aware that i3 had 4 processors? has a new chip been brought out, which would be at odds with the i5 branding...

 

[AnkitGuru]

@diddi I am not adamant, or proud or any other such stereotype.
Rather I am very open to suggestions and also like to contribute on multiple programming forums, just that this time the concern was
not programming syntax but I needed to know how exactly things work, I searched a lot but couldn't find anything anywhere. My simple problem how things work, but responses I got were
>show me your code,
>your code needs optimization
>you should tell in detail what you are doing. Kiddish responses

I mean will the basic concepts like thread priorities change if these people can optimize my code. Optimization is always the final step, here I am looking to solve a problem.

I'll explore your caching and paging comment, maybe this is the reason i was also thinking in same direction.thnx!!

 

[Michael M]

Hmm, in future I should remember to keep my Kiddish responses to myself...not an ideal way to solve any issue on a forum where one is asking for input / assistance...

 

[diddi]

@mm
i think we should ban all octo or is that septa-genarians from the site in view of their kiddish replies. so no more from you.

 

[MARK858]

Normal run time of 30 minutes is extended to over 2 hours.

QUOTE]Optimization is always the final step[/QUOTE]Time issue but optimization is the final step ????
There you go, I knew I had been doing it all wrong. I might have to stay away from the forum for a long time to adjust my thinking but then I suppose it's what you interpret as optimization rather than method change.

I must admit if it was me in this situation I would be looking at my code first.
If I believed that the code was the most efficient it could be then I would be looking at whether Excel was the right beast for the task if it took 30mins to run let alone 2 hours (after repairing the window I had just thrown the computer through)

 

[diddi]

yes, with todays hardware, the notion of waiting 2 hours to crunch something is absurd. there has got to be a problem with the algorithm.

 

[AnkitGuru]

Final Post
Finally I got it:

http://superuser.com/questions/6400/how-do-you-keep-microsoft-excel-running-at-full-speed-even-when-it-does-not-have


Look at it and see why you didn't need the code.
Never coming back to this forum again.
Code is not everything, you should have some concepts too.

the code i created is already an updated version
the previous one took 8 hours straight to do the same task
now its less than 1/8.

 

[diddi]

the solution they offer is valid for a workbook of 2000 worksheets, but really - who could possibly claim that their project is well planned and thought out when it requires 2000 worksheets to achieve an outcome?

thats for your feed back for anyone else considering such an issue.