Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

I need to write a vba code that calculates all the formulas in the Active Sheet alone…

  1. User

    I need to write a vba code that calculates all the formulas in the Active Sheet alone when the sheet is activated

  2. 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.RefreshAll
    

    End 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.

  3. 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

  4. 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 = True
    

    End 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.