Toggle Excel functionalities to speed up a macro

Tested in Excel 365 (16.8730.2046) 64-bit

A simple sub to turn on or off the things that slow a lot a macro.

 
 
  1. Public Sub ToggleExcelFunctionalities(Optional ToggleValue As Boolean = True)
  2.    
  3.     With Application
  4.         .ScreenUpdating = ToggleValue
  5.         .DisplayAlerts = ToggleValue
  6.         .DisplayStatusBar = ToggleValue
  7.         .EnableEvents = ToggleValue
  8.         .AskToUpdateLinks = ToggleValue
  9.         If ToggleValue Then
  10.             .Calculation = xlCalculationAutomatic
  11.         Else
  12.             .Calculation = xlCalculationManual
  13.         End If
  14.     End With
  15. End Sub

You can use it in pairs, like below:

 
 
  1. Sub main()
  2.     ToggleExcelFunctionality (False)
  3.    
  4.     ' add your code here
  5.    ' ...
  6.    ' ...
  7.    ToggleExcelFunctionality (True)
  8. End Sub

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.