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.

--
Regards,
Tom Ogilvy

"ZootRot" wrote:

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?