Pause a Macro or Make it Slow Down in Excel

Add to Favorites

How to make a macro stop or pause for a specified amount of time.

This allows you to slow down the execution of a macro.

Method 1 - Simple but Not Versatile

Add this line of code anywhere in your macro where you want it to pause.

Application.Wait(Now + TimeValue("00:00:01"))

This will pause the macro for 1 second.

The first set of zeros is hours; the second set is minutes; the third set is seconds.

Method 2 - More Work but Better - Fractions of Seconds

Sometimes you need to pause a macro for a fraction of a second and, for this, we need to do something else.

Put this piece of code at the top of your module:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

It would look like this:

Notice that it was put above the first Sub in the module.

Then, make the macro stop or pause using this method:

Sleep(1000)

This is in milliseconds, so 1000 equals 1 second, 2000 equals 2 seconds and anything less than a thousand is a fraction of a second.

Both Methods in the VBA Editor

Notes

Both methods have their place; use whichever one works best for you.

Download the sample file attached to this tutorial to see these macros in Excel.


Downloadable Files: Excel File

Similar Content on TeachExcel
Minimize or Maximize the Ribbon Menu in Excel
Tutorial: This is a simple way to get more space in Excel, reduce clutter, and streamline the inte...
Delete Blank Rows in Excel
Macro: This is a macro which will delete blank rows in excel. This version will delete an entire ...
Round Numbers Up or Down in Excel
Tutorial: How to round a number up or down and also to a specified number of decimal places in Excel...
Make Users Enable Macros in Order to View a Workbook in Excel
Tutorial: Tutorial showing you how to make a user enable macros in a workbook in order to view the ...
Increment a Value Every X Number of Rows in Excel
Tutorial: How to increment a value, number, percent, etc. every so many rows in Excel. This tutorial...
Generate a Non-Repeating List of Random Numbers in Excel - UDF
Macro: Generate a series of non-repeating random numbers in Excel with this UDF (user defined fun...