Question regarding "Sheets("Sheet1").Range("A1:A10").Calculate" vs "Application.Calculation = xlManual/xlAutomatic"

[bigbird9999]

Good day to You all!

I've "inherited" an Excel file that works rather slowly. It consists of multiple sheets with 30+ columns and ±6000 rows containing VLOOKUPS, XLOOKUPS, etc. in lengthy tables. The file needs to be used by unexperienced people. Their work consists of adding/deleting rows and then pushing a "button" to start VBA-code that copy/paste formulas etc.

The recalculation nowadays takes about 15 seconds. Not a pleasant user experience... I would like to minimize the time needed for recalculation. In my quest on the Interwebs I've encountered the command:

Sheets("Sheet1").Range("A1:A10").Calculate

It looks like a viable solution for the issue, but I can not discover if the code bypasses a complete recalculation or not. The current code uses the

Application.Calculation= xlManual/xlAutomatic

method.

Eternal Kudo's for the person(s) that can shed some light on this subject!

Grtz,
Bert.

 

[Jamie McMillan]

Good day to You all!

I've "inherited" an Excel file that works rather slowly. It consists of multiple sheets with 30+ columns and ±6000 rows containing VLOOKUPS, XLOOKUPS, etc. in lengthy tables. The file needs to be used by unexperienced people. Their work consists of adding/deleting rows and then pushing a "button" to start VBA-code that copy/paste formulas etc.

The recalculation nowadays takes about 15 seconds. Not a pleasant user experience... I would like to minimize the time needed for recalculation. In my quest on the Interwebs I've encountered the command:

Sheets("Sheet1").Range("A1:A10").Calculate

It looks like a viable solution for the issue, but I can not discover if the code bypasses a complete recalculation or not. The current code uses the

Application.Calculation= xlManual/xlAutomatic

method.

Eternal Kudo's for the person(s) that can shed some light on this subject!

Grtz,
Bert.

Hello Bigbird,

I hope that this helps.

If manual calculation is set then excel will not re-calculate anything unless you manually do it (Press F9; or via a macro.) If automatic calculation is set then excel will re-calculate every time any data changes.

Sub ManualCalc()
    Application.Calculation = manual
End Sub

If you add:-

Application.ScreenUpdating = False

At the start of the VBA code to calculate everything and:-

Application.ScreenUpdating = True

at the end; it will calculate faster.

Sub QuickCalc()
Application.Calculation = manual   ' sets to manual calculation:- if it is set to xlautomatic
Application.ScreenUpdating = False
Calculate
                                              'Then do the copy and paste VBA code
Application.ScreenUpdating = True
End Sub

*** You can set it Excel to always change the calculation to manual when the file is opened.
By opening VBA editor Alt-F11.
Open the 'ThisWorkbook' code from the left-hand selection window.
then paste the code below.

Option Explicit
Private Sub Workbook_Open()
    Application.Calculation = xlManual
End Sub

***

Now when the file is opened Excel is set as manual calculation.

Jamie McMillan