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.
- Public Sub ToggleExcelFunctionalities(Optional ToggleValue As Boolean = True)
- With Application
- .ScreenUpdating = ToggleValue
- .DisplayAlerts = ToggleValue
- .DisplayStatusBar = ToggleValue
- .EnableEvents = ToggleValue
- .AskToUpdateLinks = ToggleValue
- If ToggleValue Then
- .Calculation = xlCalculationAutomatic
- Else
- .Calculation = xlCalculationManual
- End If
- End With
- End Sub
You can use it in pairs, like below:
- Sub main()
- ToggleExcelFunctionality (False)
- ' add your code here
- ' ...
- ' ...
- ToggleExcelFunctionality (True)
- End Sub