I need to write a vba code that calculates all the formulas in the Active Sheet alone…
- User
I need to write a vba code that calculates all the formulas in the Active Sheet alone when the sheet is activated
- Assistant
Here is a VBA code that calculates all the formulas in the active sheet when the sheet is activated:
Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationAutomatic ThisWorkbook.RefreshAllEnd Sub
This code sets the calculation mode to automatic and refreshes all the formulas in the workbook when the active sheet is activated. Changing the calculation mode to automatic ensures that any new formulas or changes to existing formulas are automatically calculated when the sheet is activated. The RefreshAll method updates all the formulas in the workbook, including any external data connections.
- User
I want to refresh all the formulas for the sheet alone and not for the entire workbook. Workbook is set to manual and must remain in manual calculation
- Assistant
In that case, you can modify the previous code to only calculate the formulas in the active sheet and not update all the external data connections in the workbook. Here is the modified code:
Private Sub Worksheet_Activate()
Application.EnableEvents = False Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ActiveSheet.Calculate Application.EnableEvents = True Application.ScreenUpdating = TrueEnd Sub
This code temporarily disables events and screen updating to improve performance. It then sets the calculation mode to manual and calculates all the formulas in the active sheet only. Once all the formulas are calculated, the code enables events and screen updating again. This ensures that the user interface is responsive during the calculation process.
Note that this code only refreshes the formulas in the active sheet. To refresh the formulas in all the sheets in the workbook, you can use the Workbook_SheetActivate event instead of the Worksheet_Activate event.