SPEED UP YOUR VBA RunTime!

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.

Find n-th space from the right...

Another quick post:

Have you ever had the wonderful pleasure of needing to pull all characters to the right of the 2nd or 3rd space within the string?  I know I have..

Solution:
*Disclaimer*: I have no idea where I found this beauty, but again, I FOUND it because someone else was willing to share - and sharing is the whole point of this blog...  I'm posting this just as much for myself as for anyone else. :)

Assuming your string is in cell C2:

=FIND(CHAR(1),SUBSTITUTE(C2," ",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2," ",""))-n))

For n:
("n" determines the number of spaces)
0=1
1=2 etc...

Update On Progress While Macro Runs..

Okay, I know it's been FOREVER since I've posted.  Long story short, I don't make money on the blog, but I do make money working....

Anyway, I found this AWESOME little tidbit for those of us who use VBA.

Situation:
I have a macro that takes a large chunk of data from a 3rd party .csv file, cleans it, and organizes it into a format that is usable.  The problem I have is that it takes a long time for it to run and I have no idea if it's hung up somehow or just slow.  If any of you have ever used a loop before, when the code takes a long time to run, it's possible you screwed up and it will loop forever!  It's nice to have an update on progress.

My Online Search Criteria: "excel vba msgbox update during code"

Solution:
I did NOT come up with this solution on my own, but I did find it on the stackoverflow site when I searched it out:  http://stackoverflow.com/questions/5459734/using-messagebox-without-pausing-the-application-vba

Anyway, here is the answer I liked:

Application.StatusBar = "Please be patient..."
Application.StatusBar = iDone & " of " & iTotal & " items done."

Using the Application Status Bar (Bottom left corner of your Excel window) is fantastic!  You can simply use a counter to display the number of the record you're on.

Well, that's it for today.  Hoping to get back into this as things slow down.