Procedures that Help Performance of your VBA
The below sub procedures will help your VBA run more efficiently. There are two procedures, and they are simple as can be. One for turning everything off, but then one for turning everything back on. You need to turn everything back on once your main code has run so that things like alerts, calculations and the status bar show back up during normal operation.
There’s also a procedure at the bottom that will help close certain programs that may be running in the background leeching performance from your machine. You can use this to exit any task that may be running in your task list at the time your code executes.
Turning Off, and Back On, Excel Alerts
Below I explain a little bit more about what each command does, and you can pick and choose what to include in your procedure. Some people may prefer to see certain alerts or leave the status bar on. In order to do that, simply remove the statement that correlates to that action.
Application.DisplayAlerts = False
The ‘DisplayAlerts’ command is what controls the pop-up alerts you may get when performing normal operations. Things like “Are you sure…” and “Warning…”.
Application.ScreenUpdating = False
The ‘ScreenUpdating’ command is what helps your program the most. This command basically freezes the screen so that nothing is shown during your procedure. This helps free up memory and processing power because your computer is then not worrying about showing you what’s happening in your program, it’s just doing it!
Application.DisplayStatusBar = False
Like the ‘ScreenUpdating’ above, ‘DisplayStatusBar’ is one of the graphic power-vacuums that excel displays. The status bar is located at the bottom of your sheet and just lets you know what’s going on. While your procedure runs, unless you need to see the status, you can turn this one off.
Application.EnableEvents = False
‘EnableEvents’ is what controls the events that show up when updating things like pivot tables and other structure-like objects in excel and a problem arises. The Events that pop up are usually things that prevent you from moving on during an operation like running out of room on a pivot table or when you attempt to close another workbook without it being saved.
Application.Calculation = xlManual
‘Calculation’ is pretty self explanatory if you’ve been using excel for a bit of time, and this just controls whether calculations in your worksheet formulas happen as things update, or if they wait until your procedure is complete. This is one that you may want to keep an eye on because if your procedure is long and requires information in sheets to be updated periodically, you may want to add in a ‘calculate’ statement mid-way in your code to ensure formulas are accurate.
Now onto the code! Place these procedures in any module and you can refer to them at the beginning and end of any other procedure you are running.
Sub performance_TURNALERTS_OFF() 'Turns Alerts Off Application.DisplayAlerts = False Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlManual Application.DisplayStatusBar = False End Sub Sub performance_TURNALERTS_ON() 'Turns Alerts On Application.DisplayAlerts = True Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlAutomatic Application.DisplayStatusBar = True End Sub
Exit Unwanted Programs To Free Up Memory
At any point while using your machine, there may be a hundred different tasks running in the background. The majority of these are harmless, and usually necessary for the operation of your machine. There may, however, be programs that are running that you really don’t need. I refer to these as “bloat”. Below are just a few examples of some things I turn off before my code runs. This frees up a little bit of memory and sometimes, every bit helps.
On Error Resume Next
If you have yet to read the ‘Error Handling’ section of my site, please check it out. The
above statement is part of the error handling for the below code. This particular statement, while, not something I use on a regular basis, is important for the below procedure. The procedure will run through each line, but if something isn’t open, and you tell it to close it, it will give you an error. So for example, the code I have below is closing “Skype for Business”, “Outlook”, and “Internet Explorer”. But if one of these tasks is not currently present in the task list, your code will throw an error. That’s why it’s important to include this error handling statement in your code for this procedure because then it will just pass over that line and move onto the next.
This statement calls a function to kill the tasks you wish to exit. You can do almost anything you want, but I suggest you at least lookup WHAT the task is to ensure you aren’t going to shut down your machine by closing something important. You can find out the name of the task you wish to close by hitting “CTRL+ALT+DELETE” and clicking on ‘Start Task Manager’ and then clicking on the ‘Processes’ tab. The name you will look for will be in the ‘Image Name’ column.
Public Function TaskKill(sTaskName) TaskKill = CreateObject("WScript.Shell").Run("taskkill /f /im " & sTaskName, 0, True) End Function
Sub performance_KILL_BLOAT() 'This sub procedure will exit any programs that don't need to be running On Error Resume Next TaskKill ("lync.exe") ' Skype for Business TaskKill ("OUTLOOK.EXE") ' Outlook TaskKill ("iexplore.exe") ' Internet Explorer End Sub
So that’s it. What other code do you use to increase the performance of your VBA applications? Leave comments below or email them to me!