Is it possible to create a UDF that returns a defined name's Refers to:
field as text without the equal sign ?

For example, the name "Test" has a Refers to: field equal to
=SUM(A1,B1).

The UDF might function like ...

=GetRefersTo("Test")

... and return ...

SUM(A1,B1)



- Ronald K.

kittronald pretended :
Is it possible to create a UDF that returns a defined name's Refers to:
field as text without the equal sign ?

For example, the name "Test" has a Refers to: field equal to =SUM(A1,B1).

The UDF might function like ...

=GetRefersTo("Test")

... and return ...

SUM(A1,B1)



- Ronald K.

Function GetRefersTo(Name As String)
GetRefersTo = Mid$(Names(Name).RefersTo,2)
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Thanks, that works !

Getting this worksheet working is like pushing a string uphill and this
UDF helps a lot.

Is there a way to input the defined name without quotes ?

For example, =GetRefersTo("Test") versus =GetRefersTo(Test)



- Ronald K.

Garry,

How could I use this in a macro ?

The following doesn't work.

x = Application.GetRefersTo(Activeworkbook.Names("Test "))



- Ronald K.

"kittronald" wrote in message
...
Garry,

How could I use this in a macro ?

The following doesn't work.

x = Application.GetRefersTo(Activeworkbook.Names("Test "))




Ronald, take another look at Garry's UDF ... If I understand your
questions correctly, he already showed you answers to both your
questions:

y = "Test"
x = Application.GetRefersTo(Activeworkbook.Names(y).Re fersTo)

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif,

Thanks for the response, although I'm not sure I understand.

Garry created a UDF that works perfectly in a cell.

I was trying to use that UDF in a macro to read the Refers to: field of
a name "Test" and assign it to the variable "x".




- Ronald K.

Is there a way to input the defined name without quotes ?

For example, =GetRefersTo("Test") versus =GetRefersTo(Test)

How could I use this in a macro ?

The following doesn't work.

x = Application.GetRefersTo(Activeworkbook.Names("Test "))

Here is a function that answers what you asked for in the first question
above...

Function GetRefersTo(DefinedName As Range) As Variant
Dim N As Variant
On Error Resume Next
N = DefinedName.Name
If Len(N) Then
GetRefersTo = Mid$(DefinedName.Name, 2)
ElseIf DefinedName.Count = 1 Then
GetRefersTo = Mid(DefinedName.Formula, 1 - (Left(DefinedName.Formula, 1)
= "="))
Else
GetRefersTo = CVErr(xlErrRef)
End If
End Function

Assuming Test is a defined name, you can use this formula as a UDF on a
worksheet...

=GetRefersTo(Test)

You can also pass in a cell reference... if that cell has a formula, the
formula (without the equal sign) is returned; if the cell does not contain a
formula, then the cell's value is returned (I can change the functionality
for these if you want).

As for your second question, in the VBA world, an Excel Defined Name does
not reference anything (VBA will think it is a variable) and the same is
true for a cell address (such as, A1). If you use either of these in a code
statement, VBA will try to interpret them as a variable. The only way you
can use them is to quote them inside a Range call. So, you can pass
Range("Test") or Range("A1") into the function and it will return the same
results as discussed above for when the function is used as a UDF. For
example...

X = GetRefersTo(Range("Test"))

Y = GetRefersTo(Range("A1"))

Oh, one more thing... if you pass in a multi-cell address or a non-Defined
Name, then the function will return an error.

Rick Rothstein (MVP - Excel)

Here is a simplified UDF, probably more in keeping what you originally asked
for. If you pass it a Defined Name, it will return what that Defined Name
refers to... pass it anything else and it will return a #REF! error. From
the worksheet, as a UDF, you can pass it the Defined Name or a text string
or the Defined Name; so, either of these formulas will work for a Defined
Name of Test...

=GetRefersTo(Test)

=GetRefersTo("Test")

If you call it from other VB code, you must pass the Defined Name as a
String value (if you leave off the quote marks, VB will try to evaluate it
as a variable)...

X = GetRefersTo("Test")

Remember, for this version, any non-Defined Name will return an error. Okay,
here is the code...

Function GetRefersTo(DefinedName As Variant) As Variant
Dim N As Variant
On Error Resume Next
GetRefersTo = DefinedName.Name
If Err.Number Then GetRefersTo = Range(DefinedName).Name
If Len(GetRefersTo) = 0 Then GetRefersTo = CVErr(xlErrRef)
End Function

Rick Rothstein (MVP - Excel)

kittronald has brought this to us :
Garry,

Thanks, that works !

Getting this worksheet working is like pushing a string uphill and this
UDF helps a lot.

Is there a way to input the defined name without quotes ?

For example, =GetRefersTo("Test") versus =GetRefersTo(Test)



- Ronald K.

Ron,
If you use the function as I wrote it, any quotes that return are in
the name's RefersTo. If you don't want them there then edit the
RefersTo in the DN dialog. Otherwise, you could strip them out by
wrapping the entire statement to the right of the equal sign in a
Replace() function.

Example:
Function GetRefersTo(Name As String)
GetRefersTo = Replace(Mid$(Names(Name).RefersTo,2),Chr(34),"")
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Actually, I was referring to using the UDF in the Formula bar.

Currently, the name has to be wrapped in quotes (i.e.,
=GetRefersTo("Test") ) as opposed to =GetRefersTo(Test).

I should have been clearer in my post - my mistake. :(



- Ronald K.

on 10/2/2011, kittronald supposed :
Garry,

Actually, I was referring to using the UDF in the Formula bar.

Currently, the name has to be wrapped in quotes (i.e.,
=GetRefersTo("Test") ) as opposed to =GetRefersTo(Test).

I should have been clearer in my post - my mistake. :(



- Ronald K.

That would be the only way to do it since Names ARE string values in
all cases. Otherwise, Excel will think you're refering to a defined
formula name's result. If you READ the declaration in the function..,
it specifies what the data type being passed in needs to be. FYI, I
tested that function before posting it and it works EXACTLY as
expected.<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc