Running custom functions created with macros

[tapioarimo]

Hi, how can I set my custom functions that have been created with macros to run automatically? now they only run when I click the cell in question. I need them to run same way as normal functions, ie automatically when I update data they are getting input from.

thanks, Tapio

[#2]

@tapioarimo 

If you specify the inputs as arguments of the function, the formula should be updated when you change the inputs. For example:

Function MySum(r As Range) As Double
    MySum = Application.Sum(r)
End Function

If you create a formula =MySum(A1:A3), the result should be updated when you change any of the cells A1:A3.

But if your function does not specify the inputs as arguments, this won't work:

Function MySum() As Double
    MySum = Application.Sum(Range("A1:A3"))
End Function

The formula =MySum() will not be updated automatically when you change A1:A3.

If you add Application.Volatile:

Function MySum() As Double
    Application.Volatile
    MySum = Application.Sum(Range("A1:A3"))
End Function

then the formula result will be updated whenever Excel calculates - for example when you edit A1:A3.