Macro Speed Varies

1. What controls this seemingly random change in speed?

Who knows? It doesn't sound as though it is random. If it was a 100 lines yesterday, and 10 lines now, but it won't go back to 100 ... probably not random, probably broke. Guessing you have done something inadvertently. Have you added any array formulae? Or SUMPRODUCT? Do they have full column references? Have you copied a formula (or two, or three) down to the bottom of the column(s)? Have you introduced any Conditional Formatting? Does any Conditional Formatting apply to solid blocks of cells? Or is it split into many areas? And possibly duplicated?

2. How can I get my macro to run FAST every single time?

Do you switch calculation to manual before and automatic after? Do you switch off screen updating before and on again after? Do you have any event handlers, particularly Change and Selection_Change? Do you switch off event handling before and on again after? Do you select sheets, columns, rows or cells, in order to work with them? That is do you have structures like:

Please Login or Register  to view this content.

If you do, they can be combined:

Please Login or Register  to view this content.

Other than that, it's difficult to guess at how to improve it and, ideally, see it working in a sample workbook.

Regards, TMS

100 lines per minute is still incredibly slow. Chances are that if you post a workbook with the code and some data (use mockup data if you have some sensitive data) it can be made to run much faster.

To add to TMS's excellent comments;
Without knowing specifics of what your macro is doing, even 100 lines\minute is considered very slow. There are likely ways to speed it up (TMS's suggestions not withstanding). If you want to post your code and explain what it does, I'm sure you'll get additional recommendations to improve performance.

@AlphaFrog: thank you, I do my best ... but, as you say, without seeing something to assess, it's difficult to be specific. And there may be other issues when it's staring you in the face. Equally, often it's possible to read all the data into an array and process it all in memory. Some coders are more adept at that approach than others (like me) who tend to stick with processing on the worksheet but try to minimise the slow elements.

Regards, TMS

There's a lot of code to assess but, as suggested, there's quite a bit of this:

Please Login or Register  to view this content.

which you could reduce to this:

Please Login or Register  to view this content.

or even:

Please Login or Register  to view this content.

So, a) you don't select the cell in order to manipulate it and b) you don't use an intermediate variable to store the formula.

You also have commented out the ScreenUpdating = False and ScreenUpdating = True which won't help. It shouldn't cause a calculation problem as it's just preventing the screen being redrawn, not stopping cell values being updated.

I can't see that you switch calculation to manual so that's likely to slow it down.

But, in my opinion, the chances are that the biggest problem is the Worksheet_Change event handler. You are putting a formula in a cell in the event handler, hence each change (to the cell's formula) will fire the Change event again ... potentially causing a loop.

We can't assess any issues with the actual workbook/worksheet(s) because we don't have access to it.

Regards, TMS

Not sure how to answer that, at least any differently than the explanation of what (I think) is wrong.

In the Change event handler, you need to set Application.EnableEvents = False before making a change within the event handler. And then Application.EnableEvents = True after making the change.

Similarly, Application.ScreenUpdating = False at the start of your code and Application.ScreenUpdating = True at the end.

And you can set Application.Calculation = xlCalculationManual at the start of your code and Application.Calculation =xlCalculationAutomatic at the end. Given the apparent complexity of your calculations, it might be worth being a little circumspect about this. Switch it to manual before specific sections of code and to automatic after to ensure that calculations are done appropriately.

And I've already described how not to select cells.

Regards, TMS

@Lewis: whilst I totally agree with your suggestion, there are issues with the code that I think would make it relatively slow at the best of times. What I can't guess at is what has changed to make the code an order of magnitude slower. A reboot might just address that

Regards, TMS

Please post a sample workbook with a representative example of the data. Only a few 100 or even a few dozen rows of non-sensitive is necessary. The intention is not to test the speed, it's to make sure the code compiles.

Oh, and the code all needs to be where it should be

Try zipping it up. Failing that, try DropBox or similar.

The size of the file is indicative of the problem. It will be interesting to see. Is this the actual file? No sensitive data?

I've made changes to the code but, unfortunately, I just can't run the code on my machine. It may be running correctly but I'm not prepared for my laptop to be locked up for an unknown amount of time.

I've made the amendments I suggested.

My new concern is that the code uses SendKeys. Seriously not a fan and I have no idea whether or not it is necessary. If my understanding is correct, it is entering Edit mode and the pressing Enter

Please Login or Register  to view this content.

Why would you do that?


Whatever, I suspect that I cannot provide any further help.


Regards, TMS

I'm away for a few days so I'm not in a position to look at it in any detail. However, faster but wrong is not a good outcome.

I'd try commenting out the Application.Calculation = xlCalculationManual at the start and see if that helps.

SendKeys does what it says ... sends keystrokes to the active application. It can be very unreliable with unexpected results and very risky.

F2 causes Excel go into Edit mode and, when keyed manually, you'd see the cursor in the formula bar at the end of the text, value or formula. The tilda (~) is the equivalent of pressing Enter to re-commit a formula or re-enter data and I'd expect it to cause Excel to calculate.

So, imagine selecting cell A2, pressing F2 to enter Edit mode, and pressing Enter. The cursor would move down to the next row/cell. Repeat 30,000 times and move across 1 column ... repeat for 200 columns.

Not sure if it's doing it for every cell but it's not good. Could explain why things are slow. But it could be there with the intention of forcing re-calculation.

Whilst I can suggest performance improvements, I don't have an appreciation of the logic or calculations or what issues the use of SendKeys was intended to overcome. Consequently, I don't know what would happen if you were to comment the lines out and test the code without them.

Regards, TMS