What is wrong with this coding?

Question

Referring to following coding, it hangs for not responding for long period of time.

I would like to know on what wrong it is and how to solve it.

ActiveSheet.EnableCalculation = True ' in case it was False
With Application
.Calculation = xlCalculationManual
.CalculateBeforeSave = False
.ScreenUpdating = True
End With

Does anyone have any suggestions?
Thanks in advance

Answer 1

I can only monitor the running status, process is hanging here as shown below.

I also place a mark on function, which require a lot of calculation, but it does not goes and stop on those function yet during hanging period.

Is there any approach to determine where could go wrong?

Do you have any suggestions?

Thank you very much for any suggestions (^v^)

Case 1

Some coding ...

ActiveSheet.EnableCalculation = True ' in case it was False
With Application
.Calculation = xlCalculationManual ' Not responding for calculation
.CalculateBeforeSave = False
.ScreenUpdating = True
End With

Answer 2

I test above 2 cases with the same coding, there is "not responding for calculation only for case 1, not for case 2.

If the code is the same, why should it be this code?

Logical, isn't it?

Andreas.

Answer 3

With removing coding, when I perform xlCalculationManual, it still take a long time,

[Removed] ActiveSheet.EnableCalculation = True ' in case it was False
With Application
.Calculation = xlCalculationManual
.CalculateBeforeSave = False
.ScreenUpdating = True
End With

Case 1

Some coding ...

ActiveSheet.EnableCalculation = True ' in case it was False
With Application
.Calculation = xlCalculationManual ' Not responding for calculation
.CalculateBeforeSave = False
.ScreenUpdating = True
End With

Case 2

ActiveSheet.EnableCalculation = True ' in case it was False
With Application
.Calculation = xlCalculationManual ' No problem at all, when I run it independently.
.CalculateBeforeSave = False
.ScreenUpdating = True
End With

I test above 2 cases with the same coding, there is "not responding for calculation only for case 1, not for case 2.

Do you have any suggestions on what else could go wrong?

Thank you very much for any suggestions (^v^)

Answer 4

As you can read here https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.enablecalculation

When you change the value from False to True, Excel recalculates the worksheet.

Excel calculates before you set

Application.Calculation = xlCalculationManual

and therefore it needs some time to perform (i guess a lot) of calculations, conditional formattings, etc.

Andreas.