Improve your VBA processing time

If you have a routine process that you are doing in Excel, using VBA is a great solution get things done faster. VBA allows you to automate these routine processes. The trick is that most of us learn how to write in VBA using Record Macro (on the Developer tab of Excel) and just copy/alter the code as necessary for the next project. While Record Macro is a great tool, it does write most of its code in an inefficient way. Here are some tips that I have learned over the years to speed up your macros and improve your VBA processing time.

1. Remove as many “.Select” statements as you can.

When using Record Macro, Excel adds a .Select statement every time you click on a cell. Most of these statements are unnecessary.

2. Change the way you copy and paste.

Similar to the .Select, most of us learn to copy and paste using Record Macro. While the code Excel uses works, it is slow. Here is an example of Record Macro copy and paste:

          Range(“A1:B2”).Select

          Selection.Copy

          Range(“G7”).Select

          ActiveSheet.Paste

Instead rewrite the code to:

          Sheets(“Sheet1”).Range(“A1:B2”).copy Sheets(“Sheet1”).Range(“G7”)

You can also use a similar structure if you want to do paste special.

You’ll notice that I added the sheet for the cell. This is not a speed tip, but it does make the code easier to read and prevents accidents when you run the code if you are on a different tab of the worksheet than you intended.

3. Use counters.

To automate processes, you often run into the dilemma of various sizes of files from day to day. To combat this varying size, I use counters. Here is a fast counter I like to use:

          i = 1

          Do While True

                    If (Sheets(“Sheet1”).Range(“A” & i + 1) = “”) then

                              Exit Do

                    Exit If 

                    i = i + 1

          Loop

You start the counter at the first line of data (“A1” in this example), and the loop will continue until the next cell has no data. This will give you the length of the dataset so you can use an exact range rather than a range “that will always work”. 

If this counter does not work for what you are doing, there are several different examples online.

4. Start your macro with Application.DisplayAlerts = False and end your macro with Application.DisplayAlerts = True.

By setting the display alerts to false you are asking Excel to suppress prompts and alert messages while a macro is running. This allows your code to run without any pausing and waiting for you to respond. I typically add this line of code at the end of my processing at least until I make sure that the prompts are no longer necessary. Excel will automatically switch display alert equal to true once the macro is complete. I still like to add the true statement to the end of my code just in case Microsoft decides to change this automatic setting.

I hope that these tips help you improve your VBA processing time. 

If you’d like to learn more or have BTG assist with an upcoming project, please contact us at info@bluetridentgroup.com or book a free consultation here.