×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs
Home > Forums > Engineering Computer Programs > Engineering Programs > Microsoft VBA - Visual Basic for Applications > FAQs

Microsoft VBA - Visual Basic for Applications FAQ

General Use

Make My Code Run Faster by SkipVought
faq766-2001
Posted: 18 Sep 17

To make a procedure run faster, it is common practice to include a command at the BEGINNING and END of a process

CODE

Application.ScreenUpdating = False
'....Your process here
Application.ScreenUpdating = True

There is another technique that can contribute to faster, more efficient code processing. Most WorkBook, WorkSheet, Range and other Objects Properties & Methods can be referenced without using the Select or Activate Method. The following comparison test will illustrate the point...

CODE

Sub CompareMethods()
    Dim TimeStart As Date, TimeEnd As Date, i As Byte, j As Long
    Application.ScreenUpdating = False
    For i = 1 To 2
        TimeStart = Now
        For j = 1 To 65000
            Select Case i
            Case 1
                Sheet1.Cells(j, 2).Value = j
                Sheet2.Cells(j, 2).Value = j
            Case 2
                With Sheet1
                    .Activate
                    .Cells(j, 2).Select
                    Selection.Value = j
                End With
                With Sheet2
                    .Activate
                    .Cells(j, 2).Select
                    Selection.Value = j
                End With
            End Select
        Next
        TimeEnd = Now
        Sheet1.Cells(i, 1).Value = TimeEnd - TimeStart
    Next
    Application.ScreenUpdating = True
End Sub
Case 1 runs 5 TIMES FASTER than Case 2!

Even a Copy/Paste can be done without Activating and Selecting

CODE

Sub CopyAndPaste()
    Range(Sheet1.Cells(1, 1), Sheet1.Cells(3, 3)).Copy _
        Destination:=Sheet2.Cells(2, 5)
End Sub

The final technique is the use of the With...End With construct. The With statement allows you to perform a series of statements on a specified object without requalifying the name of the object. Running CompareMethods without With...End With, adds about 10% to the run time. Organize your code. Look for opportunities to nest With...End With within With...End With

Happy coding smile

Back to Microsoft VBA - Visual Basic for Applications FAQ Index
Back to Microsoft VBA - Visual Basic for Applications Forum

My Archive


Resources