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.
23 Responses to “Optimization Tips & Techniques for Excel VBA & Macros [Speedy Spreasheet Week]”
A good one for Excel 2010:
Application.PrintCommunication = False
Especially when you're changing page setup properties.
Thanks for the (future) tip! Currently working in 2007, and it's so annoying to see things get slowed down just because I need to change the headers/footers of something.
Reading a range of data into an array, looping through the array and then writing the array back to the range will often be several times faster than looping through a worksheet range cell by cell. Breaking your application down into discrete steps and writing small single-purpose functions that work towards the goal of the application will allow you to focus your optimization efforts on smaller units. While there are good code samples out there, there are also a lot of bad code samples. I tend to avoid websites that have code samples that haven't been updated since Excel 2000.
I know #5 all to well, its not enough to have code that works, it has to be elegant, quick and concise...! I find that the most fun part
I would temper the "Do not write code for lame users" idea, though. You may not need to check to make sure people don't put, say, text into a date field (that would be lame) but even good people do make mistakes. As an example, if an account number being entered should have 8 digits, make a quick check to see if the user entered 8 digits. You won't know if they entered the right value, but you'll catch some data integrity issues, and it's only one line of code.
This next tip doesn't improve macro performance, but it can very much improve your code maintenance performance -- I suggest a fair number of comments. Not too much, maybe one line every 10 lines of code, but each person is different. (That's an overall average for me. I've been known to comment on a single line, or sometimes one comment is fine for an entire routine.) I write them to myself as if I had never seen the code, because I KNOW I will forget what I was thinking a year later!
Reducing the usage of "IF...THEN" statements when setting a Boolean variable.
For example:
If x > 1 Then
MyBoolVar = True
Else
MyBoolVar = False
End If
Can be rewritten
MyBoolVar = x > 1
for all purposes in case of big excel-files I use .xlsb format instead of .xlsx or .xlsm.
.xlsb makes workbooks smaller in its size (1.5 times!!! as Microsoft says) and faster while opening and operating.
It wont speed anything up, but could the following not be cut down?
Range("r2").value = Range("r1").value
This could be more neatly written as
Range("r2") = Range("r1")
Or
[R2] = [R1]
Or are these both slower?
Possibly...but they can mean different things. Suppose R1 contains a formula. Now the first statement just transfers the value, while the latter two would transfer the formula. Defining whether you want the value or not then becomes critical. Not having something specified (value, formula, formular1c1, etc) seems to make things ambiguous, and I try to avoid that, as XL doesn't always "guess" the same way I would like it to.
The article Hui mentioned does show that [R2] = [R1] would be notably slower, so for speed is not the way to go, but I believe you are incorrect in your statement about transfering formula.
.value is the assumed, and isn't required. In all three examples it will not transfer the formula.
I prefer being explicit in code and not to rely on the default property. So I prefer to include Value. [A1]=[B1] is about 15% slower.
You may want to have a read of Daniel Ferry's post at Excelhero.com/blog
http://www.excelhero.com/blog/2010/06/when-working-in-vba-we.html
If there are more than 1 macros that will be used in a huge application, I would RESET all the variables used in the macro, so that it frees up the memory and also reduce the risk of errors, if the variable is shared between 2 macros that can be executed simultaneously.
Just a note, when setting values from one range to another, using .value for certain types (e.g., currency) will truncate the value to the displayed number of digits.
It will drive you crazy.
Use .value2 to get around this.
I would add that optimization should only be performed where there are performace issues. Otherwise the code should be written with readability and maintainability coming first.
If using the macro recorder, go back and get rid of the scrolling lines like: ActiveWindow.SmallScroll Down:=36
[...] multiple properties of a single object is accomplished using With blocks. This is a well known best practice.To get the file extension, we parse the filename. This way, the workbook can be saved in Excel 2003 [...]
Also, it is a good practice is to capture the state of the Application properties (e.g., EnableEvents, ScreenUpdating, etc.) in a bool var, set the desired state, and then revert the state. This is great for calling functions within functions.
Ex:
sub test()
on error goto err_me
with app
ee = .enableevents
if .enableevents then .enableevents = FalseÂ
end with Â
<< code >>
exit_me:
on error resume next
 with app
if .enableevents <> ee then .enableevents = ee
end with
exit sub
err_me:
with err
debug.print .number & " " & .descÂ
.clear
end with
goto exit_me
end sub Â
Many thanks guys!
With following first two tips only, my performance improved from more than 2 hours to less than 5 mins!
Â
You can use dictionaries for a multitude of things. Nothing in the VBA and Excel App is faster in searches.
You can also use the FileSystemObject class to quickly stream Text-type files.
Both are located in the "Microsoft Scripting Runtime" reference.
I have a code in a module, I want to insert a function, but is better put the function inside the sub or leave outside in a function() ??? Breaking in in subs and funtions makes the macro slower?
benefitted a lot from the tips provided here
When the performance is top priority, it may be worth to write the same procedure with different methods and test the speed. There are cases where moving range to array or using Excel native functions are NOT faster solutions. In corporate environment some tools may run hundreds of times per day - these seconds count 😉
I am always assuming my tools will be used by "lame" users. If user can make any error - it will be made sooner or later. If error can generate a real cost for your company - you better have these error handlers working 🙂