Vba excel custom function not updating

Rated 3.93/5 based on 752 customer reviews

By default, UDF's (User Defined Functions) in Excel VBA are not volatile. Explanation: the non-volatile function is not recalculated when any other cell on the sheet changes.

They are only recalculated when any of the function's arguments change.

If you send it less than a whole column, it returns an error.

If you send it more than one column, it returns an error.

I've tried using the below in the workbook module but it does not update the cells.Is there some trick maybe with paste special or some 0 additions or something to force the entire work book ie all formulas update as if I d-cliked on each one. Also, even if I was to do it manualy once there are collapsable rows which once hiden and then opened do not recalculate the UDFs but give #VALUE! Public Sub Update My Functions() Dim my Range As Range Dim rng As Range 'Considering The Functions are in Range A1: B10 Set my Range = Active Sheet. When I tried all the solutions proposed here there was no improvement, the user defined function appeared not to recalculate despite having Application. Public Sub Update My Functions() Dim my Range As Range Dim rng As Range 'Considering The Functions are in Range A1: B10 Set my Range = Active Sheet. Formula Next End Sub Using Excel 2010 I experienced the same issues as Brian. 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.

Leave a Reply