How to refresh calculation instantaneously in excel

Question

I have a problem in refreshing the cell's calculation.

In other words, I have many columns where i have a formula or macro in each one of them.

But the problem is that i should absolutely activate the option " automatic calculation" in the excel options or i should save then the new results appear.

Now, I would insert something in the macro that can refresh me the results instantaneously.

Thanks

Answer 1

To force all formulas to update, including custom vba formulas, execute the following in VBA:

Application.CalculateFullRebuild

This can also be executed from the VBA immediate window.

More documentation here: https://learn.microsoft.com/en-us/office/vba/api/excel.application.calculatefullrebuild

Answer 2

You could simply press :

F9 to calculate the whole active workbook

Shift + F9 to calculate the active sheet

---

Anyway, here are the different options with .Calculate :

Sub Souma()

'Use this to calculate all the open workbooks
Application.Calculate

'Use this to calculate the whole sheet, called "Sheet1"
ThisWorkbook.Worksheets("Sheet1").Calculate


'Use this to calculate the cell A1 on the sheet called "Sheet1"
ThisWorkbook.Worksheets("Sheet1").Range("A1").Calculate
'Use this to calculate the range A1 to D10 on the sheet called "Sheet1"
ThisWorkbook.Worksheets("Sheet1").Range("A1:D10").Calculate
'Use this to calculate the column A on the sheet called "Sheet1"
ThisWorkbook.Worksheets("Sheet1").Columns(1).Calculate
'Use this to calculate the row 1 on the sheet called "Sheet1"
ThisWorkbook.Worksheets("Sheet1").Rows(1).Calculate

End Sub

Answer 3

Copy and paste the code below to your VBA project:

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

This code should automatically run after you open the Excel file, enabling automatic calculation in all opened files.