Put Name's Refer's to: field in cell

[kittronald]

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.

 

[GS]

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

 

[kittronald]

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.

 

[kittronald]

Garry,

How could I use this in a macro ?

The following doesn't work.

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



- Ronald K.

 

[Clif McIrvin]

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).RefersTo)

 

[GS]

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

 

[Rick Rothstein]

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

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)

 

[Rick Rothstein]

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]

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.

 

[kittronald]

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.

 

[kittronald]

Clif,

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

x = GetRefersTo("Test")



- Ronald K.

 

[kittronald]

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

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)

 

[Rick Rothstein]

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)

 

[GS]

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>

 

[GS]

Rick Rothstein explained :

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>

 

[Rick Rothstein]

Function GetRefersTo(DefinedName As String) As Variant

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)

 

[Clif McIrvin]

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!

 

[GS]

Rick Rothstein formulated on Monday :

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.

 

[kittronald]

Clif,

Your input is always welcome.



- Ronald K.