Speeding up code

Hi all,

I'm working on a spreadsheet for analysing fatigue on wind turbine blades. I have no formal programming training so what you see when you dig in to the VBA may shock and disgust you, but could anyone have a quick look over the code and suggest ways of speeding it up? I've done all the stuff that I can find on the net (turning off screen updating, declaring variables etc) but although screen updating definitely improved things I timed it and I think declaring variables actually slows it down. I'm not after code rewriting, just some pointers (it's the only way I'll learn). The modules that need optimising are:

TopLevel
CopyStrainData
PeakValley
HalfRainflowCount
GoodmanDiagramLookup

The rest are just left over from me working stuff out. Screen updating is turned off in TopLevel which calls the other files as and when they are needed.

Any help will be much appreciated as it already takes 5 mins to run and I will soon be using it with input files that are 30 times longer, and I need to run it about 50 times so it will soon add up!

http://www.dur.ac.uk/p.r.greaves/Rai...heet_2007.xlsm - 889 kb

Thanks,

Pete

Avoid Select and ActiveCell, e.g.

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

Probably more you can do, but haven't looked at your code in detail.

There are other global events that you can do to speed up your macros. I use these tips from "Excel VBA Performance Coding Best Practices":

Please Login or Register  to view this content.

abousetta

thanks for your help, it seems to have had an effect. I've had the most improvement from changing the spreadsheet so as many calcs as possible are performed in VBA. Is there any way I can load data etc in VBA rather than working in the cells of the spreadsheet?

Yes, and that is much more efficient. Plenty around on that, e.g. see towards end of this page:
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

Try to write as little as possible to the worksheets.
Most of the math can be done in variables and arrays.