Cell reference in User Defined Function.

[Neal Carron]

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

All Worksheetfunctions should be referred as below in VBA

1-Worksheetfunction.Tanh(.... )

If this post helps click Yes

 

[Chip Pearson]

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)

 

[Neal Carron]

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

 

[Neal Carron]

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

 

[Bernie Deitrick]

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

 

[Jacob Skaria]

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

 

[Dougaj4]

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






- 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

 

[Neal Carron]

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?

 

[Neal Carron]

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

 

[Bernie Deitrick]

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]

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]

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

 

[Niek Otten]

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.

 

[Neal Carron]

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

 

[Neal Carron]

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]

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]

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

 

[Steve]

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

 

[JLGWhiz]

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.