Excel.Tips.Net Welcome toExcel.Tips.Net

Helpful Links

Tips.Net Home
ExcelTips Home
Ask an Excel Question
Make a Comment

Tips.Net Store

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

Newest Tips

Recording a Macro

Adding a Little Animation to Your Life

Converting a Range of URLs to Hyperlinks

Making the Formula Bar Persistent

Engineering Calculations

Digital Signatures for Macros

Fixing the Decimal Point

 

Forcing a Macro to Run When a Worksheet is Recalculated

Summary: Normally a macro is only calculated when you specifically tell Excel to calculate it. Some macros need to be calculated whenever your worksheet is recalculated. This won't happen unless you include a very specific command within the body of your macro. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

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

Save Time and Money! Many people need to keep track of employee time, but don't know where to start when it comes to creating a spreadsheet. Here's a way to save time, effort, and money with ready-to-use timesheet templates.
 
Check out Timesheet Templates today!