Excel VBA to Calculate Active Sheet (6 Practical Applications)

Have you ever discovered that Excel does not always accurately compute formulas? I imagine that we have all worked with Excel files that were enormous and consisted of several calculations. Sometimes Excel won’t compute all the numbers in the cells. However, it occurs more frequently with larger files. One solution to this problem is to use VBA in order to recalculate everything. We are going to expound on the use of Excel VBA to calculate Active Sheet.

As you can observe in the above image, we imputed a value in cell B2 and inserted a formula in the C2 cell. Using the ActiveSheet.Calculate method, we obtain the outcomes 10 and 20 in the B2 and C2 cells respectively.

What Is VBA Calculate Active Sheet?

The Calculate method in VBA (Visual Basic for Applications) is used to recalculate formulae in a worksheet or a particular range of cells. All the formulae on the active worksheet are calculated using formulae on the active worksheet are calculated when you use the ActiveSheet object’s Calculate function.

Code

Sub Calculate_ActiveSheet()
ActiveSheet.Calculate
End Sub

After defining ActiveSheet.Calculate, This subroutine will compute all the formulae on the current sheet and update the values to correspond with any modifications to the referenced cells when executed. For your convenience, you need to follow different ways to write VBA code in Excel.

Excel VBA Calculate Active Sheet: 6 Practical Applications

In this article, we will discuss 6 practical applications using ActiveSheet.Calculate method while constructing with VBA to calculate in an active sheet. We are considering a dataset from a workbook containing 4 worksheets. Utilizing ActiveSheet.Calculate method, we will show you practical applications.

1. Calculate Sheet Only

To recalculate or calculate on a specific Worksheet, you must use Activesheet.Calculate method. Therefore, all the formulas in the selected active sheet (Sheet1) will occur once we execute the VBA code.

Code

Sub calculate_on_a_specific_sheet()
ThisWorkbook.ActiveSheet.Calculate
Sheets("Sheet1").Calculate
End Sub

2. Insert Value Using ActiveSheet.Calculate

In this case, we are likely to insert a string value in an Excel cell. So, using the method Activesheet.Calculate to enable an active sheet. We selected a specific sheet Main Sheet and inserted a string value “Exceldemy” in the B2 cell.

Code

Sub insert_value()
ThisWorkbook.ActiveSheet.Calculate
Range("B2") = "Exceldemy"
End Sub

3. Update Dependent Values

To insert values in the active sheet and re-calculate the sheet based on the formula, we must use the Activesheet.Calculate method. Therefore Inserting a value and formula depending on that value, we obtain 20 and 30 in two consecutive cells B2 and C2.

Code

Sub upadte_dependent_value()
Range("B2").Value = 10
Range("C2").Formula = "=B2 * 2"
ActiveSheet.Calculate
End Sub

4. Calculate Via Conditional

Calculations based on logical expression can also be executable once we use ActiveSheet.Calculate method. Here we consider a logic for the value of B2 to be greater than 100. As you can see, B2 = 10, and therefore, cell C2 remains blank after executing the VBA code.

Code

Sub Calculate_via_conditional()
If Range("B2").Value > 100 Then
ActiveSheet.Calculate
Range("C2").Formula = "=B2 * 2"
End If
End Sub

5. Get Total Number of Rows

From a range of data, you will be able to calculate the total number of rows. We are here selecting Sheet1, defining Rng as Range(“B2:D11“), and using Rng.Rows.Count function we obtain Total Rows: 10 in the Immediate window.

Code

Sub Total_rows()
Set Rng = Range("B2:D11")
ActiveSheet.Calculate
Debug.Print "Total Rows: " & Rng.Rows.Count
End Sub

6. Obtain Total Number of Columns

Like row counting, from a range of data, you will be able to calculate the total number of columns. Selecting Sheet1, defining Rng as Range(“B2:D11“), and using Rng.Columns.Count function we obtain Total Columns: 3 in the Immediate window.

Code

Sub Total_columns()
Set Rng = Range("B2:D11")
ActiveSheet.Calculate
Debug.Print "Total Columns: " & Rng.Columns.Count
End Sub

Excel VBA Manual Calculation on One Sheet Only

Generally, the automatic calculation is the by-default setting while working with formulas in worksheets. Therefore, we need to turn off automatic calculation. First, we declare ws as a worksheet. Enabling Sheet1 mentioning it. Typing Application.Calculation= xlCalculationManual statement we turned off automatic calculation. Finally, ws.Calculate dictates to recalculate the formulas of Sheet1.

Code

Sub Calculate_manually_in_one_sheet()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
Application.Calculation = xlCalculationManual
ws.Calculate
End Sub

Frequently Asked Questions(FAQs)

1. How do you calculate active worksheets?

Utilizing ActiveSheet.Calculate method, we can select active worksheets and calculate accordingly on that worksheet. Using the following lines, you will be able to insert Exceldemy text in the B2 cell.

ThisWorkbook.ActiveSheet.Calculate
Range("B2") = "Exceldemy"

2. How do I select an active workbook in Excel VBA?

Writing ActiveSheet.Calculate method in the code and select the required sheet before hitting the Run icon, we can select the worksheet as an active worksheet.

Range("B2").Value = 10
ActiveSheet.Calculate

Therefore, we will be able to insert 10 in the B2 cell of the active workbook.

3. What is the difference between active sheet and select sheet in VBA?

One or more worksheets that were specifically chosen within an Excel window are referred to as Selected Worksheets. There is a specific collection of Selected Worksheets for each Workbook. The worksheet you are observing and working on right now is called ActiveWorksheet.

📝 Takeaways from This Article

• The application of ActiveSheet.Calculate method.
• Recalculation by using ActiveSheet.Calculate method.
• Insert a specific value into an Excel cell of the active sheet.
• Update the active sheet based on the dependent value.
• Use conditionals and get values in the active sheet.
• Figure out the Number of rows and columns from a range of active sheets.

Download Practice Workbook

To practice, please download the Excel Workbook file from the link below.

Conclusion

We concisely construe 6 practical uses of Excel VBA to calculate on an active sheet. I hope you will be able to recalculate using ActiveSheet.Calculate statement with VBA. Any suggestions including queries are appreciated. Don’t hesitate to leave your thoughts in the comment section. For related articles and knowledge, visit our site.

Related Articles

• Excel VBA to Sort Worksheet
• Excel VBA to Sort Worksheets Numerically