Calculate Worksheet Method in VBA is used to calculate all open workbooks or a specific worksheet in a workbook or a specified range in a worksheet.
Why we use Calculate Worksheet Method in VBA?
If a workbook or a worksheet or a specific range has formulas we need to refresh each time when the values are changing. In that time we can use Calculate Worksheet Method in VBA.
VBA Calculate Worksheet Method- Syntax
Here is the example syntax to Calculate Worksheet method in Excel VBA.
Where Worksheet represents the object and Calculate is the method of worksheet object.
VBA Calculate Worksheet Method: All Open Workbooks
Please see the below VBA code to Calculate all open Workbooks. In this example we are using calculate method of application object.
Sub Calculate_AllOpenWorkbooks() Application.Calculate End Sub
VBA Calculate Worksheet Method: In Specific Sheet
Please see the below VBA code to Calculate in specified Worksheet named “Sheet1”.
Sub Calculate_Sheet() Sheets("Sheet1").Calculate End Sub
VBA Calculate Worksheet Method: In Specified Range
Please see the below VBA code to Calculate in specified range (“A1:E10”) in a specified worksheet named “Sheet1”.
Sub Calculate_Range() Sheets("Sheet1").Range("A1:E10").Calculate End Sub
VBA Calculate Worksheet Method: Instructions
Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:
- Open an Excel Worksheet
- Press Alt+F11 to Open VBA Editor
- Insert a Module from Insert Menu
- Copy the above code for activating worksheet and Paste in the code window(VBA Editor)
- Save the file as macro enabled Worksheet
- Press ‘F5’ to run it or Keep Pressing ‘F8’ to debug the code line by line and observe the calculations in the Worksheet.
Is there a way to call VBA Code when a cell value gets changed, when one calculates the worksheet