Examples to Speed Up / Shorten Excel Code

[johnnyL]

I have compiled this bit of info in hopes of helping some people to avoid slow running code and/or shortening their code.

If anybody has more suggestions to offer, please do submit your suggestions, My hopes are to make this a thread for coding tips for shortening/speeding up code.

If you do post a suggestion, please keep it in the format of Example of code and Better Example of code, similar to what I post below.

If I have made any errors, please correct me, I typed a lot of this without testing, ie. thinking out loud.

Not sure if 'sticky' threads are allowed here, but if this thread takes off, perhaps the Mods may consider it.




1) Dim the variables that will be integers as Long instead of as Integer
Example: Instead of
Dim Counter as Integer

Better Example:
Dim Counter as Long ' Long is a 32-bit integer, (rather than 16 bit) which is faster,



2) Try to avoid '.Activate' when using copy/Move/etc.
Example: copies Data from one sheet to another sheet
ActiveWorkbook.Worksheets("Sheet1").Activate
ActiveWorkbook.Worksheets("Sheet1").Rows(j).EntireRow.Copy
'
ActiveWorkbook.Worksheets("Sheet2").Activate
ActiveWorkbook.Worksheets("Sheet2").Rows(j).PasteSpecial
Application.CutCopyMode = False

Better Example:
ActiveWorkbook.Worksheets("Sheet1").Rows(j).EntireRow.Copy ActiveWorkbook.Worksheets("Sheet2").Rows(j)

Maybe Better Example: When Copying more data, bypass the '.copy' all together
ActiveWorkbook.Worksheets("Sheet1").Range("A1:Z100").value = ActiveWorkbook.Worksheets("Sheet2").Range("A1:Z100").value



3) Try to avoid using '.Select/Selection.'
Example: Deletes Cell values in a range
Range("$A$1:$C$20").Select
Selection.ClearContents

Better Example:
Range("$A$1:$C$20").ClearContents



Another Example of '.Select'
Worksheets("sheet1").Select
Amount1 = Cells(1, 1)

Better Example:
Amount1 = Worksheets("sheet1").Cells(1,1)



4) When doing the same thing to multiple ranges, combine the ranges into one line
Example: This code colors 3 different ranges to the same color
Range("H6").Interior.Color = vbWhite
Range("J6:M6").Interior.Color = vbWhite
Range("I7:M7").Interior.Color = vbWhite

Better Example:
Range("H6,J6:M6,I7:M7").Interior.Color = vbWhite



5) Turn off Auto calculation when writing numerous values to cells in Excel, When a new value is entered into a worksheet cell, Excel will recalculate all the cells that refer to it
Example:
' Code that performs a bunch of writes to cells

Better Example:
Application.Calculation = xlCalculationManual ' Turn off the automatic calculation of all cell values
'
' Code that performs a bunch of writes to cells
'
Application.Calculation = xlCalculationAutomatic ' Enable the automatic calculation of all cell values



6) Turn off Screen Refreshing and Event Handling when writing numerous values to cells in Excel, Every time VBA writes data to the worksheet it refreshes the screen image that you see
Example:
' Code that performs a bunch of writes to cells

Better Example:
Application.ScreenUpdating = FALSE ' Turn off the automatic screen refreshing
Application.EnableEvents = False
'
' Code that performs a bunch of writes to cells
'
Application.ScreenUpdating = TRUE
Application.EnableEvents = True



7) Try to avoid obtaining the same values from a worksheet numerous times, Excel code operates much faster when it doesn't need to stop and grab info from a worksheet
Example:
For Counter = 1 to 100
Total = Total + A1
Next

Better Example:
AddToTotalAmount = A1
'
For Counter = 1 to 100
Total = Total + AddToTotalAmount
Next



8) Try to write data to cells in Excel via an array as opposed to individually



Dim myArray() As Variant
'
myArray= Worksheets("Sheet1").Range("A1:Z100").value ' Save the values from range A1 - Z100 to myArray
Worksheets("Sheet1").Range("A1:Z100").value = myArray ' Write the values from the array back to Sheet1



9) Use "Option Explicit" at the beginning of your macro, otherwise, any undefined variable will be a Variant.
Variants are very flexible because they can be numerical or text, but they are slow to process in a formula.



10) Use vbNullString instead of “”, it occupies less memory

 

[diddi]

hi this looks like a good idea, but i think you need to be a bit more formal with the way you have executed it
eg

7) Try to avoid obtaining the same values from a worksheet numerous times, Excel code operates much faster when it doesn't need to stop and grab info from a worksheet
Example:

    Dim Counter as Long, Total As Long
    For Counter = 1 to 100
        Total = Total + Range("A1")
    Next Counter

Better Example:

    Dim AddToTotalAmount as Long, Counter as Long, Total As Long
    AddToTotalAmount = Range("A1")
'
    For Counter = 1 to 100
        Total = Total + AddToTotalAmount
    Next Counter

It would also be a useful addition to run the code and compare the saving as %

 

[johnnyL]

It would also be a useful addition to run the code and compare the saving as %

Are you suggesting use a timer, in addition to each code submission as opposed to the honor system?

When you mention 'formal' I assume you mean more code, such as Dims?

 

[diddi]

slight update:

Sub Example7()
    'Method 1 (slow)
    Start = Timer
   
    Dim AddToTotalAmount As Long, Counter As Long, Total As Long
    For Counter = 1 To 10000000
        Total = Total + Range("A1")
    Next Counter
   
    Cells(2, 1) = Timer - Start
   
    'Method 2 (improved speed)
    Start = Timer
   
    AddToTotalAmount = Range("A1")
    For Counter = 1 To 10000000
        Total = Total + AddToTotalAmount
    Next Counter
    Cells(2, 2) = Timer - Start
End Sub

So the faster code runs 390 times quicker!

 

[johnnyL]

slight update:

Sub Example7()
    'Method 1 (slow)
    Start = Timer
 
    Dim AddToTotalAmount As Long, Counter As Long, Total As Long
    For Counter = 1 To 10000000
        Total = Total + Range("A1")
    Next Counter
 
    Cells(2, 1) = Timer - Start
 
    'Method 2 (improved speed)
    Start = Timer
 
    AddToTotalAmount = Range("A1")
    For Counter = 1 To 10000000
        Total = Total + AddToTotalAmount
    Next Counter
    Cells(2, 2) = Timer - Start
End Sub

View attachment 38094

So the faster code runs 390 times quicker!

Excellent!! You just proved that the 'Better' method in #7 of my OP is much faster!!!

 

[johnnyL]

Keep up the good work!

As a side joke, you didn't Dim Start
LOL

Keep on Rocking!

 

[johnnyL]

Can the Moderators please correct my 2).
It has references to Row J which we know is not possible, the J references should be '10'

Please correct my OP portion of:
2) Try to avoid '.Activate' when using copy/Move/etc.
Example: copies Data from one sheet to another sheet
ActiveWorkbook.Worksheets("Sheet1").Activate
ActiveWorkbook.Worksheets("Sheet1").Rows(j).EntireRow.Copy
'
ActiveWorkbook.Worksheets("Sheet2").Activate
ActiveWorkbook.Worksheets("Sheet2").Rows(j).PasteSpecial
Application.CutCopyMode = False

Better Example:
ActiveWorkbook.Worksheets("Sheet1").Rows(j).EntireRow.Copy ActiveWorkbook.Worksheets("Sheet2").Rows(j)

Maybe Better Example: When Copying more data, bypass the '.copy' all together
ActiveWorkbook.Worksheets("Sheet1").Range("A1:Z100").value = ActiveWorkbook.Worksheets("Sheet2").Range("A1:Z100").value


to ...
2) Try to avoid '.Activate' when using copy/Move/etc.
Example: copies Data from one sheet to another sheet
ActiveWorkbook.Worksheets("Sheet1").Activate
ActiveWorkbook.Worksheets("Sheet1").Rows(10).EntireRow.Copy
'
ActiveWorkbook.Worksheets("Sheet2").Activate
ActiveWorkbook.Worksheets("Sheet2").Rows(10).PasteSpecial
Application.CutCopyMode = False

Better Example:
ActiveWorkbook.Worksheets("Sheet1").Rows(10).EntireRow.Copy ActiveWorkbook.Worksheets("Sheet2").Rows(j)

Maybe Better Example: When Copying more data, bypass the '.copy' all together
ActiveWorkbook.Worksheets("Sheet1").Range("A1:Z100").value = ActiveWorkbook.Worksheets("Sheet2").Range("A1:Z100").value

 

[johnnyL]

I have Timed #7, Original code took 134 seconds
suggested replacement code took .21 seconds

I have timed #2, After the correction of, j to 10, Original code took 69.8 seconds
suggested replacement code took 12.1 seconds

 

[Joe4]

1) Dim the variables that will be integers as Long instead of as Integer
Example: Instead of
Dim Counter as Integer

Better Example:
Dim Counter as Long ' Long is a 32-bit integer, (rather than 16 bit) which is faster,

I do not believe this is true.
I have seen it mentioned numerous times by some of the power programmers that VBA now converts all variables declared as "Integer" to "Long", so it would use the same amount of memory. So declaring a variable as "Integer" would not save you anything.

Also, one of the biggest things that can be done to speed up code is to avoid using Loops whenever possible. Loops are a resource hog, and can really slow down your code, especially if it does thousands (or more) iterations. Many times loops are not necessary at all, and can be avoided or replaced with other options.

 

[diddi]

i have always read the same about integers, but have never benchmarked it.
@Joe4 can you post an example of removing loops in code. i feel i am fairly careful with loops in that i optimise the looping by early exit methods etc, but i cant see how you can remove them. TIA