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.