Welcome toExcel.Tips.Net
Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment
ExcelTips FAQ
ExcelTips Premium
Learn Access Now
Free Printable Forms
Beauty Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Legal Tips
Money Tips
Organizing Tips
Pest Tips
Pet Tips
Wedding Tips
Word2007 Tips
WordTips
Advertise on the
ExcelTips Site
Filtering Columns for Unique Values
Printing Multiple Worksheets on a Single Page
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 (2013) applies to Microsoft Excel versions: 97 2000 2002 2003 2007
Got the Time? Understanding the ins and outs of working with times and dates can be confusing. Remove the confusion--ExcelTips: Times and Dates is an invaluable resource for learning how best to work with times and dates.