1

I've written a Function in VBA to return a certain value based on certain cells' values. I execute/run this function in a formula on a certain cell

=MyFunction()

My problem is, the cells which have the above formula don't automatically update if the cells used in the function (in VBA) are changed.

How can I re-run or update or re-execute the above formula if the certain cells that are used are changed?

Improve this question
4
  • 3
    Excel only refreshes worksheet formulas (including UDFs) if cells in ranges which are passed as parameters to the functions are changed. Excel doesn't parse your UDF source code to see if it needs to recalculate your UDF when something changes. Either pass the input ranges via your UDF's parameter list, or look at adding Application.Volatile to your UDF VBA code. Commented Oct 8, 2014 at 22:01
  • Well my UDF only returns a value, it's used exactly as I stated above, not sure if this is an issue. Another issue is, it's not in the same sheet as the cells that are changing. Commented Oct 8, 2014 at 22:04
  • I edited my comment - hope that clears thing up. Commented Oct 8, 2014 at 22:07
  • It does clear some things up. My problem with Application.Volatile is that the cells that are changing are not in the same sheet where the function is used. All I needed to do was add a useless argument in the UDF for the range that needs to be tracked for any changed and it worked! Thanks! Commented Oct 8, 2014 at 22:12

1 Answer 1

Reset to default
2

You must recode your UDF to achieve the desired volatility. Say you want the UDF to re-calculate if A1 or B1 or C1 are changed, then use:

=MyFunction(A1,B1,C1)

Using arguments gets the required volatility.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.