Optimization Tips & Techniques for Excel VBA & Macros [Speedy Spreasheet Week]

Optimization Tips & Techniques for Excel VBA & Macros [Speedy Spreasheet Week]:

Is VBA slowing down your workbooks? Do you spend hours starting at Excel while the macros finish running? As part of our Speedy Spreadsheet Week, today lets talk about optimization techniques for Excel VBA & Macros.

Optimization Techniques for Excel VBA & Macros

Lets break this in to 2 sections. (1) Quick optimization techniques & tactics (2) Optimization ideas for the long run

Quick optimization techniques & tactics for VBA & Macros

1. Turn off the features you don’t want before running your macro

This is easy pick. Even while your macros are running, Excel does a lot of work to update the spreadsheet screen, re-calculate the formulas, display alerts etc. You can turn-off these things before running your macro and this would instantly speed up your code.
This is how you can do it:
Application.ScreenUpdating = false

Application.DisplayAlerts = false

and use these lines at the bottom of your code to turn on the alerts & screenupdating.

Application.ScreenUpdating = true

Application.DisplayAlerts = true
Things to remember:

• If your macro changes a part of the spreadsheet (for example animation, chart updation), turning off screenupdating is not the way to go.
• Set calculation mode to manual if it is ok  (application.Calculation =xlCalculationManual)
• Make sure your turn-on everything at the end of your macro.

2. Do not select cells & objects

Range("A1").value = 10 is faster than

Range("A1").select

Selection.Value = 10
Many times, we select cells, objects in our code because the macro recorder produced such code. There is no need to select cell(s) to access or update the value(s).

3. Use built-in functions & features if possible

Do not re-invent the wheel. If there is a built-in formula or function, use it instead of developing your own. Objects like Application have many useful methods that can do what you want. For example, if you want to check if 2 ranges overlap, you an use Application.intersect instead of doing the math yourself.

4. Loop carefully

Some people say avoid loops. I say loop carefully. If you can do the same thing without a loop, do it. Else, make the loop as light-weight as possible. Think thru the problem and see what is the best way to loop. Use below guidelines when you are writing macros with loops:

• To search: Use built in methods like search, find, VLOOKUP, MATCH to find a value in a range instead of looping thru it.
• To copy: Use array to range copy method instead of looping thru the array and copying one element at a time. See example below:
  
  Dim myArr(1 to 1000) as String

'do something and have a lot of values in myArr

'Copy all values in myArr to worksheet range myRange in one step!

Range("myRange").Value = Application.WorksheetFunction.Transpose(myArr)
• To copy: If you want to copy one range of values to another, just assign the values to second range instead of looping. See below:
  
  Range("r2").value = Range("r1").value
• To sort: Use Range.sort method or any other built-in methods to sort if possible.
• Nesting loops: avoid them if you can. Else, take coffee breaks when you run the 14,000 times loop inside 17,345 times loop.

5. Use with block

When you want to do several operations on same object, use With block. This keeps your code clean, tells Excel to cache the object for a bunch of operations.

VBA & Macros – Optimization techniques for long run

Optimization is never ending process. So a good coder constantly learns techniques & follows sound principles to keep her code light-weight & fast.

1. Copy good ideas

There are tons of good code samples, example macro code on various sites, forums or books. Copy any good ideas you come across to speed up your code than trying to re-invent.

2. Divide and conquer

Sometimes a macro is slow because you are trying to do everything in one go. Try doing the task in small chunks. These ideas help:

• Break down your application in to smaller modules / macros.
• Show progress to end users thru a progress bar, frequent screen updates or status messages.
• Render most important aspects of the output first. Then do the rest in background.

3. Less is better

The less code you have, the lesser memory you use, the lesser objects, variables you deal with, the faster your code becomes. As an exercise, take your most complex macro and see if you can delete a line. Repeat this until there is nothing else you can remove. That alone improves the performance. Some ideas to consider:

• Plan your code before you write it. Think thru all steps.
• Do not write code for lame users (unless you are developing something to sell to larger public). Most users in workplace are smart and reasonable. So you can lessen error handling etc.
• Release objects you no longer want to clear memory.
• Negotiate with users and reduce features if possible.

4. Learn & Practice

Merriam-Webster dictionary defines “optimize” as,

to make perfect … more

Learning & practicing is a proven path to perfection. You can learn by examining others code, reading books or helping others. Very soon, you see that your own code becomes better & faster.

5. Know when you cannot optimize

Optimization is like an itch. If you do not resist in time, it consumes you. For most of us running the code in shortest time is not the goal. Our goal is to meet end user needs & get things done. So as long as your code runs fast enough leave it in peace and move to next challenge.
Also, some times no matter what you do, your Excel macro takes time to run. May be its time you considered other languages / tools to solve the problem

More on Excel Optimization & Speeding up:

Read these articles too,

• Optimization & Speeding-up Tips for Excel Formulas
• Charting & Formatting Tips to Optimize & Speed up Excel
• Excel Optimization tips by Experts
• Excel Optimization tips submitted by our readers

How do you speed up your VBA Macros?

Personally, I try to stay away from VBA in my workbooks. But I find that with just a few lines of VBA, we can add a lot of wow factor, convenience to the spreadsheets. So, once in a while I add VBA to make my workbooks even more awesome.  I also use VBA to clean up data, process it or generate reports. In such cases, by using above ideas I saved a lot of time & made my workbooks nimble.
What about you? How do you speed up your VBA & Macros? Please share using comments.

For more information on VBA & Macros

Check out our Excel VBA section or join our VBA Classes online program.