Program flow unexpected behavior

54 views Asked by At

I am running the following Sub, which inside other two subs:

Sub SalvaDadosLogin()

 1:   ThisWorkbook.Worksheets("Registro").range("a11") = loginComdinheiro
 2:   ThisWorkbook.Worksheets("Registro").range("a12") = senhaComdinheiro
 3:   ThisWorkbook.Worksheets("Registro").range("a14") = salvaSenhaComdinheio
 4:   ThisWorkbook.Save

End Sub

Sometimes when I run this sub my VBE starts executing a user defined function (after running the line 1). Other times it doesn't and the other lines of this sub are executed. I don't know what is causing this since the test condition do not change.

Why my worksheet is being recalculated after line 1?

1

There are 1 answers

0
Erik Eidt On BEST ANSWER

Without further information, it looks like you are changing the worksheet, which triggers the recalc.

You can suspend recalculation and restore later.

let savedCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto restore
...
restore:
Application.Calculation = savedCalcMode

If you do this, try to restore even on errors otherwise you'll finish leaving the global calculation state in a funny setting, hence the On Error part.

(FYI, It will recalculate the full worksheet pretty much right away when you restore calculation mode, which means you don't want to do a save/restore when you don't really need to.)