Refresh Excel VBA Function Results

Question!

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...



Answers

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.

By : Seggy224


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).

By : vzczc


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
By : MfJ


This video can help you solving your question :)
By: admin