Option Explicit
Private Sub Workbook_Open()
Application.ErrorCheckingOptions.BackgroundChecking = False
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ErrorCheckingOptions.BackgroundChecking = True
End Sub
There are actually several types of background error checking that go on in a workbook. Excel allows you to disable these separate error checks by themselves if don’t want to disable everything. Specifically, you can modify background checking options for empty cell references, error calculations, inconsistent formulas, and omitted cells, among others. For example, you might simply write:
Application.ErrorCheckingOptions.OmittedCells = False
if you only want Excel to stop monitoring for formula patterns that appear to omit cells that Excel thinks should be included in the formula. This will work so long as BackgroundChecking is still True. If you set BackgroundChecking to False like in the example above, Excel will cease all attempts to second guess your work (which can make your life easier, sometimes).
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020