Refresh Excel VBA Function Results

excel vba user-defined-functions
Brian Sullivan picture Brian Sullivan · Aug 14, 2008 · Viewed 144.4k times · Source

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.

Answer

vzczc picture vzczc · Aug 15, 2008

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

Related questions

Create a custom worksheet function in Excel VBA

I have a faint memory of being able to use VBA functions to calculate values in Excel, like this (as the cell formula): =MyCustomFunction(A3) Can this be done? EDIT: This is my VBA function signature: Public Function MyCustomFunction(str …

Read More
Function Overloading and UDF in Excel VBA

I'm using Excel VBA to a write a UDF. I would like to overload my own UDF with a couple of different versions so that different arguments will call different functions. As VBA doesn't seem to support this, could anyone …

Read More
User Defined Functions NOT recalculating

I recently took a large, stable XLSM file, and split it apart into an XLAM and XLSX. Thousands of cells in the XLSX call (udfs) functions in the XLAM, and each such udf begins with the statement "Application.Volatile" (overkill, …

Read More