VBA CALCULATE

What is VBA Calculate?

The Calculate method in VBA is used to trigger a recalculation of all formulas in the active workbook or in a specific worksheet. This can be useful in situations where you want to ensure that all formulas are up-to-date and reflect the latest data. Suppose you use the Excel formula RANDBETWEEN(1,100) from the range “A1” to “A5.” You can recalculate it by calling the VBA Calculate Manual, by setting the calculation from “Automatic” to “Manual.”

You get a range of values. By creating a subroutine to get a new range of values, you can use the VBA Calculate Formula function to reactivate the RANDBETWEEN function.

After running this subroutine, you will notice the values change.

How to Use VBA Calculate?

To create an example where the Date Function is implemented, follow the steps below.

Step 1: Open “Formluas” in the Excel workbook after you’ve opened it.

Change the process to “Manual”.

Suppose you have a table and its sum printed on the side using the SUM Excel function.

If you change one value in the table, you’ll need to recalculate using the VBA Calculate formula

Step 2: Similarly, in the title bar, choose the “Developer” icon and click on it.

After you click the Developer icon, select “Visual Basic.” It opens the VBA editor.

In the VBA Module, in the title bar, click the “Insert” button and select the “Module” option.

You can now write codes to perform calculate operations for you.

Step 3: Define a subroutine to call the VBA Calculate formula.

Step 4: Implement the VBA Calculate cell function by calling the cell using the Range function and by specifying the worksheet it is in.

This will call out the Excel SUM function called in that cell.

Code:

Sub Re_Calculate()

    Sheets(“Sheet1”).Range(“J7”).Calculate

End Sub

Step 5: Click “F5” or the “Run” icon on the activity bar in the Excel VBA Editor to run the program. Check the sum.

Since the value 180 is changed to 130, the SUM value in cell “J7” changes from 1260 to 1210.

Now you know how the VBA Calculate function is used in general. See some more simple and intuitive examples below to learn more.

Examples

See the different and interesting ways in which the VBA Calculate function is being used.

Example #1

Here, you want a function where all the calculations in your activesheet is done immediately. This is how you can mimic the “Calculate Now” option in Excel VBA. In this worksheet, you use the Excel RAND function which returns a value between 0 and 1.

Step 1: Define a subroutine to call the Calculate Now option in Excel VBA.

This will calculate the entire sheet you’re currently in.

Step 2: Call the VBA Calculate formula in the current sheet you’re in. This is done using the “ActiveSheet” keyword.

Code:

Sub Calc_Now()

    ActiveSheet.Calculate

End Sub

Step 10: Press “F5” to run the subroutine and see the changes.

We can infer that the age of the person is 10 years from this.

Example #2

In this example, you can learn how to activate all the formulae in a WorkBook by following the steps below.

Suppose there are multiple sheets.

Step 1: Define a subroutine to calculate the entire workbook.

Step 2: Using the “Application” keyword in Excel VBA, you can activate all the functions using the “CalculateFull” component in Excel VBA.

The Application keyword makes it so that all open Workbooks will run the VBA Calculate function.

Code:

Sub Calc_WorkBook()

    Application.CalculateFull

End Sub

Step 3: Run the above subroutine. You will observe that all the values in the worksheets have changed.

Example #3

Suppose you have a worksheet full of values. In this worksheet, you use the Excel RANDBETWEEN function which returns a value between a range that you specified. If you want to change the values through the worksheet, you can use the VBA Calculate function to do so.

Step 1: Define a subroutine to calculate the entire worksheet.

Step 2: Call the Calculate function with the sheet name.

Code:

Sub Calc_Sheet()

    Sheets(“Sheet2”).Calculate

End Sub

Step 3: Run the subroutine to change the values using the RANDBETWEEN function. Every time you run the subroutine a new value will appear throughout the worksheet.

Example #4

You can learn how to call the VBA Calculate function throughout a range in Excel VBA. Suppose you have a range where you’ve called the RANDBETWEEN function to print random values between 100 and 200.

You can create a VBA subroutine to run throughout the range.

Step 1: Create a subroutine to run in a specific range.

Step 2: Using the VBA Range function, define the range this subroutine will be working in and also define the worksheet it will be working in using the Worksheets function.

This will ensure that this subroutine will work from the range “D3” to “D16’.

Code;

Sub Calc_Range()

    Sheets(“Sheet2”).Range(“D3:D16”).Calculate

End Sub

Step 3: Run the subroutine to view the changes.

In this way, you can view all the changes and change the values in a range using the VBA Calculate function.

Important Things To Note

• Understand the different calculation modes (Automatic, AutomaticExceptTables, Manual) and ensure that your code aligns with the desired calculation mode.
• Consider other calculation-related methods (CalculateFull, CalculateFullRebuild, etc.) if needed based on your specific requirements.
• Recursive calls to the Calculate method can lead to infinite loops. Be careful when using Calculate within an event that can trigger itself.
• Understand calculation dependencies in your workbook. Ignoring dependencies might result in incorrect calculations or unnecessary recalculations.
• If using workbook indices, be cautious about hardcoding them. Workbooks can change in number and order, so it’s often safer to refer to workbooks by name or other identifiers.

Frequently Asked Questions (FAQs)

Download Template

This article must help us understand the VBA Calculate formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What is VBA Calculate & its Meaning. Here we explain How to use VBA Calculate along with examples & downloadable excel template. You can learn more from the following articles –

• VBA Square Root
• VBA Month Function
• VBA Long