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

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

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)

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.

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.

Clif,

Figured out how to get the UDF to work in a macro.

x = GetRefersTo("Test")



- Ronald K.

Rick,

Wow, you give new meaning to technical support !

I created a "Test" name where the Refers to: field is equal to
=SUM($A$1,$B$1) and then inserted your code in a module:

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


In C1, I input:

=GetRefersTo(Test).

The result is #REF.

What am I doing wrong ?

How is the N variable used ?



- Ronald K.

Rick
Wow, you give new meaning to technical support !

Yes but, as it turns out, that support is not very good.<g I took a
shortcut and did all my testing with Defined Names being ranges. It appears
that when a formula is assigned to a Defined Name, the name object cannot be
passed without the quote marks as Excel evaluates the formulas before
passing it as an argument. So, as long as we acknowledge the argument must
be quoted, then this simple UDF seems like it should work...

Function GetRefersTo(DefinedName As String) As Variant
If Not Names(DefinedName) Is Nothing Then _
GetRefersTo = Mid(Names(DefinedName).RefersTo, 2)
End Function

Note that the function is a one-liner... I used the line continuation
character to avoid having the message possibly be word-wrapped at an awkward
spot.

Rick Rothstein (MVP - Excel)

Function GetRefersTo(DefinedName As String) As Variant
If Not Names(DefinedName) Is Nothing Then _
GetRefersTo = Mid(Names(DefinedName).RefersTo, 2)
End Function

Actually, that function is more complicated than it needs to be...

Function GetRefersTo(DefinedName As String) As Variant
GetRefersTo = Mid(Names(DefinedName).RefersTo, 2)
End Function

Remember, your passed in argument must be quoted.

Rick Rothstein (MVP - Excel)

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

Rick Rothstein explained :
Function GetRefersTo(DefinedName As String) As Variant
If Not Names(DefinedName) Is Nothing Then _
GetRefersTo = Mid(Names(DefinedName).RefersTo, 2)
End Function

Actually, that function is more complicated than it needs to be...

Function GetRefersTo(DefinedName As String) As Variant
GetRefersTo = Mid(Names(DefinedName).RefersTo, 2)
End Function

Remember, your passed in argument must be quoted.

Rick Rothstein (MVP - Excel)

Rick,
This looks very familiar!<bg

--
Garry

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

Function GetRefersTo(DefinedName As String) As Variant
GetRefersTo = Mid(Names(DefinedName).RefersTo, 2)
End Function

Remember, your passed in argument must be quoted.

Rick,
This looks very familiar!<bg

Yes, now that I look at the whole thread, it does look similar.<g Why did
you include the Replace function call though, I'm not sure I see a need for
it?

Rick Rothstein (MVP - Excel)

"kittronald" wrote in message
...
Clif,

Figured out how to get the UDF to work in a macro.

x = GetRefersTo("Test")




I see there's a lot more information in the thread this morning <g.

I think part of the confusion came from me trying to "guess" exactly
what you were asking ... and not quite getting it right.

Glad you got it sorted!

--
Clif McIrvin

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

Rick Rothstein formulated on Monday :
Function GetRefersTo(DefinedName As String) As Variant
GetRefersTo = Mid(Names(DefinedName).RefersTo, 2)
End Function

Remember, your passed in argument must be quoted.

Rick,
This looks very familiar!<bg

Yes, now that I look at the whole thread, it does look similar.<g Why did
you include the Replace function call though, I'm not sure I see a need for
it?

Rick Rothstein (MVP - Excel)

Original post did not include the Replace() function. Ron asked how to
remove the quotes if the return value had them. The Replace() function
was the simplest way I could think of!<g Normally, I'd use Evaluate()
as demonstrated in another post related to this subject.

--
Garry

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

Clif,

Your input is always welcome.



- Ronald K.

Garry, Rick and Clif,


The UDF is working correctly within the macro as wanted. 8)

Getting the UDF to work in a cell was a backup solution since I'm
learning to write macros on the spot.

Why is it when you create a defined name called "TEST" with a Refers to:
field equal to =SUM(A1,B1) and type =SUM(TEST,1), you don't have to wrap the
name TEST in quotes ?

Thanks for the help guys !



- Ronald K.

Why is it when you create a defined name called "TEST" with a
Refers to: field equal to =SUM(A1,B1) and type =SUM(TEST,1),
you don't have to wrap the name TEST in quotes ?

Characters wrapped with quote marks is text... the argument to the SUM
function is not a text string... if you used "TEST" as the argument, Excel's
evaluator would not see the defined name TEST, rather, it would see just the
individual characters T, E, S and T and would assign no meaning to them.

Rick Rothstein (MVP - Excel)