Imagine you’ve written a VBA code that writes in a file, saves and closes it. Now, every time you run your code it shows a popup message in Excel like this (if the file already exists).
You can click OK for one time but if your code does the same for 100s of the time then it is not feasible to do so. You don’t want this to happen. You need to automate this process. To do so, just add these lines to your code.
|Sub DisablUpdates() |
With Application.DisplayAlerts = False 'Turns of alerts.AlertBeforeOverwriting = False 'Turns of overwrite alerts.ScreenUpdating = False 'Turns of screen updatingEnd With
'*****************'Your Code here'*****************
With Application.DisplayAlerts = True 'Turns back on alerts
.AlertBeforeOverwriting = True 'Turns on Overwrite alerts
.ScreenUpdating = True 'Turns on screen updating
This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
To use this code, you first need to enable VBA to excel of course.
At the beginning of the code, we disabled all unnecessary operations and in the end, we turned them on so that your system works as it was working before.
With Application: This line gives us access to all properties of the Application object. We can invoke them just by using ‘.’ (dot) operator if called using “With” Block.
.DisplayAlerts = False: This is a property of the application object. See here we have called it using “.” operator just.This line disables all alerts of the closing file, overwriting, or opening an already open file.
.AlertBeforeOverwriting = False: This line disables alert overwriting cells during drag down operation or any other on sheet alert.
.ScreenUpdating = False: in almost every code you move from one cell to another, one sheet to another and one workbook to another. If you don’t add this line, you will see screen flickering. Every movement you do on the system using VBA will be displayed. This causes time overhead and can cause your system to hang. To save time and resources always incorporate this line.
The bottom block must also be executed to turn your excel back to normal and to see results. You are doing nothing but turning every switch on you turned off in the Write this block before each End Sub and Exit Sub.
.DisplayAlerts = True
.AlertBeforeOverwriting = True
.ScreenUpdating = True
This is one of the most common questions of Excel VBA Programmer Interviews. There is a 90% chance that you will be asked this question in Advanced Excel and VBA questions.
I hope you got your solution. If not, do use the comments section to ask a more personalised question.
How to Change The Default Printer Using VBA in Microsoft Excel 2016
How to Display A Message On The Excel VBA Status Bar
How to Insert Pictures Using VBA In Microsoft Excel 2016
How to Add And Save New Workbook Using VBA In Microsoft Excel 2016
How to use the Conditional Formatting using VBA in Microsoft Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.