Turn off "everything" while VBA macro running

Question

I have a VBA macro code. Its main purpose is to copy data from a workbook to another workbook; just the rows changing. The macro was fast enough until I made some conditional formatting on the destination sheet.

And my question is whether there is any way to disable every side operation in Excel while the macro is running? Now I disabled these :

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

But I think that I can turn off more to make it faster. Any suggestions?

What @YowE3K said, just remember to turn it back on with Application.Calculation = xlAutomatic — Jeremy
Conditional formatting increases the size of a workbook tremendously, and that would be even more if your macro adds one row at a time in a loop. If turning off calculations doesn't stop Excel from making all the calculations and recording all the data pertaining to the formatting, you might try to paste big chunks of data in one go or, better, apply conditional formatting after you finish pasting or, still better, dispense with conditional formatting and apply the formatting you want using VBA. — Variatus
@Jeremy - In my company, people have the horrible habit of sometimes using "manual" when they shouldn't, so I always have to include code such as Dim origCalc As XlCalculation origCalc = Application.Calculation Application.Calculation = xlCalculationManual and then switch it back using Application.Calculation = origCalc, just so that they don't come screaming at me saying "why did you change my calculation mode !!?!?!" — YowE3K
@YowE3K +1 for this note, far better to save existing settings, disable for the macro, and then change back to original settings at the end. We assume on by default but that's certainly not always the case. — Zerk

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2017-05-05T10:05:53

You can have the following two codes on the standard module...

Sub TurnEverythingOff()
With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With
End Sub

Sub TurnEverythingOn()
With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With
End Sub

And then you can call them in your sub routine like below...

Sub YourMacro()
'variable declaration section

TurnEverythingOff
On Error GoTo Skip   'if an error occurs, this makes sure that everything is turned on back
'other stuff here

Skip:
TurnEverythingOn
End Sub

As far as conditional formatting is concerned, make sure you don't apply it to unused range i.e. not referring whole row or whole column in the conditional formatting formulas. In another words, limit it to the range with data on the sheet.

Turn off "everything" while VBA macro running

2 Answers