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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10598) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. 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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Open up a workbook, and Excel normally runs the macros associated with that workbook. You can disable the automatic ...
Discover MoreMacros that run automatically when you open or close a workbook are quite helpful. You may not want them to run, however, ...
Discover MoreWant to stop Excel from running any automatic macros that may be stored with a workbook? Here's how to do it.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-03-08 13:21:41
Willy Vanhaelen
The function used in this tip is a bad example. Without Application.Volatile it will always give the right result when the worksheet is recalculated automatically or manually. Application.Volatile is often used (by precaution) when it is not necessary . The disavantage of using it is that it can slow down the recalculation a lot. So better use it only if it is really necessary.
Here you can find a clear explanation of a situation where Application.Volatile is a must: https://www.excel-easy.com/vba/examples/volatile-functions.html
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments