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.

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.
>

The problem is not passing the value x to the UDF but rather that
there is no built-in function for Tanh.

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

On Wed, 27 May 2009 11:52:01 -0700, Neal Carron
<(E-Mail Removed)> 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.

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.
> >

Chip,
Help (F1) defines Tanh in the usual way, so it appears there is such a
built-in function.
- Neal

"Chip Pearson" wrote:

>
> The problem is not passing the value x to the UDF but rather that
> there is no built-in function for Tanh.
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional
> Excel Product Group, 1998 - 2009
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on web site)
>
> On Wed, 27 May 2009 11:52:01 -0700, Neal Carron
> <(E-Mail Removed)> 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.
>

Neal,

Try this. Note that X1 will not be a valid range name, since it is a cell
address. So, name the cells NamedRangeX0 and NamedRangeX1.

Function fa(x) As Double
fa = 1 - Application.WorksheetFunction.Tanh _
((x - Range("NamedRangeX0")) / Range("NamedRangeX1"))
End Function


HTH,
Bernie
MS Excel MVP

"Neal Carron" <(E-Mail Removed)> wrote in message
news:100BC206-4253-41DB-80BB-(E-Mail Removed)...
>
> 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.
>

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.
> > >

On May 28, 12:13*pm, Jacob Skaria
<JacobSka...@discussions.microsoft.com> 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

Bernie,
Thanks, that worked.
Now the problem is this:
I've put =fa($E12) in, say, cell F12. It evaluates nicely.
Then I change the value in NamedRangeX0.
But the value in F12 does not change. the function does not update
automatically.
Automatic update is turned on.
Note that if cell F12 contained, say, =Sin(NamedRangeX0), it updates
immediately.
What am I doing wrong?



"Bernie Deitrick" wrote:

> Neal,
>
> Try this. Note that X1 will not be a valid range name, since it is a cell
> address. So, name the cells NamedRangeX0 and NamedRangeX1.
>
> Function fa(x) As Double
> fa = 1 - Application.WorksheetFunction.Tanh _
> ((x - Range("NamedRangeX0")) / Range("NamedRangeX1"))
> End Function
>
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Neal Carron" <(E-Mail Removed)> wrote in message
> news:100BC206-4253-41DB-80BB-(E-Mail Removed)...
> >
> > 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.
> >
>
>

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
> <JacobSka...@discussions.microsoft.com> 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
>