Pause a Macro or Make it Slow Down in Excel

Add to Favorites
Author:

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.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Our Excel Courses

Professional Input Forms in Excel

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

Send Emails from Excel

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

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...
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 w...
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...
Increment a Value Every X Number of Rows in Excel
Tutorial: (New Easier Version for Excel 2010 and Later) How to increment a value, number, percent, e...
Delete Blank Rows in Excel
Macro: This is a macro which will delete blank rows in excel. This version will delete an entire ...
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...
Tutorial Details
Downloadable Files: Excel File
Similar Content
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...
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 w...
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...
Excel Forum