Cell reference in User Defined Function.
 

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.

Cell reference in User Defined Function.
 

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.

Cell reference in User Defined Function.
 

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

Cell reference in User Defined Function.
 

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.

Cell reference in User Defined Function.
 

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

Cell reference in User Defined Function.
 

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" wrote in message
...

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.

Cell reference in User Defined Function.
 

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.

Cell reference in User Defined Function.
 

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

Cell reference in User Defined Function.
 

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" wrote in message
...

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.

Cell reference in User Defined Function.
 

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

Cell reference in User Defined Function.
 

Neal,

Excel dependency tree does not include cells called from within UDFs, so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in message
...

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.

Cell reference in User Defined Function.
 

Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept parameters
and automatically update them. Having to put all parameters as arguments
seems to partially defeat the usefulness of a UDF. I would ask "Why does
Excel do it that way", but the answer is probably "That's the way it is".

Is there another way around it? For example, can a parameter (like a
named cell) be declared, say, as "Global" so a subroutine or function will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within UDFs, so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in message
...

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.

Cell reference in User Defined Function.
 

Neal,

You can force a recalc of a UDF by 'marrying' it to a volatile function. A silly example

=IF(NOW()1,YourUDF(x))

NOW() will always be 1, so it will always evaluate as TRUE, and your UDF will be evaluated.

You could also use

=(NOW()<1)*YourUDF(x)

if your UDF returns a number.


HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept parameters
and automatically update them. Having to put all parameters as arguments
seems to partially defeat the usefulness of a UDF. I would ask "Why does
Excel do it that way", but the answer is probably "That's the way it is".

Is there another way around it? For example, can a parameter (like a
named cell) be declared, say, as "Global" so a subroutine or function will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within UDFs, so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in message
...

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.

Cell reference in User Defined Function.
 

Hi Neal,

<This is the first I've heard of a function that doesn't accept parameters
and automatically update them.

Well, I never heard of a function that reacts to items that are NOT in the
argument list. The whole idea of a function is that its outside world is
defined by its arguments. There is nothing else that can influence the
outcome of the function.

Of course I know that we sometimes make exceptions, like internal tables
with constants. Nevertheless, formally, a function is completely defined by
its arguments and a description of how it processes them to a defined
result, which then takes the place of its call.

Any other form is more likely to be a subroutine, not a function.

Well, that's my opinion; I'm curious what others might think about this.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Neal Carron" wrote in message
...
Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept parameters
and automatically update them. Having to put all parameters as arguments
seems to partially defeat the usefulness of a UDF. I would ask "Why does
Excel do it that way", but the answer is probably "That's the way it is".

Is there another way around it? For example, can a parameter (like a
named cell) be declared, say, as "Global" so a subroutine or function will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within UDFs, so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in message
...

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.

Cell reference in User Defined Function.
 

Hi Niek,
Well, I had in mind a function in Fortran. Parameters that occur in the
function definition, not in the arguments, can be put in common, are updated
immediately, and have their new value when the function is called.
So I was wondering about something like that in Excel when I asked about
declaring them "Global". I believe some other languages besides Fortran
allow the same kind of thing.
Best Regards,
- Neal

"Niek Otten" wrote:

Hi Neal,

<This is the first I've heard of a function that doesn't accept parameters
and automatically update them.

Well, I never heard of a function that reacts to items that are NOT in the
argument list. The whole idea of a function is that its outside world is
defined by its arguments. There is nothing else that can influence the
outcome of the function.

Of course I know that we sometimes make exceptions, like internal tables
with constants. Nevertheless, formally, a function is completely defined by
its arguments and a description of how it processes them to a defined
result, which then takes the place of its call.

Any other form is more likely to be a subroutine, not a function.

Well, that's my opinion; I'm curious what others might think about this.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Neal Carron" wrote in message
...
Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept parameters
and automatically update them. Having to put all parameters as arguments
seems to partially defeat the usefulness of a UDF. I would ask "Why does
Excel do it that way", but the answer is probably "That's the way it is".

Is there another way around it? For example, can a parameter (like a
named cell) be declared, say, as "Global" so a subroutine or function will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within UDFs, so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in message
...

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.

Cell reference in User Defined Function.
 

Bernie,
Thanks, but it didn't work.
I removed the parameters (named cells) from the arguments and put them back
in the function definition.
When using either of your suggested methods, the function does not recalc. I
get #VALUE! in the cell.
The one remaining argument is a cell reference; the cell contains a number.
All the parameters are named cells, each a number.
- Neal

"Bernie Deitrick" wrote:

Neal,

You can force a recalc of a UDF by 'marrying' it to a volatile function. A silly example

=IF(NOW()1,YourUDF(x))

NOW() will always be 1, so it will always evaluate as TRUE, and your UDF will be evaluated.

You could also use

=(NOW()<1)*YourUDF(x)

if your UDF returns a number.


HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept parameters
and automatically update them. Having to put all parameters as arguments
seems to partially defeat the usefulness of a UDF. I would ask "Why does
Excel do it that way", but the answer is probably "That's the way it is".

Is there another way around it? For example, can a parameter (like a
named cell) be declared, say, as "Global" so a subroutine or function will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within UDFs, so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in message
...

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.

Cell reference in User Defined Function.
 

Neal,

I don't get your #VALUE! result. That usually results if your named ranges
do not exist... Did you create the named ranges that your UDF is using?

I used

=IF(NOW()1,fa(A2))
and
=fa(A2)

with this definition:

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

When I change the value in either of the named ranges NamedRangeX0 or
NamedRangeX1, the first formula updates, while the second one does not.

HTH,
Bernie
MS Excel MVP




"Neal Carron" wrote in message
...
Bernie,
Thanks, but it didn't work.
I removed the parameters (named cells) from the arguments and put them
back
in the function definition.
When using either of your suggested methods, the function does not recalc.
I
get #VALUE! in the cell.
The one remaining argument is a cell reference; the cell contains a
number.
All the parameters are named cells, each a number.
- Neal

"Bernie Deitrick" wrote:

Neal,

You can force a recalc of a UDF by 'marrying' it to a volatile function.
A silly example

=IF(NOW()1,YourUDF(x))

NOW() will always be 1, so it will always evaluate as TRUE, and your
UDF will be evaluated.

You could also use

=(NOW()<1)*YourUDF(x)

if your UDF returns a number.


HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept
parameters
and automatically update them. Having to put all parameters as
arguments
seems to partially defeat the usefulness of a UDF. I would ask "Why
does
Excel do it that way", but the answer is probably "That's the way it
is".

Is there another way around it? For example, can a parameter (like a
named cell) be declared, say, as "Global" so a subroutine or function
will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within UDFs,
so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in
message
...

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.

Cell reference in User Defined Function.
 

Bernie,
You're quite right. I just made a dumb mistake.
Sorry, I hate bothering people because of my own mistakes.
Thanks for all your help.
- Neal

"Bernie Deitrick" wrote:

Neal,

I don't get your #VALUE! result. That usually results if your named ranges
do not exist... Did you create the named ranges that your UDF is using?

I used

=IF(NOW()1,fa(A2))
and
=fa(A2)

with this definition:

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

When I change the value in either of the named ranges NamedRangeX0 or
NamedRangeX1, the first formula updates, while the second one does not.

HTH,
Bernie
MS Excel MVP




"Neal Carron" wrote in message
...
Bernie,
Thanks, but it didn't work.
I removed the parameters (named cells) from the arguments and put them
back
in the function definition.
When using either of your suggested methods, the function does not recalc.
I
get #VALUE! in the cell.
The one remaining argument is a cell reference; the cell contains a
number.
All the parameters are named cells, each a number.
- Neal

"Bernie Deitrick" wrote:

Neal,

You can force a recalc of a UDF by 'marrying' it to a volatile function.
A silly example

=IF(NOW()1,YourUDF(x))

NOW() will always be 1, so it will always evaluate as TRUE, and your
UDF will be evaluated.

You could also use

=(NOW()<1)*YourUDF(x)

if your UDF returns a number.


HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept
parameters
and automatically update them. Having to put all parameters as
arguments
seems to partially defeat the usefulness of a UDF. I would ask "Why
does
Excel do it that way", but the answer is probably "That's the way it
is".

Is there another way around it? For example, can a parameter (like a
named cell) be declared, say, as "Global" so a subroutine or function
will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within UDFs,
so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in
message
...

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.

Cell reference in User Defined Function.
 

I've been reading this post because I've run into a similar problem:

I have created several user defined functions. None of them happen to call
existing EXCEL functions. They're just simple arithmetical operations.
I have a spreadsheet that contains several named ranges of data.
I am computing a table of values and I want to use entries in the named
ranges as arguments to my UDFs.
If I enter as an argument to my UDF a named range, I get a "Value" error in
the cell containing the the UDF. If I enter arguments to the UDF by clicking
on the cells in the named range, the UDF returns the correct value.
I have created the UDFs and Ranges to make the programming in the
spreadsheet more readable and help me (and others locate errors).

Is it possible to do what I'm trying to do?

Steve
"Neal Carron" wrote:

Bernie,
You're quite right. I just made a dumb mistake.
Sorry, I hate bothering people because of my own mistakes.
Thanks for all your help.
- Neal

"Bernie Deitrick" wrote:

Neal,

I don't get your #VALUE! result. That usually results if your named ranges
do not exist... Did you create the named ranges that your UDF is using?

I used

=IF(NOW()1,fa(A2))
and
=fa(A2)

with this definition:

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

When I change the value in either of the named ranges NamedRangeX0 or
NamedRangeX1, the first formula updates, while the second one does not.

HTH,
Bernie
MS Excel MVP




"Neal Carron" wrote in message
...
Bernie,
Thanks, but it didn't work.
I removed the parameters (named cells) from the arguments and put them
back
in the function definition.
When using either of your suggested methods, the function does not recalc.
I
get #VALUE! in the cell.
The one remaining argument is a cell reference; the cell contains a
number.
All the parameters are named cells, each a number.
- Neal

"Bernie Deitrick" wrote:

Neal,

You can force a recalc of a UDF by 'marrying' it to a volatile function.
A silly example

=IF(NOW()1,YourUDF(x))

NOW() will always be 1, so it will always evaluate as TRUE, and your
UDF will be evaluated.

You could also use

=(NOW()<1)*YourUDF(x)

if your UDF returns a number.


HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept
parameters
and automatically update them. Having to put all parameters as
arguments
seems to partially defeat the usefulness of a UDF. I would ask "Why
does
Excel do it that way", but the answer is probably "That's the way it
is".

Is there another way around it? For example, can a parameter (like a
named cell) be declared, say, as "Global" so a subroutine or function
will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within UDFs,
so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in message
...
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" wrote in
message
...

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.

Cell reference in User Defined Function.
 

It would be helpful to see an example of a UDF where the named range is
used. Named ranges can be used in regular formulas, so, although I have
never tried it, I would assume they could be used with a UDF.


"Steve" wrote in message
...
I've been reading this post because I've run into a similar problem:

I have created several user defined functions. None of them happen to call
existing EXCEL functions. They're just simple arithmetical operations.
I have a spreadsheet that contains several named ranges of data.
I am computing a table of values and I want to use entries in the named
ranges as arguments to my UDFs.
If I enter as an argument to my UDF a named range, I get a "Value" error
in
the cell containing the the UDF. If I enter arguments to the UDF by
clicking
on the cells in the named range, the UDF returns the correct value.
I have created the UDFs and Ranges to make the programming in the
spreadsheet more readable and help me (and others locate errors).

Is it possible to do what I'm trying to do?

Steve
"Neal Carron" wrote:

Bernie,
You're quite right. I just made a dumb mistake.
Sorry, I hate bothering people because of my own mistakes.
Thanks for all your help.
- Neal

"Bernie Deitrick" wrote:

Neal,

I don't get your #VALUE! result. That usually results if your named
ranges
do not exist... Did you create the named ranges that your UDF is
using?

I used

=IF(NOW()1,fa(A2))
and
=fa(A2)

with this definition:

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

When I change the value in either of the named ranges NamedRangeX0 or
NamedRangeX1, the first formula updates, while the second one does not.

HTH,
Bernie
MS Excel MVP




"Neal Carron" wrote in message
...
Bernie,
Thanks, but it didn't work.
I removed the parameters (named cells) from the arguments and put
them
back
in the function definition.
When using either of your suggested methods, the function does not
recalc.
I
get #VALUE! in the cell.
The one remaining argument is a cell reference; the cell contains a
number.
All the parameters are named cells, each a number.
- Neal

"Bernie Deitrick" wrote:

Neal,

You can force a recalc of a UDF by 'marrying' it to a volatile
function.
A silly example

=IF(NOW()1,YourUDF(x))

NOW() will always be 1, so it will always evaluate as TRUE, and
your
UDF will be evaluated.

You could also use

=(NOW()<1)*YourUDF(x)

if your UDF returns a number.


HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in
message
...
Bernie,
OK, thanks. That works fine.
This is the first I've heard of a function that doesn't accept
parameters
and automatically update them. Having to put all parameters as
arguments
seems to partially defeat the usefulness of a UDF. I would ask
"Why
does
Excel do it that way", but the answer is probably "That's the way
it
is".

Is there another way around it? For example, can a parameter
(like a
named cell) be declared, say, as "Global" so a subroutine or
function
will
accept its latest value? Or is there some other way?
Thanks, Neal

"Bernie Deitrick" wrote:

Neal,

Excel dependency tree does not include cells called from within
UDFs,
so
Excel is unaware of the call to NamedRangeX0 and NamedRangeX1.

You could change the UDF to take three arguments

Function fa(x,y,z) As Double
fa = 1 - Application.WorksheetFunction.Tanh((x - y) / z)
End Function

and then call it like

=fa($E12,NamedRangeX0,NamedRangeX1)

HTH,
Bernie
MS Excel MVP


"Neal Carron" wrote in
message
...
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" wrote in
message
...

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.