How can I improve the performance of my VBA code?

frye_user_error_616-300x219
User Error? Never…

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

OffOnMeme
I maintain that 90 percent of the worlds problems can be solved by simply turning things off and back on again.

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.

Commands

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.

The Code

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.

Commands

On Error Resume Next

If you have yet to read the ‘Error Handling’ section of my site, please check it out. The

KillMeme
Not going to lie, I laughed hard at this one…

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.

TaskKill

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.

The Code

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!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s