Tuesday, March 17, 2015

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.

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"

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.

No comments:

Post a Comment