CODEWITHSUNDEEP
vbaexcelexcel-formula
Luis
Luis

Reputation: 139

How to stop automatic recalculation in excel

In the excel sheet pictured below I would like the recalculation to stop for the top portion from Row 1-5. And what I mean with recalculation is that for example cell M2, H4, S4 have the today() formula and every time I open it it recalculates. I would like for it to calculate once and once I save as to not recalculate again once opened again. Any suggestions? I have tried

Private Sub Workbook_Open()
   Application.Calculation = xlCalculationManual
End Sub

enter image description here

Upvotes: 1

Views: 3522

Answers (4)

Prasun Kumar Khan
Prasun Kumar Khan

Reputation: 163

I would suggest to add the below subroutine under Workbook_Open in PERSONAL.XLSB (to be saved at %APPDATA%\Microsoft\Excel\XLSTART) so that it gets loaded whenever Excel application is launched.

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationManual
    Application.CalculateBeforeSave = False 
End Sub

While Application.Calculation = xlCalculationManual will set the calculation mode to Manual (3rd Option in the below screenshot), Application.CalculateBeforeSave = False will untick the checkbox to Recalculate workbook before saving

enter image description here

Upvotes: 0

Mrig
Mrig

Reputation: 11702

Well, setting Application.Calculation = xlCalculationManual at the time of Workbook_Open may not always assure you the desired result as there is a possibility that the Excel might be in xlCalculationAutomatic while opening your file.

So the workaround is, open Excel and set calculation to manual either in VBE or through menus and then open your file.
In VBE, type Application.Calculation = xlCalculationManual in immediate window then hit Enter and then open your file.
In Excel, for 2007 version, click on Formula Menu, go to Calculation Group, in Calculation Options select Automatic/Manual. For older versions, Tools > Options > Calculation.

Upvotes: 2

gsmari
gsmari

Reputation: 17

I would suggest evaluating the function in VBA on events you want.

If you want TODAY function to calculate on your request, I would create a button with a macro that reevaluates TODAY.

How I would implement this depends on if you want/need to have the cell as a formula. If it is enough to have the calculated value, you can calculate in VBA and set the value to the cells you want. If you want to have a formula and keep the last evaluated TODAY value, you can set the formula to the cell. Range("M2").Formula = Date() & "other formula part"

The easiest solution is to set todays value in some cell, in VBA

Sub Macro1 ()

    Range("A5").Value = Date()

End Sub

Upvotes: 0

Paaqua Grant
Paaqua Grant

Reputation: 160

I don't have the reputation to post a comment to ask a clarifying question, so I will post an answer instead (making an assumption about what your answer would be).

As long as you never want those cells to recalculate (and don't mind losing the formula), the easiest solution would be to convert them to values.

simple macro to achieve this for first 5 rows:

sub ConvertToValues()
Dim rngToValues as Range
set rngToValues = YourSheet.UsedRange.Rows("1:5").EntireRow
rngToValues.Value = rngToValues.Value
end sub

Upvotes: 2

Related Questions