Public function returns error intermitently

[Brettjg]

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

 

[Charles Williams]

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]

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

 

[Dave Peterson]

Check your other post.

When you're going to multipost similar messages or just want to supersede your
previous message with a new message, you should either post in the same
thread--or at least post a reply to the initial post telling people not to waste
time responding to that message.

 

[Brettjg]

Hi Dave, yes sorry about that. I couldn't remember what precise question name
was (and I forgot to tick notify replies), and this site is so pedantic about
that (even to the point of caps v lower case). Even looking for recent posts
by myself is pretty useles - it misses heaps of recent stuff and gives me
stuff I asked about 3 years ago.
I'll check your reply after some badly needed fuel. Regrds, Brett

 

[Chip Pearson]

As a general design guideline, you should always pass as parameters
all of the cell that are used within a user defined function. You
should never refer to cells that are not passed in as parameters.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Brettjg]

THIS POST IS NO LONGER REQUIRED

Hi Dave, yes sorry about that. I couldn't remember what precise question name
was (and I forgot to tick notify replies), and this site is so pedantic about
that (even to the point of caps v lower case). Even looking for recent posts
by myself is pretty useles - it misses heaps of recent stuff and gives me
stuff I asked about 3 years ago.
I'll check your reply after some badly needed fuel. Regrds, Brett