tags:

views:

3921

answers:

4

Does anyone know how I can get a user-defined function to re-evaluate itself (based on changed data in the spreadsheet)? I've tried F9 and Shift+F9, but those don't work. The only thing that seems to work is editing the cell with the function call and then pressing Enter. Any ideas? I seem to remember being able to do this...

+2  A: 

Okay, found this one myself. You can use Ctrl+Alt+F9 to accomplish this.

Brian Sullivan
+5  A: 

You should use Application.Volatile in the top of your function

Function doubleMe(d)
    Application.Volatile
    doubleMe = d * 2
End Function

It will then reevaluate whenever the workbook changes (if you calculation is set to automatic)

vzczc
That's awesome, i did not know that, thanks a bunch
Matthew Rathbone
Thanks, just been bashing my head against a desk over this. Should point out though that in Excel 2010, you need to pass True to Application.Volatile, i.e. `Application.Volatile True`.
mdm
+4  A: 

Some more information on the F9 keyboard shortcuts for calculation in Excel

  • F9 Recalculates all worksheets in all open workbooks
  • Shift+F9 Recalculates the active worksheet
  • Ctrl+Alt+F9 Recalculates all worksheets in all open workbooks (Full recalculation)
  • Shift+Ctrl+Alt+F9 Rebuilds the dependency tree and does a full recalculation
Robert Mearns
A: 

If you include ALL references to spreadsheet data in the UDF parameter list Excel will recalculate your function whenever the referenced data changes:


    Public Function doubleMe(d as variant)
    doubleMe=d*2
    end Function

You can also use Application.Volatile, 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.


    Public Function doubleMe()
    Application.Volatile
    doubleMe=Worksheets("Fred").Range("A1")*2
    end Function