Menu
How can I get a user-defined function to re-evaluate itself based on changed data in the spreadsheet?
I tried F9 and Shift+F9.
The only thing that seems to work is editing the cell with the function call and then pressing Enter.
472
You can trigger a data refresh when your Excel file is first opened by pasting VBA code into the Workbook_Open event. Simply double-click the ThisWorkbook object in the VBA Project Pane to open the text editor (blank white sheet) within the Visual Basic Editor (keyboard shortcut Alt +F11).
You can also press Ctrl+Alt+F5 anywhere in the workbook. This command not only refreshes data connections, it also refreshes all pivot tables (which are a form of data connection).
The keyboard shortcut for that choice is Shift + F9 . Recalculate formulas that have changed since the last calculation, and formulas dependent on them, in all open workbooks. If a workbook is set for automatic recalculation, you do not need to press F9 for recalculation.
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 your calculation is set to automatic).
96
Some more information on the F9 keyboard shortcuts for calculation in Excel
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
