Thank you Charles, in the interim I stumbled across that in a reply to
another post in 2005 and it seems to work. It may even solve that
non-triggering of a worksheet change event that I've been griping about -
I'll be testing that shortly. Regards, Brett

"Charles Williams" wrote:

Because your function refers to cells that are not in the argument list
excel does not know when to recalculate the function.

Try adding
Application.Volatile
to your function so that it will recalculate at each Excel recalculation.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Brettjg" wrote in message
...
Hi, I have the following Public Function in a non-object module, and for
the
most part it works perfectly. However, when I run a macro (or something
else
happens that I can't define just yet) the cells using the function return
#VALUE (when they previously had the correct values in them - and nothing
else to do with them has changed. Simply copy & paste the cel with the
function in it does nothing. I have to actually copy & paste the formula
for
the cell in the formula bar at the top and then the value returns to
normal.
The code is:


Public Function FX_EXISTING(num)
Dim cnt As Integer, cntfx As Integer, fx_pos(10) As Integer
cnt = 1: cntfx = 0
Do Until cntfx = num
If Range("FX.REFI").Offset(cnt, 0) 0 Then
cntfx = cntfx + 1
fx_pos(cntfx) = cnt
End If
cnt = cnt + 1
If cnt 10 Or cntfx = num Then: Exit Do
Loop
If cntfx = num Then: FX_EXISTING = Range("FX.REFI").Offset(fx_pos(num),
-23).Value
'Sheets("LOANS").Calculate

End Function

I had a suspicion that the Calculate at the end may have mad it play up so
I
commented that out, but it didn't help (it's probably not necessary
anyway).
Regards, Brett