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

 

[johnnyL]

i have always read the same about integers, but have never benchmarked it.

I have opened a new thread Here to hopefully put that debate to rest. Integer and Long ARE HANDLED DIFFERENTLY, Integer is not converted to Long, despite what you may have read on the internet.

 

[footoo]

@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

Here's an example.
Your Example 7 Code :

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

Avoiding loop :

Total=Range("A1")*100

 

[johnnyL]

I have opened a new thread Here to hopefully put that debate to rest. Integer and Long ARE HANDLED DIFFERENTLY, Integer is not converted to Long, despite what you may have read on the internet.

Upon further investigation, my previous statement of "Integer is not converted to Long" may not be 100% correct. Upon my most recent readings, a declared 'Integer' may be temporarily converted to 'Long' during proccessing, but is converted back to 'Integer' afterwards. This would explain why 'Overflow' errors can occur when a declared 'Integer' value is too large.

I'm not saying that this temporary conversion, from Integer-to Long-Back to Integer, does indeed occur, but the apparent end result is that 'Integer' ends up as 'Integer' and any converting from 'Integer' to 'Long' and then back to 'Integer' would have to slow performance down when done enough times.

 

[johnnyL]

Here's an example.
Your Example 7 Code :

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

Avoiding loop :

Total=Range("A1")*100

Your 'Avoiding loop' submitted code doesn't do the same thing. In this example, your code would yield the same result, but the theory could be easily thwarted.

    AddToTotalAmount = A1
'
    For Counter = 1 to 100
        Total = Total + AddToTotalAmount
        Total = Total / 2
    Next

 

[diddi]

@footoo well thats not really what i meant. its pretty obvious that anyone in their right mind would not do repetitive addition for multiplication. that example7 code was to demonstrate the slowing effect of using cell values rather than variables.

how about a real example

 

[johnnyL]

And here is some code to test Dimming as an Integer vs Dimming as Long:

' Test Dim as Integer
'
    Dim Counter As Integer
'
    Start = Timer
'
    For h = 1 To 30000
        For Counter = 1 To 32766
            i = i + Counter
        Next
    Next
'
    Cells(2, 1) = Timer - Start        ' Reports 53.16015625

and:

'Test Dim as Long
'
    Dim Counter As Long
'
    Start = Timer
'
    For h = 1 To 30000
        For Counter = 1 To 32766
            i = i + Counter
        Next
    Next
'
    Cells(2, 2) = Timer - Start        ' Reports 45.2197265625 which = a 17.5% increase in speed by using Long as opposed to Integer

17.5% faster using the same variable as a Long Data Type.

 

[footoo]

@footoo well thats not really what i meant. its pretty obvious that anyone in their right mind would not do repetitive addition for multiplication. that example7 code was to demonstrate the slowing effect of using cell values rather than variables.

how about a real example

I'm sure you can easily find plenty of real examples from a simple search of the internet or even this board.
For example : How to check if a specific range of cells have any blank cell?

 

[diddi]

oh yes. thats a better example. looping would be very slow for something like that

 

[Joe4]

Yes, there are various different examples when people use loops when they are not necessary.
Examples of that include:
1. When they want to apply the same formula to every cell in a column, and they use a loop instead of applying the formula to the whole range at one time.
2. Looping through each row in a range, and hide rows meeting/not meeting a certain condition, when this can easily be accomplished with a simple filter.

 

[Joe4]

The Intger/Long Integer situation is a bit more complicated than I first stated, and can be a bit confusing.
Here is a good discussion on it here: Why Use Integer Instead of Long?