VBA- Improve Your Code Execution Time

Improve VBA Code Execution Time
You’ve just completed a VBA Program to automate a task that your office has done manually forever.  You are feeling pretty good about your accomplishment and you should.  Push the button and watch the screen jump around and do all the work that used to take forever.  It gets the job done but, you wonder is it as good as it could be?  The answer is most likely no.  The problem is how we tend to learn Excel VBA- by using the ‘record a macro feature’ which duplicates key strokes that we did in the past to manually update our spreadsheet.  In Excel we are visual users – we travel to cell A66 by using the down arrow or perhaps pagedown key and then update the cell.  Most of us don’t even use the F5 go to feature.  Your computer can update cell A66 without you traveling there. In lieu of traveling to A66 and making it the active cell, we could say with code both get the job done and eliminate that unnecessary travel time.

Just how much time does all that travel time add up to? I’ve written two little routines below to test it.  Please see my video below where I demonstrate the code.

This simple VBA program below starts at cell A2 and travels down column A and sets the value of the current cell to 1 plus the cell before it- for 5,000 times. It records the start time and the end time and pops up a message box indicating how long the VBA code took to execute.

Sub MoveExample()
' How to improve execution time
' this simple example moves to each cell ...


Dim TimeStart As Date, TimeEnd As Date
Dim i As Integer
Dim sMsg As String

TimeStart = Now
For x = 1 To 5000
ActiveCell.Value = 1 + ActiveCell.Offset(-1, 0).Value
ActiveCell.Offset(1, 0).Select
Next x
TimeEnd = Now

i = DateDiff("s", TimeStart, TimeEnd)
sMsg = "Elapse time = " & i & " seconds"
MsgBox sMsg, vbInformation, sMsg


End Sub

In this case it ran for 17 seconds.  In today’s world 17 seconds is an eternity.

Improve VBA Code Execution Time
Below I’ve rewritten the VBA code to avoid travel and used the Cells object to affect the same changes for the same 5000 cells.   
Sub RefertoExample()
' this simple example refers to each cell ...
' using the cells object...


Dim TimeStart As Date, TimeEnd As Date
Dim i As Integer
Dim sMsg As String

TimeStart = Now
For x = 2 To 5000
Cells(x, 2) = 1 + Cells(x - 1, 2)
Next x
TimeEnd = Now

i = DateDiff("s", TimeStart, TimeEnd)
sMsg = "Elapse time = " & i & " seconds"
MsgBox sMsg, vbInformation, sMsg


End Sub
Improve VBA code execution times

You will note the dramatic improvement in execution time.  This version of code executed in under 1 second.  In fact if you turn screen updating off (application.screenupdating = off) it will appear instantaneous to the user.  I doubt anything could be more impressive.

Improve VBA Code Execution Time:
If you were able to utilize what you learned here or improve the code, please leave a comment Here

If you enjoyed this post or found it helpful andl would like to say hello and buy me a cup of coffee
Click here

Raymond Mills MBA, MS

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle, Sequel Server and Sybase to give up their secrets.

Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” If you have a challenge with Excel, Access or Word and would like to speak with Ray, you can get his contact details by clicking here: 
Contact Me 

Comments are closed.