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)