17 ways to Optimize VBA Code for FASTER Macros

On the eve before Christmas, I'm writing this article on VBA code optimization for my blog readers as a gift. It contains both the knowledge and good practices which if followed and used effectively then it might put you in elite category of excellent VBA programmers. VBA programs are known to save a lot of time and now this post is to save further time. Read them carefully and open new pathways of innovation in creating excel dashboards, reports and automation. Wish my readers a very MerRy ChRisTMaS !

Here is a summary of the article:
1. Analyze the Logic                                          2. Turn off ScreenUpdating
3. Turn off 'Automatic Calculations'                   4. Disable Events
5. Hide Page breaks                                           6. Use 'WITH' statement
7. Use vbNullString instead of ""                         8. Release memory of Object variables
9. Reduce the number of lines using colon(:)     10. Prefer constants
11. Avoid Unnecessary Copy and Paste               12. Clear the Clipboard after Paste
13. Avoid 'Macro Recorder' style code.               14. Use 'For Each' than 'Indexed For'
15. Use 'Early Binding' rather 'Late Binding'         16. Avoid using Variant
17. Use Worksheet Functions wherever applicable

Do read full article to understand the logic behind them.



Analyze the logic: Before optimizing the syntax, pay more attention in optimizing the logic. Without a good logic, a good written VBA macro program has no value. So streamline your program logic and get the best performance of macros.

Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.
The ScreenUpdating property controls most display changes on the monitor while a procedure is running. When screen updating is turned off, toolbars remain visible and Word still allows the procedure to display or retrieve information using status bar prompts, input boxes, dialog boxes, and message boxes. You can increase the speed of some procedures by keeping screen updating turned off. You must set the ScreenUpdating property to True when the procedure finishes or when it stops after an error. Refer MSDN for details.

Turn off automatic calculations: Whenever content(s) of a cell or range of cells are changed, the formulas dependent on them and Volatile functions are recalculated. You may turn off the automatic calculation using
Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation
Now, whenever due to the program logic(that due to macros dependent on existing formulas) you need to calculate the formulas, you may use the following code accordingly.
ActiveSheet.Calculate ' To calculate the formulas of Active Worksheet
Application.Calculate ' To calculate the formulas of Active workbook or all workbooks in current application.

Stop Events: Use Application.EnableEvents to tell VBA processor whether to fire events or not. We rarely fire an event for each cell we're changing via code. Hence, turning off events will speed up our VBA code performance.

Hide Page Breaks:When we run a Microsoft VBA macro in a later version of Microsoft Excel, the macro may take longer to complete than it does in earlier versions of Excel. For example, a macro that required several seconds to complete in an earlier version of Excel may require several minutes to complete in a later version of Excel. This problem may occur if the following conditions are true:
* The VBA macro modifies the properties of many rows or columns.
* An operation has been performed that forced Excel to calculate page breaks. Excel calculates page breaks when we perform any of the following operations:
o We display a print preview of your worksheet.
o In Microsoft Office Excel 2003 and in earlier versions of Excel, we click Page Setup on the File menu.
o We modify any of the properties of the PageSetup object in a VBA macro.
* In Excel 2003 and in earlier versions of Excel, we selected the Page breaks check box on the View tab of the Options dialog box.
Solution: is to disable Page breaks using ActiveSheet.DisplayPageBreaks = False
Refer to this Microsoft article for more details - http://support.microsoft.com/kb/199505

Use 'WITH' statement when working with objects: If we have to access an object's properties and methods in several lines, we must avoid using object's name or fully qualified object path again and again. It is annoying for VBA processor as it needs to fully qualify the object each time. (Isn't it annoying for us too when some work or something is told us again and again? Got it Guys !
SLOW MACRO

FAST MACRO

Sheets(1).Range("A1:E1").Font.Italic = True
Sheets(1).Range("A1:E1").Font.Interior.Color = vbRed
Sheets(1).Range("A1:E1").MergeCells = True
With Sheets(1).Range("A1:E1")
    .Font.Italic = True
    .Font.Interior.Color = vbRed
    .MergeCells = True
End With
The point here to understand is minimum qualifying of an object by VBA processor. i.e. using minimum dots/periods(.) in the code. This concept tells us to use [A1] rather than Range("A1") and Range("StockRange")(3,4) rather than Range("StockRange").Cells(3,4)


Use vbNullString instead of ""(2 double quotes) : vbNullString is slightly faster than "", since vbNullString is not actually a string, but a constant set to 0 bytes, whereas "" is a string consuming at least 4-6 bytes for just existence.
For example: Instead of strVariable = "", use strVariable = vbNullString.

Release memory from object variables: Whenever we create an object in VBA, we actually create two things -- an object, and a pointer (called an object reference). We might say, "VB does not use pointers", but it is not true. "VB does not let you manipulate pointers" is more precise. Behind the scenes, VB still makes extensive use of pointers. To destroy an object in VB, you set it to Nothing. But wait a minute. If all we ever use are object pointers, how can we set the object itself to Nothing? The answer is: We can't.
When we set a reference to Nothing, something called the garbage collector kicks in. This little piece of software tries to determine whether or not an object should be destroyed. There are many ways to implement a garbage collector, but Visual Basic uses what is called the reference count method.
When VB interprets the last line(where we generally sets our objects to Nothing), it will remove the existing reference. At this point, if the object has no more references, the garbage collector will destroy the object and deallocate all its resources. If any other references point to the same object, the object will not be destroyed.

Reduce the number of Lines: Avoid multiple statements especially when they can be clubbed into one line. For example - See these 2 macros
SLOW MACRO

    With Selection
        .WrapText = True
        .ShrinkToFit = False
    End With
FAST MACRO


    With Selection
        .WrapText = True: .ShrinkToFit = False
    End With
As you can see, you can club multiple statements into one using colon character(:). When you do this with multiple statements, it will decrease the readability but will increase the speed.
Compiler Logic: When we save the macro, it is virtually compiled and unlike it's human readable form as we saw in VBE(Visual Basic Editor), keywords(the dark blue words which you cannot use as variable) are saved as three-byte token which are faster to process as machine understand them better and variables, comments and literal strings which are not either keyword or directive are saved "as is". However VBA compiler tokenizes the word but it does not compress the lines and each line is maintained as is ending with the 'Carriage Return'. When the VBA macro is executed, VBA processor fetched single line at a time. The tokens of the fetched line saved by virtual compiler are now interpreted and executed then next line is fetched and so on. When we combine multiple lines using colon into one line, we're reducing the number of fetch cycles the processor must go through.
Our Suggestion: This change will bring minor difference in time due to faster processors today. Moreover, you cannot have more than 255 characters in a line and you won't be able to debug your code using F8 efficiently. So it's a kind of useless, there is no reason to trade-off with readability with such a minor change in time.

Declare variable as Variable and constant as Constant: Seems, obvious ! But many of us don't follow it. Like
      Dim Pi As Double
      Pi = 3.14159
instead use
      Const Pi As Double
      Pi = 3.14159
Since, its value is never changed so it will be evaluated once during compilation unlike variable which are evaluated many times during the run-time.

Avoid Unnecessary Copy and Paste: Follow this table rules:
Instead ofUse this:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial
Application.CutCopyMode = False   
'Clear Clipboard
'Bypass the Clipboard
Sheet1.Range("A1:A200").Copy Destination:= Sheet2.Range("B1")
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial xlPasteValues
Application.CutCopyMode=False
'Clear Clipboard
'Bypass the Clipboard if only values are required
Sheet2.Range("B1:B200").Value = Sheet1.Range("A1:A200").Value
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").PasteSpecial xlPasteFormulas
Application.CutCopyMode=False
'Clear Clipboard
'Bypass the Clipboard if only formulas are required
Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula

'Same can be done with FormulaR1C1 and Array Formulas.

Use Worksheet Functions rather developing own logic: By using Application.WorkSheetFunction, we tell VBA processor to use native code rather than interpreted code as VBA understands the worksheet functions better than your algorithm. So, for example use
      mProduct = Application.WorkSheetFunction.Product(Range("C5:C10"))
rather than defining your own logic like this:
      mProduct = 1
      For i = 5 to 10
            mProduct = mProduct * Cells(3,i)
      Next

Use 'For Each' rather than 'indexed For': We can avoid using Indexed For when looping through collections. For example, take the code just before this tip. It can be modified to:
      For Each myCell in Range("C5:C10")
            mProduct = mProduct * myCell.Value
      Next
This is in relation to qualifying object again and again as using "WITH" statements.

Avoid using 'Macro Recorder' style code:Ya, the code will look genius and eventually perform like Genius too ! You'll better catch it with example, so use:
      [A1].Interior.Color = vbRed
rather than
      Range("A1").Select
      Selection.Interior.Color = vbRed
Using too many Select and Selection effects the performance drastically. Ask yourself why to go in Cell and then change the properties? or rather ask why to go pizza shop when you can enjoy it at your home ;)

Avoid using Variant and Object in declaration statements: Think about better logic and get rid of them. i.e. do not use Dim i As Variant or Dim mCell As Object. By trying to be specific,we will save a lot of system memory this way, particularly in case of large projects. We may not remember which has been declared variant above and misuse a variable assigning any value to it which will be type-casted without errors. A variant's descriptor is 16 bytes long while double is 8 bytes, long is 4 bytes and integer is 2 bytes. Hence use Variant cautiously. As an example, use:
      Dim i As Long rather than Dim i As Variant
Similarly use:
      Dim mCell As Range 'or
      Dim mSheet As Worksheet
rather than
      Dim mCell As Object 'or
      Dim mSheet As Object

Declare OLE objects directly: Declaring and defining the OLE objects in declaration statement is called 'Early Binding' but declaring an object and defining it later is called 'Late Binding'. Always prefer 'Early Binding' over 'Late Binding'. Now for example use:
      Dim oXL As Excel.Application
rather than
      Dim oXL As Object
      Set oXL = CreateObject("Excel.Application")


Comments

Prashant said…
I'm regular reader of your blog and it helped me in my MIS job a lot ! Thanks again for such a nice article.

Regards
Your Fan
Anonymous said…
loved the deep insights of VBA programming provided here.....
Ashish Jain said…
Thanks, my readers loved it - that's my Christmas gift :)
Paul Kempinski said…
for sure, these are good programming practices and many of them saves significant time. It's good to see all of them together.
Anonymous said…
Complimenti!
Articolo fatto molto bene!
Mi รจ tornato utile

Daniela
Ashish Jain said…
Grazie Daniele - Il mio piacere che i miei lettori piace.

Ci dispiace, se erroneamente ha detto niente di male. Io non sono molto brava in italiano.
Vinay Gupta said…
I bookmarked your blog, its worth reading. Many thanks
Anonymous said…
very elaborative
Anonymous said…
A very good article, thank you. Can you give more detail on why early binding is preferred over late? I find that using late binding, when say automating Excel from Access, prevents lingering excel processes that early binding does not. Any thoughts on this?
Emil C said…
Coping without clipboard works like a charm. Clipboard was trowing lots of junk from time to time. Thanks !
Anonymous said…
Hi,

Thanks for the great tips. totally speed up my data processing.

Just wondering, is there any way to optimize the script to copy data from different workbooks, and at the same time avoid using the clipboard?

Thanks
Anonymous said…
Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.


Is there a typo for turning it on?
khanh2e said…
What a fantastic post !
Thanks a lot.
Anonymous said…
Anon ... early-binding helps set aside the memory for the object. Late binding can be useful if you have some code that may not even need the object.

EG: I have an Access db that cranks through sql statements (via SQL Server), and if any sql statement has some results (records) then I need them to dump to excel as a spreadsheet and save.

If I have 50 sql statements to crank through, and I know several will return results (eg: maybe a few are daily reports pulling #'s), then I can early-bind the xl object (dim xl as Excel.Application). At the end of processing, I just need to close xl (xl.quit) and then set the xl object to nothing (set xl = nothing). This destroys the object, and should get rid of the lingering processes you were seeing.

However, if I rarely get any results on the sql files I run, there's no reason to have the xl object sitting there tying up memory if it won't get used.

So, you could change your code to only have the xl object declared if the xl code is actually needed / used. If the sql statements run and no results show up (hence the xl sub / func never gets called), then the xl object never gets created.

However, with xl, if you know you're going to use it, it's easier to just open it one time and keep it open. It takes like 3 seconds to open. So, if you have, eg: the sql grinder I have, it would be very inefficient for the xl sub/func to constantly open & close xl each time it needs to run the results of a sql statement into a spreadsheet. So, I just open it once at the start of the grinding, and then when the xl sub/func needs to run it just creates a new workbook, dumps the results into the default worksheet that comes with a workbook, saves it, and closes that workbook (but still keeps excel open). Next time a result comes back, xl is still open, just make a new workbook, wash-rinse-repeat, until everything is done. Then just have the xl.quit and set to nothing as part of a cleanup job at the very end.
Unknown said…
2nd one should be "True" not false

Avoid 'Screen Flickering' or 'Screen Repainting': Use
Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = False 'To Turn on at the end of the code.

Thanks


TheSmileyCoder said…
Thank you for your tips. Used the bit about copying values to shave a 9 second operation to a .5 second operation. EXCELLENT :)
Anonymous said…
for "Avoid Unnecessary Copy and Paste", is it possible to use variable for the cells in the formulas. I tried using this formula but it is resulting in an error:

Sheet.Range(Cells(r, c)).Value = Sheet.Range("AB" & r).Value

I believe the error is in the "Cells" part but I cannot make it work.

Thank you!
Unknown said…
For the "Avoid Unnecessary Copy and Paste", is it possible to use variables for the cells/range? My current formula is this but it takes ages to process my file which might have 50,000 rows:

r = 2

Do Until IsEmpty(Cells(r, 3))
c = 29

Do Until IsEmpty(Cells(r, c))

'This duplicates the row
Cells(r, 1).EntireRow.copy
Cells(r, 1).Select
Selection.Insert Shift:=xlDown

'This copies an identifier to a column
Cells(r, c).Select
Application.CutCopyMode = False
Selection.copy
Cells(r, 28).Select
ActiveSheet.Paste

c = c + 1
r = r + 1
Loop

r = r + 1
Loop

Any help you can give will be greatly appreciated!
Anonymous said…
Great tips, another amazingly simple method of speeding up excel VBA code is to remove the program to module interaction by setting up your module to declare the working sheet inside of a variant array.

Example:

'I use variant array here so that the data types on the page will be captured
'note rich text formatting will not be captured
dim r
dim i as long, j as long

r = ActiveSheet.UsedRange

for i = lbound(r) to ubound(r)
for j = lbound(r,2) to ubound(r,2)
'do something with your cell here
cstr(r(i,j))
next j
next i

ActiveSheet.UsedRange = r
CK said…
for a realistic application of my previous anonymous post:
compare the api version of 1,000,000 random numbers to the array

non-api call runs in .4 seconds and the api version runs in 12.9 seconds micro optimization counts big for large operations if the developer can wrap their minds around arrays, tip use the locals window and breakpoints in the module to view your variables and debug effectively.

Note that minimizing the application.screenupdating feature helps by removing 1.1 seconds from the total api version of the procedure, just ensure that there is error handling in the procedure that will turn the application.screenupdating back to true Practical application: On Error GoTo errHandler 'at the beginning of module errHandler: Application.ScreenUpdating = True 'at the end of module

Option Base 1
Sub RandomNumbers()

Dim r(1000, 1000) As Double
Dim i As Long, j As Long

For i = 1 To 1000
For j = 1 To 1000
r(i, j) = Rnd
Next j
Next i

Range(Cells(1, 1), Cells(1000, 1000)) = r


End Sub

Sub RandomNumbersAPIform()


Dim i As Long, j As Long

For i = 1 To 1000
For j = 1 To 1000
Cells(i, j) = Rnd
Next j
Next i

End Sub