Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Forcing a Macro to Run when a Worksheet is Recalculated.

Forcing a Macro to Run when a Worksheet is Recalculated

by Allen Wyatt
(last updated April 21, 2016)

2

When you write a macro, it is designed to be run whenever you choose to run it. What if you need to develop a macro that will run whenever something changes in your worksheet? What if you want the macro to run automatically? This is particularly necessary if you are creating a custom function that you want to use within the cells of the worksheet.

This is where the Volatile method comes in handy. All you need to do is include the following statement within your macro:

Application.Volatile

This informs Excel that the results of the macro are dependent on the values in the worksheet, and that it should be executed whenever the worksheet is recalculated. For instance, consider the following user-defined function:

Function CountCells(MyRange As Range)
    Dim iCount As Integer
    iCount = 0
    For Each cell In MyRange
        If cell.HasFormula Then
            iCount = iCount + 1
        End If
    Next cell
    CountCells = iCount
End Function

This function, if used in a cell, counts the number of cells that contain formulas within a specified range. However, the function will only run the first time it is entered into a cell, or whenever the cell containing the formula is edited. If you want the function to recalculate every time the worksheet is recalculated, you would add the Volatile method near the beginning of the function:

Function CountCells(MyRange As Range)
    Dim iCount As Integer
    Application.Volatile
    iCount = 0
    For Each cell In MyRange
        If cell.HasFormula Then
            iCount = iCount + 1
        End If
    Next cell
    CountCells = iCount
End Function

The inclusion of the Application.Volatile method means that every time the worksheet is recalculated, this function (macro) is again run.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10598) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Forcing a Macro to Run when a Worksheet is Recalculated.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Table Borders Won't Print

Print a table and you may be surprised if it has no borders. That could be because you actually have the borders turned off. ...

Discover More

Odd & Even Headers and Footers

Adding a running header or footer to a document can be a nice touch. If you want, you can even tell Word to use a different ...

Discover More

Default Font for Page Numbers

Page numbers are a common addition to documents, and a great aid to readers. If you want to easily format page numbers, you ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Understanding Variables in VBA Macros

You can create and use all sorts of variables in your macros. This tip examines all the different data types you can specify.

Discover More

Running Macros on Hidden Worksheets

Excel allows you to hide worksheets so that they aren't visible to those using your workbook. Hiding worksheets has a ...

Discover More

Removing Pictures for a Worksheet in VBA

Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time figuring ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six more than 9?

2016-12-23 10:06:06

Michel Bilodeau

The example given here uses Application.Volatile in a function. Does this work for a subroutine as well?


2016-09-07 03:43:05

Jacob Larsen

Note that this only works for future changes. If you add Application.Volatile to an existing macro with existing cells using it, then you must manually force recalculate by dummy-editing all the relevant cells.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.