Listing Avoiding Select and Selection

Option Explicit

Sub RecorderCode() ' RecorderCode Macro

' Macro recorded 10/12/2003 by Steven M. Hansen

Sheets("Sheet1").Se1ect Co1umns("A:A").Se1ect Selection.Font.Bold = True Sheets("Sheet2").Se1ect Co1umns("B:B").Se1ect Selection.Font.Bold = True Sheets("Sheet3").Se1ect Co1umns("C:D").Se1ect Selection.Font.Bold = True Range("A1").Se1ect Sheets("Sheet4").Se1ect Co1umns("D:D").Se1ect Se1ection.Font.Bo1d = True Range("A1").Se1ect End Sub

' A more efficient version of RecorderCode Sub RecorderCodeII() With ThisWorkbook

.Worksheets("Sheet1").Range("A:A").Font.Bo1d = True .Worksheets("Sheet2").Range("B:B").Font.Bo1d = True .Worksheets("Sheet3").Range("C:D").Font.Bo1d = True .Worksheets("Sheet4").Range("D:D").Font.Bo1d = True End With End Sub

Sub TestProcedures()

Dim dResu1t As Doub1e dResu1t = TestProcedure(1, True) Debug.Print "RecorderCode w/screen updating: " & _ Format(dResu1t, "0.00") & " seconds."

dResu1t = TestProcedure(2, True)

Debug.Print "RecorderCodelI w/screen updating: " & _ Format(dResu1t, "0.00") & " seconds."

dResult = TestProcedure(1, False)

Debug.Print "RecorderCode w/o screen updating: " & _ Format(dResult, "0.00") & " seconds."

dResult = TestProcedure(2, False)

Debug.Print "RecorderCodeII w/o screen updating: " & Format(dResult, "0.00") & " seconds."

End Sub

Function TestProcedure(nVersion As Integer, _ bScreenUpdating As Boolean) As Double

Dim nRepetition As Integer Dim ws As Worksheet Dim dStart As Double

' Set screen updating

Application.ScreenUpdating = bScreenUpdating

' Record the start time dStart = Timer

' Loop through procedure 100 times For nRepetition = 1 To 100 If nVersion = 1 Then RecorderCode

Else

RecorderCodeII End If

Next

' Return elapsed time since procedure started TestProcedure = Timer - dStart

' Make sure ScreenUpdating is on Application.ScreenUpdating = True

Set ws = Nothing

End Function

Running this TestProcedures routine produces the following output on my computer.

RecorderCode w/screen updating: 2.96 seconds. RecorderCodeII w/screen updating: 0.10 seconds. RecorderCode w/o screen updating: 0.45 seconds. RecorderCodeII w/o screen updating: 0.04 seconds.

As you can see, the procedure that doesn't use Select is significantly faster, regardless of the screen updating setting. From a maintenance perspective, which of the two procedures would you rather have in your project?

0 0

Post a comment