select, declaring variables....

[daveyc18]

i'm genuinely curious....does eliminating "selecting" and declaring variables really make a difference with performance nowadays?

sometimes i get this feel people that just say selecting is the root of all evil cause it screams recorded code ....but from my experience (but maybe the macros I use havent been complex enough) it doesn't really make a difference with speed...although admittedly getting rid of "select" does make the code look cleaner

 

[MARK858]

Try running the 2 codes below....

Sub aaa()
    Dim mycell As Range, x As Long
    Dim t As Double
    t = Timer
    x = 1
    For Each mycell In Range("A1:A10000")
        mycell.Select
        Selection.Value = x
        x = x + 1
    Next
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub

Sub bbb()
    Dim mycell As Range, x As Long
    Dim t As Double
    t = Timer
    x = 1
    For Each mycell In Range("B1:B10000")
        mycell.Value = x
        x = x + 1
    Next
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub

 

[daveyc18]

Try running the 2 codes below....

Sub aaa()
    Dim mycell As Range, x As Long
    Dim t As Double
    t = Timer
    x = 1
    For Each mycell In Range("A1:A10000")
        mycell.Select
        Selection.Value = x
        x = x + 1
    Next
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub

Sub bbb()
    Dim mycell As Range, x As Long
    Dim t As Double
    t = Timer
    x = 1
    For Each mycell In Range("B1:B10000")
        mycell.Value = x
        x = x + 1
    Next
    MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub

i see your point...but practical code? although admittedly there are likely instances where you need fill in 10k cells

 

[daveyc18]

what about variables...i removed the declaration and the time was the same

 

[MARK858]

i see your point...but practical code?

Selecting a cell is 5 times slower on average than not Selecting a cell, give an example of what you call "PRACTICAL CODE"?

Practical code to me is any code I need to use.

what about variables...i removed the declaration and the time was the same

Declaring Variables is about the internal processing time and resources and so you are unlikely to see a noticeable time difference unless your code is very complicated.
What does make a difference with declaring your variables and using Option Explicit is detecting when you have misused/misspelt a variable.

http://www.cpearson.com/excel/declaringvariables.aspx

... and Excel can rarely make an error on interpreting what data type your variable should be.

 

[daveyc18]

Selecting five different cells and copying and pasting to other locations is a common thing ....time wise I would think there's no material difference vs just code that doesn't use select .

I have a macro copying and pasting several columns with thousands of rows and tried a few methods such as

Range.select
Selection.copy
Range.select
Selection.paste values
......

Range.copy
Range.paste values

....

Range(a1).copy thisworkbook.sheets(blah).range(a1)

..

Personally didn't find a material difference but that said good point with the timer ..I'll try that

 

[MARK858]

..I'll try that

I would

 

[MARK858]

Btw, one important thing I didn't say about using Select is you can only Select on the Activesheet, so if you are Selecting on different sheets then you have to activate/select the sheet first and that also slows the code down.

 

[footoo]

Compare the following 2 macros.
All they do is copy 30 non-contiguous cells from one workbook and PasteSpecial/Values to another workbook.
Apart from the time difference, the first macro causes screen flickering even although screenupdating is set to false.
With the second macro it is not necessarry to set screenupdating and there is no flicker.

Sub Slct()
Dim r%, c%
Dim t As Double
t = Timer
Application.ScreenUpdating = False
For r = 1 To 30
    c = c + 1
    Workbooks("Workbook2.xlsm").Activate
    Sheets("Sheet1").Select
    Cells(r, c).Select
    Selection.Copy
    Workbooks("Workbook1.xlsm").Activate
    Sheets("Sheet1").Select
    Cells(r, c).Select
    Selection.PasteSpecial Paste:=xlPasteValues, _
         Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next
MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub


Sub NonSlct()
Dim ws2 As Worksheet, ws1 As Worksheet, r%, c%
Set ws2 = Workbooks("Workbook2.xlsm").Sheets("Sheet1")
Set ws1 = Workbooks("Workbook1.xlsm").Sheets("Sheet1")
Dim t As Double
t = Timer
For r = 1 To 30
    c = c + 1
    ws1.Cells(r, c) = ws2.Cells(r, c).Value
Next
MsgBox "Code took " & Format(Timer - t, "0.00 secs")
End Sub