A common problem achieving full VBA and Macro automation with Excel is that the warning messages can halt your script in the middle of processing. Learn how to stop that problem and achieve full Excel automation with VBA…
Excel is pretty smart in that it will inform you when an action is going to change something significant in your workbook. This is usually a great safety feature to prevent you from making mistakes but there are time when you are automating a process and ideally you do not want Excel to keep prompting you during this.
For example, a simple bit of VBA script to delete a worksheet called “Data” from a workbook would be written like below:
1 2 3
Sub deletesheet() Sheets("Data").Delete End Sub
But when you run this VBA script you will get the Excel alert:
And that means you have to stop what you are doing and click on “Delete” for the code to complete, sure this is not a major task but this is a simplified example. Imagine if you have a macro that runs for 20-30minutes and instead of being able to leave alone you have to sit and wait for multiple messages like this.
The solution is a piece of code that needs to be included in your visual basic script.
You need to insert the following line at the beginning of your procedure to ensure that right from the get-go Excel does not display any warning messages or alerts:
Application.DisplayAlerts = False
So in our simple example above the VBA script now becomes:
1 2 3 4 5 6 7
Sub deletesheet() Application.DisplayAlerts = False Sheets("Data").Delete End Sub
What does this do?
The parts of the code can be broken down like follows:
- Application: This tells Excel that we are referring to the Excel Application.
- DisplayAlerts: This tells Excel that we are referring to the Display Alerts option within the Excel application.
- = False: This tells Excel that we want to switch alerts off, common practice with VBA is to set something to false. If we set to True it turns them on.
Do you need to switch displayalerts back on?
There is no need to switch the alerts back on at the end of the procedure. A lot of people will do this and in a way it is good practice and keeps the script clean by inserting the line
Application.DisplayAlerts = True
right at the end of your script, but it is not needed. Excel will switch alerts back on once your procedure has completed by default.
To achieve full Excel automation using VBA you will often find that the use of Application.DisplayAlerts = False is necessary in your VBA script.
Always insert at the beginning of the script if you are trying to achieve full Excel automation with VBA as otherwise you might get some initial alerts that need attention.
There is no need to switch back on at the end of the procedure, Excel does this for you, however it can sometimes be worth adding in-case it confuses less experienced Excel analysts.
Finally make an effort to test your scripts before including this line. You should always test the script first to understand what alerts pop-up and whether or not they are issues that need to be addressed. Only when you are satisfied that the script works as intended should you switch the alerts off.
Now you are one step closer to achieving Excel automation with VBA!