Force refresh of custom functions
 

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

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

"donesquire" wrote:

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

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.


In article ,
"donesquire" wrote:

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.

Thank you both, these suggestions are very helpful.

Cheers,
Don

"bj" wrote:

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

"donesquire" wrote:

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

Thank you both, these suggestions are very helpful.

Cheers,
Don

"JE McGimpsey" wrote:

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.


In article ,
"donesquire" wrote:

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.

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.

"donesquire" wrote:

Thank you both, these suggestions are very helpful.

Cheers,
Don

"JE McGimpsey" wrote:

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.


In article ,
"donesquire" wrote:

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.