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...
Refresh Excel VBA Function Results
Using Excel 2010 I experienced the same issues as Brian. When I tried all the solutions proposed here there was no improvement, the user defined function appeared not to recalculate despite having Application.Volatile, using recalculation key combinations etc. However I did realize that my function definition was at fault in that all cell references in it were not sufficiently qualified to work correctly when the sheet containing the function was not the active sheet for example changing cells(a,b) to Application.Caller.Worksheet.Cells(a,b) completely fixed the problem! I believe this is the solution because when the sheet containing my function is not the active sheet cells(a,b) is interpreted as referring to whatever sheet happens to be active at the time.
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).
It works well this one to Refresh the calculation better than Range(A:B).Calculate
Public Sub UpdateMyFunctions() Dim myRange As Range Dim rng As Range 'Considering The Functions are in Range A1:B10 Set myRange = ActiveSheet.Range("A1:B10") For Each rng In myRange rng.Formula = rng.Formula Next End Sub