Tuesday, March 24, 2015


WOW!  I just found a nice little help that improved my vba run time from 30 minutes to less than 1 minute!

So, I had a bit of VBA that I wrote to clean up and group a transaction level data set from a 3rd party report.  I noticed that on average it was taking up to FOUR seconds per row to clean, identify, re-organize, validate for duplicate entries, and write to a new table.  Essentially, 600 rows would take at least 30 minutes for the vba code to complete its task.  I have NEVER run into this issue before - Turning off Screen Updating has resolved any issues with speed I've had up until this point.

30 minutes just won't do.

So, I went hunting for solutions.  But, before I can solve the problem, I had to FIND the problem.

What is the problem?  Well, I found through the use of breakpoints where the lag in time was.  It was taking up to 3 seconds to simply place a batch of values that were stored in variables into empty cells. 

example line: 

Sheets("Converted").Cells(Rowc, 1).Value = Location

The issue was that Excel was recalculating the sheet every time a new value was placed in a cell.  So, how do we stop that? 

At the beginning of your vba code (probably right along with your line to deactivate screen updating) insert the following line:

Application.Calculation = xlCalculationManual

Then, at the bottom right before you end sub, insert the line:

Application.Calculation = xlCalculationAutomatic

BINGO!  Problem solved.

No comments:

Post a Comment