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]

Next debate might be SUM VS SUMIF VS SUMPRODUCT

Examples:
=SUM($A:$A)
=SUMIF($A:$A,">0")
=SUMPRODUCT($A:$A)

Anyone still interested in playing? My money says SUMPRODUCT is last in the results.

Post your test code if you want to play.

 

[MARK858]

Next debate might be SUM VS SUMIF VS SUMPRODUCT

Why would there be a debate? they are 3 different functions, designed to carry out different tasks.
It is like trying to compare a pushbike with a car to a train.

 

[johnnyL]

@MARK858 All 3 of those can be used to perform the same function, In the example I gave, they are used to sum a column of values. So which example is the pushbike as you likened it to?

 

[Fluff]

The example you gave could quite easily give different results.
ergo: They are not the same.

 

[johnnyL]

The example you gave could quite easily give different results.
ergo: They are not the same.

Ok, I did a quick test for what I think will be the ends of the time spectrum... Using SUM or SUMPRODUCT to sum a column totals, both yield same total

Test Timing.xlsm
	A	B	C	D	E	F
1	10
2	20					550
3	30
4	40
5	50
6	60
7	70
8	80
9	90
10	100
11		Total for SUM Function	Total for SUMIF	Total for SUMPRODUCT
12		550		550
13
14		Time for SUM Function	Time for SUMIF	Time for SUMPRODUCT
15		0.553710938		60.47949219
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUMIF($A:$A,">0")
B12	B12	=SUM($A:$A)
D12	D12	=SUMPRODUCT($A:$A)

Which one is the 'pushbike'?

If you are keeping score, the SUM function was 109 times faster than the SUMPRODUCT to sum a column value in this example.

 

[johnnyL]

To be completely fair, SUMPRODUCT is at a disadvantage. It is not smart enough to recognize the range end. Thus the major time difference in the testing. If a more precise range is provided, the differences in time are fairly minimal between the sum variations.