Macro takes approx. 15 seconds to run, how do i reduce run time?

[galvinpaddy]

Good morning happy campers,

I have a large sheet with numerous formula's etc within it, spread over 4 tabs, with a 5th tab readin info from an exported source.

I have the following macro,

Please Login or Register  to view this content.

Now i know its a big ask, asking for help on a code/sheet without loading the sheet here, but at present its A) Huge and B) full of sensitive info, so the next big ask would be if someone would kindly do this with receiving the sheet through drop box perhaps?

I look forward to your responses, thanks in advance.

Galvinpaddy

[JosephP]

have you at least stepped through the code to figure out which parts take the most time?

[Mowgli]

Galvinpaddy,

Good luck, I have a macro which takes 3min 40sec to run although admittedly it is much longer LOL

[galvinpaddy]

have you at least stepped through the code to figure out which parts take the most time?

Hi JosephP, i have gone through 'Step Into' using F8 from the Visual Basic window, but there is no delay.
Once i have gone back to the sheet, in the bottom right it did read 'Calculating' with an incremental percentage.

Other than this i am afreaid i am slightly lost.

[Norie]

Do you need to calculate?

[JosephP]

then I reckon it's the recalculation that causes the delay and not your code. you need to look at your formulas if 15 seconds is really unacceptable

[galvinpaddy]

Hi both,

Calculations - Do i need to? hhmm, i believe i do, as all cells within one tab are all linked to the exported data (another excel sheet in a seperate location) if i take off the

Please Login or Register  to view this content.

Would that have a negative impact on the rest of the code?

[galvinpaddy]

OK, update, and before i do, i have no idea why it works this way

Once i run the macro, the sheet will basically appear to hang as if the code is taking ages, but as soon as i click somewhere on the sheet, the formula completes.

Is there something dodgy in my original code??

[JosephP]

no-that's normal excel behavior. if you click and wait a moment excel will probably start calculating again

[galvinpaddy]

but thats the thing, it doesnt appear to do so. once i click, it works as normal and lets me carry on with what im doing.
Is it simply the case that i cant resolve it and as such need to click to continue?

[JosephP]

it shouldn't be-do you have calculation set to manual?

[galvinpaddy]

No, its set to Auto

[JosephP]

then why do you explicitly calculate the range in your code?

[galvinpaddy]

in all fairness, i have no idea.
The code was created by visiting here and asking various questions, in all fairness, i understand some, but not all of it.
Please, feel free to pull it to pieces and re-order it

[JosephP]

try just commenting out the

Please Login or Register  to view this content.

line

[galvinpaddy]

Ok, i have commented out that line and have come across the following:

When i add a 'button (form control) with the macro above added to it, the code takes around 1 minute to cycle
When i add a 'Command Button (Active X Control) with the above macro, the code will run in about 2-4 seconds,

With the Form Control, i am unable to click somewhere to continue using the sheet until it un freezes
With the Command Button, as soon as i click it, i can click in a cell and the macro appears to be complete.

[JosephP]

"appears to be complete"-have you checked it is actually doing what you think?