Running custom functions created with macros

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3137583%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ERunning%20custom%20functions%20created%20with%20macros%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3137583%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%2C%20how%20can%20I%20set%20my%20custom%20functions%20that%20have%20been%20created%20with%20macros%20to%20run%20automatically%3F%20now%20they%20only%20run%20when%20I%20click%20the%20cell%20in%20question.%20I%20need%20them%20to%20run%20same%20way%20as%20normal%20functions%2C%20ie%20automatically%20when%20I%20update%20data%20they%20are%20getting%20input%20from.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3Ethanks%2C%20Tapio%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3137583%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EMacros%20and%20VBA%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Visitor

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

1 Reply

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