If you run your VBA code with calculations that affect any cells on your workbook sheets, it is good practice to disable some Excel services to increase the speed of the calculation.
Call these functions before and after your calculation code to reduce the calc time.
'-------------------------------------------------------------------------
' Source: risksir.com
' Description: Acceleration of VBA calculations by disabling slow services
'-------------------------------------------------------------------------
'To accelerate, use "Call AccelerateExcel" at the beginning of your function
Public Sub AccelerateExcel()
'Don't update screen
Application.ScreenUpdating = False
'Disable automatic calculation
Application.Calculation = xlCalculationManual
'Don't show page breaks
If Workbooks.Count Then
ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False
End If
'Disable events
Application.EnableEvents = False
'Don't show status bar
Application.DisplayStatusBar = False
'Disable alerts
Application.DisplayAlerts = False
End Sub
'Don't forget to enable disabled services by including "Call disAccelerateExcel" at the end of your function
Public Sub disAccelerateExcel()
'Turn on screen update
Application.ScreenUpdating = True
'Turn on automatic calculation
Application.Calculation = xlCalculationAutomatic
'Turn on page breaks
If Workbooks.Count Then
ActiveWorkbook.ActiveSheet.DisplayPageBreaks = True
End If
'Turn on events
Application.EnableEvents = True
'Turn on status bar
Application.DisplayStatusBar = True
'Turn on alerts
Application.DisplayAlerts = True
End Sub