That will probably work also.
Bernie Deitrick and Jacob Skaria's method works.
But I added one more question in my reply to Bernie.
- Neal

"Dougaj4" wrote:

On May 28, 12:13 pm, Jacob Skaria
wrote:
Dear Neal

Here if x is a number....and others are named ranges....

Function fa(x) As Double
fa = 1-WorksheetFunction.Tanh((x - _
Range("myname2")) / Range("myname3"))
End Function

If this post helps click Yes
---------------
Jacob Skaria



"Neal Carron" wrote:
Jacob,
Not enough. With your suggestion, there was no error, but the function
evaluates to #VALUE! in the cell in which I entered =fa(E12).
This occurred when I inserted Worksheetfunction. in front of Tanh and in
front of Range or Cell.
Any other suggestions?
Thanks,
- Neal

"Jacob Skaria" wrote:

All Worksheetfunctions should be referred as below in VBA

1-Worksheetfunction.Tanh(.... )

If this post helps click Yes
---------------
Jacob Skaria

"Neal Carron" wrote:

In a user defined function, how do you reference named cells in Excel2007?
Mathematically I need to evaluate:
fa(x) = 1-tanh( (x-x0)/x1 )
Mathematically, x, x0, and x1 are numbers.

In Excel the calling argument x is (the contents of) a cell, like E11, which
is a number.
x0 and x1 are named cells, each containing a number. i.e., each is a
one-cell range

I've tried:

Function fa(x)
fa = 1-Tanh( (Range(x) - Range(x0)) / Range(x1) )
End Function

which doesn't work. The error window highlights Range and says "Compile
error: Sub or Function not defined"

and

Function fa(x)
fa = 1-Tanh( (Cell("contents",x) - Cell("contents",x0) / Cell("contents",x1) )
End Function

also doesn't work. Same error with Cell highlighted.
Can someone set me straight?
I am not well versed in Excel2007.- Hide quoted text -

- Show quoted text -


What Jacob said, or alternatively:

Function fa(X, X_0, X1)
fa = 1 - WorksheetFunction.Tanh((X - X_0) / X_1)
End Function

which gives more flexibility, at the expense of two extra arguments