<p>How can I get a user-defined function to re-evaluate itself based on changed data in the spreadsheet?</p>

<p>I tried <strong><kbd>F9</kbd></strong> and <strong><kbd>Shift</kbd>+<kbd>F9</kbd></strong>.</p>

<p>The only thing that seems to work is editing the cell with the function call and then pressing Enter.</p>

[{'answer_id': 11061, 'author': 'Brian Sullivan', 'author_id': 767, 'author_profile': 'https://Stackoverflow.com/users/767', 'pm_score': 4, 'selected': False, 'text': '<p>Okay, found this one myself.  You can use <kbd>Ctrl</kbd>+<kbd>Alt</kbd>+<kbd>F9</kbd> to accomplish this.</p>\n'}, {'answer_id': 12018, 'author': 'vzczc', 'author_id': 224, 'author_profile': 'https://Stackoverflow.com/users/224', 'pm_score': 8, 'selected': True, 'text': '<p>You should use <code>Application.Volatile</code> in the top of your function:</p>\n\n<pre><code>Function doubleMe(d)\n    Application.Volatile\n    doubleMe = d * 2\nEnd Function\n</code></pre>\n\n<p>It will then reevaluate whenever the workbook changes (if your calculation is set to automatic).</p>\n'}, {'answer_id': 48548, 'author': 'Robert Mearns', 'author_id': 5050, 'author_profile': 'https://Stackoverflow.com/users/5050', 'pm_score': 5, 'selected': False, 'text': '<p>Some more information on the F9 keyboard shortcuts for calculation in Excel</p>\n\n<ul>\n<li><kbd>F9</kbd>                Recalculates all worksheets in all open workbooks</li>\n<li><kbd>Shift</kbd>+ <kbd>F9</kbd>          Recalculates the active worksheet</li>\n<li><kbd>Ctrl</kbd>+<kbd>Alt</kbd>+ <kbd>F9</kbd>       Recalculates all worksheets in all open workbooks (Full recalculation)</li>\n<li><kbd>Shift</kbd> + <kbd>Ctrl</kbd>+<kbd>Alt</kbd>+ <kbd>F9</kbd> Rebuilds the dependency tree and does a full recalculation </li>\n</ul>\n'}, {'answer_id': 120363, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': '<p>If you include ALL references to the spreadsheet data in the UDF parameter list, Excel will recalculate your function whenever the referenced data changes:</p>\n\n<pre><code>Public Function doubleMe(d As Variant)\n    doubleMe = d * 2\nEnd Function\n</code></pre>\n\n<p>You can also use <code>Application.Volatile</code>, but this has the disadvantage of making your UDF always recalculate - even when it does not need to because the referenced data has not changed.</p>\n\n<pre><code>Public Function doubleMe()\n    Application.Volatile\n    doubleMe = Worksheets("Fred").Range("A1") * 2\nEnd Function\n</code></pre>\n'}, {'answer_id': 26507013, 'author': 'Prashanth', 'author_id': 4169806, 'author_profile': 'https://Stackoverflow.com/users/4169806', 'pm_score': 0, 'selected': False, 'text': '<pre><code>Public Sub UpdateMyFunctions()\n    Dim myRange As Range\n    Dim rng As Range\n\n    \'Considering The Functions are in Range A1:B10\n    Set myRange = ActiveSheet.Range("A1:B10")\n\n    For Each rng In myRange\n        rng.Formula = rng.Formula\n    Next\nEnd Sub\n</code></pre>\n'}, {'answer_id': 27279953, 'author': 'ayman', 'author_id': 3180806, 'author_profile': 'https://Stackoverflow.com/users/3180806', 'pm_score': 1, 'selected': False, 'text': '<p>To switch to Automatic:</p>\n\n<pre><code>Application.Calculation = xlCalculationAutomatic    \n</code></pre>\n\n<p>To switch to Manual:</p>\n\n<pre><code>Application.Calculation = xlCalculationManual    \n</code></pre>\n'}, {'answer_id': 30745135, 'author': 'MfJ', 'author_id': 4992736, 'author_profile': 'https://Stackoverflow.com/users/4992736', 'pm_score': 1, 'selected': False, 'text': '<p>This refreshes the calculation better than <code>Range(A:B).Calculate</code>:</p>\n\n<pre><code>Public Sub UpdateMyFunctions()\n    Dim myRange As Range\n    Dim rng As Range\n\n    \' Assume the functions are in this range A1:B10.\n    Set myRange = ActiveSheet.Range("A1:B10")\n\n    For Each rng In myRange\n        rng.Formula = rng.Formula\n    Next\nEnd Sub\n</code></pre>\n'}, {'answer_id': 47408310, 'author': 'alex303411', 'author_id': 8978155, 'author_profile': 'https://Stackoverflow.com/users/8978155', 'pm_score': 1, 'selected': False, 'text': "<p>The <code>Application.Volatile</code> doesn't work for recalculating a formula with my own function inside. I use the following function:\n<code>Application.CalculateFull</code></p>\n"}, {'answer_id': 57929883, 'author': 'Gene Sorg', 'author_id': 12065022, 'author_profile': 'https://Stackoverflow.com/users/12065022', 'pm_score': 1, 'selected': False, 'text': '<p>I found it best to only update the calculation when a specific cell is changed. Here is an example VBA code to place in the "Worksheet" "Change" event:</p>\n\n<pre><code>Private Sub Worksheet_Change(ByVal Target As Range)\n  If Not Intersect(Target, Range("F3")) Is Nothing Then\n    Application.CalculateFull\n  End If\nEnd Sub\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/11045', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/767/']