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.

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.

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

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

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.

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.

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.

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.

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.

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.

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.

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.