User Function Not Recalculating

[ZootRot]

Excel 2003

I've created a simple user function in VBA. It uses data in a cell to
calculate an answer. It works fine, except when I change the input data,
the function doesn't recalculate. All other recalcs work on the
spreadsheet.

I have checked that auto recalc is on, and but even when I press F9, the
recalc doesn't work. I have to edit the cell formula or copy and paste the
formulae again in order to recalc.

Any thoughts on resolving this?

 

[Guest]

Place the following line at the beginning of your function

Application.Volatile

 

[Niek Otten]

Look here:

http://xldynamic.com/source/xld.xlFAQ0024.html

under the UDF heading.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Excel 2003
|
| I've created a simple user function in VBA. It uses data in a cell to
| calculate an answer. It works fine, except when I change the input data,
| the function doesn't recalculate. All other recalcs work on the
| spreadsheet.
|
| I have checked that auto recalc is on, and but even when I press F9, the
| recalc doesn't work. I have to edit the cell formula or copy and paste the
| formulae again in order to recalc.
|
| Any thoughts on resolving this?
|
|

 

[Guest]

to get Excel to recalculate it, put all precedent cells in the argument to
the function

instead of

Public function Mysum()
Dim tot as Double, cell as Range
for each cell in Range("A1:A10")
if isnumeric(cell) then
tot = tot + cell.Value
end if
Next
Mysum = tot
End Function

do

Public function Mysum(rng as range)
dim tot as Double, cell as Range
for each cell in rng
if isnumeric(cell) then
tot = tot + cell.Value
end if
Next
Mysum = tot
End Function

usage =MySum(A1:A10)

then it will recalculate when a cell in A1:A10 is changed.

 

[Dave Peterson]

You could add "application.volatile" and the UDF will recalc the next time that
excel recalcs.

But if you change something that the function needs but doesn't cause a
recalculation, your UDF could be incorrect.

If possible, it's better to pass the cells that the UDF needs to use so that
excel can know when to calculate that function.

function myFunc(rng1 as range, rng2 as range)
myfunc = rng1.value + rng2.value
end function

is better than:

function myFunc(rng1 as range)
myfunc = rng1.value + rng1.offset(0,1).value
end function

 

[Dave Peterson]

Ps. There are some changes that won't cause the function to recalculate -- even
if you pass all the ranges.

Things like formatting changes (font/fill/boldness...) have this trouble. You'd
want to use application.volatile, but force a recalc before you trust the
results.