Excel Freezes After Running Macro

[BrianAll]

Hello,

I have a macro in a Worksheet Change.
It works fine, but, as soon as the macro stops running, the screen get frozen for few seconds (even the VBA Editor).
I can't get why this happens, any clue?

I already tried with the DoEvents at the beginning of the Sub.
I have many Application.EnableEvents (and Application.ScreenUpdating) because they prevent the Worskheets Change to start again when I write (through the code) something in the sheet.
In any case, at the and of the sub I set bot of the as "True".
The code is too long to be attached here, let me know if needed.

Thanks
Regards

[rorya]

It's pretty hard to debug it when we can't see it.

[BrianAll]

You should find the Sub attached.
Thanks!

[rorya]

Um, nope.

[BrianAll]

.cls was invalid format.
I attached a .txt

[rorya]

Does the freeze happen no matter what cell you change?

[BrianAll]

@rorya
Yes...

[rorya]

I can't test that code in isolation as it calls other code and uses variables that aren't declared - can you post the workbook?

[BrianAll]

Hello,
it contains sensitive data.
I attached the code of the Call routine.
I hope it helps.

Thanks!

[rorya]

I don't need any data in the file, just the structure including formulas and formatting. Otherwise I have to try and go through all of your code to create the necessary worksheets to prevent errors and it likely still won't reproduce your issue.

[BrianAll]

Here is the file.
I think it will crash but I still hope it helps..

[rorya]

It doesn't crash, but also doesn't display any lag for me. Does that sample still exhibit the problem for you? If so, try starting Excel in safe mode and see if it still has the issue when using the workbook.

[BrianAll]

Nope. Any lag.
I tried to clean the workbook in order to leave just one working example to show you and it still doesn't show any lag...

I have an almost identical copy of the whole file. One is showing lag, the other one not. Any clue on this?

[rorya]

I suspect the clue will be in the "almost" part of "almost identical". What's different? Lags like that are usually down to something like recalculation - does one have volatile formulas that the other doesn't?

[BrianAll]

Nope, no formulas.
Just different set of data in one sheet but they have also the same DB (where to search when Target.Column is = 2 and the value = "Interno").
I even tried with Application.Calculation = xlCalculationManual. No clue.

The problem is within the Call.
I set an interruption before the Call. Up to there, everything runs smoothly.
I set a code interruption on the line below the Call --> the macro is performed fine, the cursor reaches the stop and the line is highlighted in yellow. But still it takes few second to allow me doing anything (i.e. pressing F8 to continue step by step).

[rorya]

The only thing I can think of at this point would be the conditional formatting on one of your sheets. Try deleting that (in a copy of your workbook) and then see if the behaviour persists.

Your called code is not particularly efficient, but there's nothing in there that would keep running after the call to it had completed.

[BrianAll]

Hi rorya.
Great point!
Without conditional formatting a small lag persists but it's remarkably faster.
Any hint about preventing this through VBA without deleting all conditional formatting rules? (Maybe I can use a worksheet change instead of conditional formatting where needed…)

(Yeah, I know it is not effecient at all. I wrote it in different steps, I should take few moments and "clean it up").

[hot toddy]

Did you ever solve this? I think I've got the same problem... I'm new here and not sure what the etiquette is; should I ask here or start a new post?

I have a macro that creates about 50 sheets. It executes quickly, and there is no hang immediately on completion, but upon the first change made to a cell after that it hangs for about 15 seconds, then goes back to normal for subsequent changes.

I suspected it was just recalculating for the first time after so many changes to the spreadsheet (I turn screen updating and calculating off at the start of the macro, and back on at the end), but I've tried to force it to recalculate just before exiting the macro and that didn't help. I've tried the following lines of code in every imaginable order:

Please Login or Register  to view this content.

I've also tried putting this at the very end of the macro to simulate changing a cell after the macro has finished:

Please Login or Register  to view this content.

But nothing works. Any clues?

[FDibbins]

Did you ever solve this? I think I've got the same problem... I'm new here and not sure what the etiquette is; should I ask here or start a new post?...

Welcome, and yes, please start a new thread, otherwise it's considered highjacking to ask a question on another member's thread. Quote the link to this thread if you think it would be relevant