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
2008-08-14 13:59:21
+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
2008-08-15 06:18:51
That's awesome, i did not know that, thanks a bunch
Matthew Rathbone
2008-10-10 12:37:55
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
2010-03-31 13:00:36
+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
2008-09-07 16:11:58
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