Force refresh of custom functions

[Guest]

Hi, I created a custom function using VBA in Excel. The function name is used
in a spreadsheet and (i) pulls numbers from 2 different cells, (ii) passes
them as arguments to the function, and then (iii) display a result with the
value returned by the function. However, when the values of the 2 "sources
cells" change, the result shown in the cell with the function doesn't update
automatically. Is there something I can do to force it to refresh? Any help
is appreciated.

Kind regards,
Don Thompson
(e-mail address removed)

 

[Guest]

one option would be to add
"Application.Volatile"
as a line in your function

 

[JE McGimpsey]

The best way is to include the cells as arguments to your function. So
instead of

Public Function foo()
foo = Range("A1").Value + Range("A2").Value
End Function

use

Public Function bar(arg1 As Double, arg2 As Double) As Double
bar = arg1 + arg2
End Function

then call the function with

A3: =bar(A1, A2)

Then whenever A1 or A2 changes, A3 will be updated (as long as
Calculation is set to Automatic).

Alternatively, you could make the function volatile:

Public Function foobar()
Application.Volatile
foobar = Range("A1").Value + Range("A2").Value
End Function

which will cause foobar() to recalculate each time any cell in the
worksheet is calculated.

 

[Guest]

Thank you both, these suggestions are very helpful.

Cheers,
Don

 

[Guest]

Thank you both, these suggestions are very helpful.

Cheers,
Don

 

[Guest]

One of the advantages to what Je says is that if you have a large program
forcing the "Volatile" function issue can dramatically increase the time for
recalcs. Most programers make a habit of using the volatile component only
when absolutely neccessary.