offthelip
08-04-2017, 07:19 AM
I have been writing code for a lifetime and using Excel VBA for 20 years, however it is only recently after joining this forum that I came across the idea that using select was bad idea.
I can certainly agree that using select in a poor way can make macros very slow indeed. However I have just been trying to see what the difference in timing was between the way I tend to write my code and a common method which avoids using select.
My method of getting my macros to run as fast as possible is to load all of the data from any number of worksheets into memory, then do the calculations in memory and then paste them back .
Note: I have still had macros that take hours to run usually because of multiple loops which can end up with millions of iterations
I had this feeling that operating on variant arrays in memory was going to be a lot faster than operating on range variables. This proved to be correct in my test. Selecting the worksheet and loading a variant array took 0.34 seconds.
Using methods of setting a variable to worksheet and range took 18.12 seconds that is over 50 times as long.
So my question is how would you alter my code to get it to run faster or is the best way of doing what I am doing which is selecting the worksheet to load the variant array? Is there another way of loading a variant array without selecting or activating the worksheet?
The slow routine:
Dim SecondsElapsed As Double
Worksheets("Sheet1").Select
StartTime = Timer
Dim ws As Worksheet
Dim rng2 As Range
Set ws = Worksheets("Sheet2")
lastcol2 = 100
lastrow2 = 5000
Set rng2 = ws.Range(ws.Cells(1, 1), ws.Cells(lastrow2, lastcol2))
For i = 1 To 5000
For j = 1 To 10
rng2(i, j) = i + j
Next j
Next i
For i = 1 To 5000
For j = 1 To 10
rng2(i, j) = rng2(i, j) + 1
Next j
Next i
SecondsElapsed = Round(Timer - StartTime, 2)
'MsgBox SecondsElapsed
Cells(1, 1) = "Setting worksheet variable"
Cells(1, 2) = SecondsElapsed
End Sub
the fast routine:
Sub speedtest()
Dim StartTime As Double
Dim SecondsElapsed As Double
Worksheets("Sheet1").Select
StartTime = Timer
Dim var2 As Variant
Worksheets("Sheet2").Select
lastcol2 = 100
lastrow2 = 5000
var2 = Range(Cells(1, 1), Cells(lastrow2, lastcol2))
For i = 1 To 5000
For j = 1 To 10
var2(i, j) = i + j
Next j
Next i
For i = 1 To 5000
For j = 1 To 10
var2(i, j) = var2(i, j) + 1
Next j
Next i
Range(Cells(1, 1), Cells(lastrow2, lastcol2)) = var2
Worksheets("Sheet1").Select
SecondsElapsed = Round(Timer - StartTime, 2)
'MsgBox SecondsElapsed
Cells(2, 1) = "Selecting worksheet"
Cells(2, 2) = SecondsElapsed
End Sub
I can certainly agree that using select in a poor way can make macros very slow indeed. However I have just been trying to see what the difference in timing was between the way I tend to write my code and a common method which avoids using select.
My method of getting my macros to run as fast as possible is to load all of the data from any number of worksheets into memory, then do the calculations in memory and then paste them back .
Note: I have still had macros that take hours to run usually because of multiple loops which can end up with millions of iterations
I had this feeling that operating on variant arrays in memory was going to be a lot faster than operating on range variables. This proved to be correct in my test. Selecting the worksheet and loading a variant array took 0.34 seconds.
Using methods of setting a variable to worksheet and range took 18.12 seconds that is over 50 times as long.
So my question is how would you alter my code to get it to run faster or is the best way of doing what I am doing which is selecting the worksheet to load the variant array? Is there another way of loading a variant array without selecting or activating the worksheet?
The slow routine:
Dim SecondsElapsed As Double
Worksheets("Sheet1").Select
StartTime = Timer
Dim ws As Worksheet
Dim rng2 As Range
Set ws = Worksheets("Sheet2")
lastcol2 = 100
lastrow2 = 5000
Set rng2 = ws.Range(ws.Cells(1, 1), ws.Cells(lastrow2, lastcol2))
For i = 1 To 5000
For j = 1 To 10
rng2(i, j) = i + j
Next j
Next i
For i = 1 To 5000
For j = 1 To 10
rng2(i, j) = rng2(i, j) + 1
Next j
Next i
SecondsElapsed = Round(Timer - StartTime, 2)
'MsgBox SecondsElapsed
Cells(1, 1) = "Setting worksheet variable"
Cells(1, 2) = SecondsElapsed
End Sub
the fast routine:
Sub speedtest()
Dim StartTime As Double
Dim SecondsElapsed As Double
Worksheets("Sheet1").Select
StartTime = Timer
Dim var2 As Variant
Worksheets("Sheet2").Select
lastcol2 = 100
lastrow2 = 5000
var2 = Range(Cells(1, 1), Cells(lastrow2, lastcol2))
For i = 1 To 5000
For j = 1 To 10
var2(i, j) = i + j
Next j
Next i
For i = 1 To 5000
For j = 1 To 10
var2(i, j) = var2(i, j) + 1
Next j
Next i
Range(Cells(1, 1), Cells(lastrow2, lastcol2)) = var2
Worksheets("Sheet1").Select
SecondsElapsed = Round(Timer - StartTime, 2)
'MsgBox SecondsElapsed
Cells(2, 1) = "Selecting worksheet"
Cells(2, 2) = SecondsElapsed
End Sub