Here is a quick and dirty snippet on how to pause VBA while it is running and executing a process. Say for instance you are setting a connection and closing another to an external source, and you need to pause your VBA for a moment of time for the exterior items to transition before continuing your VBA. This solution will do exactly what you need it do then. Just note, it will pause EVERYTHING!

So, if you are in your MS Access Database and call this procedure, it’s going to lock (safely) and hold tight for the duration. If you are looking to pause VBA process without locking your application, I’ll have to post another solution for that. Just note, there is one, it’s just a little more complicated than this little nugget.

First, you need to make a quick declaration in your module to the AppSleep library. Something along these lines will do just fine:
Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

Next, all you need to do is call the action with your criteria in seconds:
Call AppSleep(PauseInSeconds * 1000)

Of course, you would want to put this in a function so you can easily call it from within your application, so here is run through of the entire solution:

Were you expecting more than that? Sorry to disappoint, but this one is pretty straight forward. While it is simple to implement and use, it is one that I have can just about always find an excuse to implement in my work.

Enjoy!

Tags: