Thread: Function doesn't work

The following function doesn't work (I get #VALUE in the cell)
It is called with, say, =IntrptFn2($B$56,$K11,$L11)
I've been unable to debug it.
Why doesn't it evaluate properly?
-------
Function IntrptFn2(sRel, tauLeft, tauRight) As Double
' =IntrptFn2($B$56,$K11,$L11) doesn't work
eL = Application.WorksheetFunction.Exp(-tauLeft)
eR = Application.WorksheetFunction.Exp(-tauRight)
es = Application.WorksheetFunction.Exp(-sRel)
IntrptFn2 = Abs((1 + tauLeft) * eL - (1 + tauRight) * eR)
' That's good for xLeft>0, or xRight<0). Now inbetween
If (tauLeft < 0 And tauRight > 0) Then
tauGreater = tauLeft
If (tauRight > tauLeft) Then
tauGreater = tauRight
End If
eG = Application.WorksheetFunction.Exp(-tauGreater)
IntrptFn2 = (1 + sRel) * es - (1 + tauGreater) * eG
End If
End Function

I've resolved it. The problem was that the statement

eG = Application.WorksheetFunction.Exp(-tauGreater)

doesn't work. You just need

eG = Exp(-tauGreater)
I have no idea why; other macros I have require the long version.


"Joe User" wrote:

> "Neal Carron" wrote:
> > The following function doesn't work (I get #VALUE in the cell)
> > It is called with, say, =IntrptFn2($B$56,$K11,$L11)
> > I've been unable to debug it.
> > Why doesn't it evaluate properly?
>
> What does the following statement show you:
>
> debug.print sRel, tauLeft, tauRight
>
> FYI, I find the following logic to be strange:
>
> > If (tauLeft < 0 And tauRight > 0) Then
> > tauGreater = tauLeft
> > If (tauRight > tauLeft) Then
> > tauGreater = tauRight
> > End If
> > eG = Application.WorksheetFunction.Exp(-tauGreater)
>
> If the first condition is true (tauLeft<0 and tauRight>0), the second
> condition is always true (tauRight > tauLeft). So eG = eR always.
>
> Is something wrong with your logic?
>
> (Not the cause of the #VALUE error, though.)
>
>
> ----- original message -----
>
> "Neal Carron" wrote:
> > The following function doesn't work (I get #VALUE in the cell)
> > It is called with, say, =IntrptFn2($B$56,$K11,$L11)
> > I've been unable to debug it.
> > Why doesn't it evaluate properly?
> > -------
> > Function IntrptFn2(sRel, tauLeft, tauRight) As Double
> > ' =IntrptFn2($B$56,$K11,$L11) doesn't work
> > eL = Application.WorksheetFunction.Exp(-tauLeft)
> > eR = Application.WorksheetFunction.Exp(-tauRight)
> > es = Application.WorksheetFunction.Exp(-sRel)
> > IntrptFn2 = Abs((1 + tauLeft) * eL - (1 + tauRight) * eR)
> > ' That's good for xLeft>0, or xRight<0). Now inbetween
> > If (tauLeft < 0 And tauRight > 0) Then
> > tauGreater = tauLeft
> > If (tauRight > tauLeft) Then
> > tauGreater = tauRight
> > End If
> > eG = Application.WorksheetFunction.Exp(-tauGreater)
> > IntrptFn2 = (1 + sRel) * es - (1 + tauGreater) * eG
> > End If
> > End Function