What is default property of name object

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I can use
Range("A1:a3").Name = "data"
to define a name object.

but Range("A1:a3").Name will display the address of the name object

Could you explain why?

Clara
 
Hi Clara,

The default property of a Name is RefersTo, hence if the name refers to a
range returning the default property will return its address

Maybe you want this -

On error resume next
s = ""
s = Range("A1:a3").Name.Name
on error goto 0
If len(s) then
msgbox s & vbcr & Range("A1:a3").Name
else
msgbox "the range is not defined"
End if

Regards,
Peter T
 
Hi Peter,
Thank you very mcuh!
What I really mean is that there is an inconsistency here since we use
Range("A1:a3").Name = "data"( Set method) to assign a name, we can not
retrieve(Get) by referring to Range("A1:a3").Name

Clara
 
Hi again,

I see what you mean but it's not really an inconsistency

say you did -
Set rng = [a1]
then
v = rng
v returns the default property of the range which is its value, not the cell
reference or its name if it has one.

If you are trying to return your range's name then you need to return the
Name property of the Name object.
As before -
On error resume next ' in case the range doesn't have a Name
s = ""
s = Range("A1:a3").Name.Name

similarly
set nm = activeworkbook.names("test")
s = nm.Name ' 'test

Regards,
Peter T
 
Peter, Clara,

From Help:
Name property as it applies to the Font and Range objects.
Returns or sets the name of the object. The name of a Range object is a Name
object. For every other type of object, the name is a string. Read/write
Variant.
<This does not really help, as what does "the name of the object" mean in
this case ?>

So, if a Name has been set for the range:
?typename(Range("A1").name)
Name

Which makes sense.
But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo, but this is
clearly setting the Name.Name correctly.
It does seems some short-cut in the implementation, which, whilst useful,
can be confusing.

NickHK

Peter T said:
Hi again,

I see what you mean but it's not really an inconsistency

say you did -
Set rng = [a1]
then
v = rng
v returns the default property of the range which is its value, not the cell
reference or its name if it has one.

If you are trying to return your range's name then you need to return the
Name property of the Name object.
As before -
On error resume next ' in case the range doesn't have a Name
s = ""
s = Range("A1:a3").Name.Name

similarly
set nm = activeworkbook.names("test")
s = nm.Name ' 'test

Regards,
Peter T


clara said:
Hi Peter,
Thank you very mcuh!
What I really mean is that there is an inconsistency here since we use
Range("A1:a3").Name = "data"( Set method) to assign a name, we can not
retrieve(Get) by referring to Range("A1:a3").Name

Clara
Click to expand...
to
a
Click to expand...
Click to expand...
 
Hi Nick,

Re this bit -
But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo,
Click to expand...

Don't see any confusion here or why it "should not work"

A Range object has a Name property which is a Name object, assuming the
range is named.

As you say, the default property of the Name object is RefersTo which as
expected returns the string range reference (in the case of a range name).
This subject to the max 255 limit (or a bit less) even though the actual
defined range reference can be considerably more.

But a Name object also has a string Name property, similar to some other
objects, hence objDefinedName.Name returns the name of the Name.

Regards,
Peter T


NickHK said:
Peter, Clara,

From Help:
Name property as it applies to the Font and Range objects.
Returns or sets the name of the object. The name of a Range object is a Name
object. For every other type of object, the name is a string. Read/write
Variant.
<This does not really help, as what does "the name of the object" mean in
this case ?>

So, if a Name has been set for the range:
?typename(Range("A1").name)
Name

Which makes sense.
But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo, but this is
clearly setting the Name.Name correctly.
It does seems some short-cut in the implementation, which, whilst useful,
can be confusing.

NickHK

Peter T said:
Hi again,

I see what you mean but it's not really an inconsistency

say you did -
Set rng = [a1]
then
v = rng
v returns the default property of the range which is its value, not the cell
reference or its name if it has one.

If you are trying to return your range's name then you need to return the
Name property of the Name object.
As before -
On error resume next ' in case the range doesn't have a Name
s = ""
s = Range("A1:a3").Name.Name

similarly
set nm = activeworkbook.names("test")
s = nm.Name ' 'test

Regards,
Peter T


clara said:
Hi Peter,
Thank you very mcuh!
What I really mean is that there is an inconsistency here since we use
Range("A1:a3").Name = "data"( Set method) to assign a name, we can not
retrieve(Get) by referring to Range("A1:a3").Name

Clara
--
thank you so much for your help


:

Hi Clara,

The default property of a Name is RefersTo, hence if the name refers
Click to expand...
to
a
range returning the default property will return its address

Maybe you want this -

On error resume next
s = ""
s = Range("A1:a3").Name.Name
on error goto 0
If len(s) then
msgbox s & vbcr & Range("A1:a3").Name
else
msgbox "the range is not defined"
End if

Regards,
Peter T

Hi all,
I can use
Range("A1:a3").Name = "data"
to define a name object.

but Range("A1:a3").Name will display the address of the name object

Could you explain why?

Clara
Click to expand...
Click to expand...
Click to expand...
 
Peter,
Only that if you can do this to a non-default property of the Name object,
ie. Name.Name
Range("A1").Name="Name1"
what are you actually setting ?

Small point, but I understand Clara's confusion.

NickHK

Peter T said:
Hi Nick,

Re this bit -
But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo,
Click to expand...

Don't see any confusion here or why it "should not work"

A Range object has a Name property which is a Name object, assuming the
range is named.

As you say, the default property of the Name object is RefersTo which as
expected returns the string range reference (in the case of a range name).
This subject to the max 255 limit (or a bit less) even though the actual
defined range reference can be considerably more.

But a Name object also has a string Name property, similar to some other
objects, hence objDefinedName.Name returns the name of the Name.

Regards,
Peter T


NickHK said:
Peter, Clara,

From Help:
Name property as it applies to the Font and Range objects.
Returns or sets the name of the object. The name of a Range object is a Name
object. For every other type of object, the name is a string. Read/write
Variant.
<This does not really help, as what does "the name of the object" mean in
this case ?>

So, if a Name has been set for the range:
?typename(Range("A1").name)
Name

Which makes sense.
But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo, but
Click to expand...
this
is
clearly setting the Name.Name correctly.
It does seems some short-cut in the implementation, which, whilst useful,
can be confusing.

NickHK

Peter T said:
Hi again,

I see what you mean but it's not really an inconsistency

say you did -
Set rng = [a1]
then
v = rng
v returns the default property of the range which is its value, not
Click to expand...
the
cell
reference or its name if it has one.

If you are trying to return your range's name then you need to return the
Name property of the Name object.
As before -
On error resume next ' in case the range doesn't have a Name
s = ""
s = Range("A1:a3").Name.Name

similarly
set nm = activeworkbook.names("test")
s = nm.Name ' 'test

Regards,
Peter T


Hi Peter,
Thank you very mcuh!
What I really mean is that there is an inconsistency here since we use
Range("A1:a3").Name = "data"( Set method) to assign a name, we can not
retrieve(Get) by referring to Range("A1:a3").Name

Clara
--
thank you so much for your help


:

Hi Clara,

The default property of a Name is RefersTo, hence if the name
Click to expand...
refers
to
a
range returning the default property will return its address

Maybe you want this -

On error resume next
s = ""
s = Range("A1:a3").Name.Name
on error goto 0
If len(s) then
msgbox s & vbcr & Range("A1:a3").Name
else
msgbox "the range is not defined"
End if

Regards,
Peter T

Hi all,
I can use
Range("A1:a3").Name = "data"
to define a name object.

but Range("A1:a3").Name will display the address of the name object

Could you explain why?

Clara
Click to expand...
Click to expand...
Click to expand...
 
I follow the confusion and to a certain extent the oddity
Range("A1").Name="Name1"
what are you actually setting ?
Click to expand...

Not Set'ing but assigning a string name to the range's name-object property
(the property always exists even if the name doesn't). This in turn adds the
Name to the Names collection.

Regards,
Peter T

NickHK said:
Peter,
Only that if you can do this to a non-default property of the Name object,
ie. Name.Name
Range("A1").Name="Name1"
what are you actually setting ?

Small point, but I understand Clara's confusion.

NickHK

Peter T said:
Hi Nick,

Re this bit -
But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo,
Click to expand...

Don't see any confusion here or why it "should not work"

A Range object has a Name property which is a Name object, assuming the
range is named.

As you say, the default property of the Name object is RefersTo which as
expected returns the string range reference (in the case of a range name).
This subject to the max 255 limit (or a bit less) even though the actual
defined range reference can be considerably more.

But a Name object also has a string Name property, similar to some other
objects, hence objDefinedName.Name returns the name of the Name.

Regards,
Peter T


NickHK said:
Peter, Clara,

From Help:
Name property as it applies to the Font and Range objects.
Returns or sets the name of the object. The name of a Range object is
Click to expand...
a
Name
object. For every other type of object, the name is a string. Read/write
Variant.
<This does not really help, as what does "the name of the object" mean in
this case ?>

So, if a Name has been set for the range:
?typename(Range("A1").name)
Name

Which makes sense.
But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo, but
Click to expand...
this
is
clearly setting the Name.Name correctly.
It does seems some short-cut in the implementation, which, whilst useful,
can be confusing.

NickHK

Hi again,

I see what you mean but it's not really an inconsistency

say you did -
Set rng = [a1]
then
v = rng
v returns the default property of the range which is its value, not the
cell
reference or its name if it has one.

If you are trying to return your range's name then you need to
Click to expand...
return
the
Name property of the Name object.
As before -
On error resume next ' in case the range doesn't have a Name
s = ""
s = Range("A1:a3").Name.Name

similarly
set nm = activeworkbook.names("test")
s = nm.Name ' 'test

Regards,
Peter T


Hi Peter,
Thank you very mcuh!
What I really mean is that there is an inconsistency here since we use
Range("A1:a3").Name = "data"( Set method) to assign a name, we can not
retrieve(Get) by referring to Range("A1:a3").Name

Clara
--
thank you so much for your help


:

Hi Clara,

The default property of a Name is RefersTo, hence if the name refers
to
a
range returning the default property will return its address

Maybe you want this -

On error resume next
s = ""
s = Range("A1:a3").Name.Name
on error goto 0
If len(s) then
msgbox s & vbcr & Range("A1:a3").Name
else
msgbox "the range is not defined"
End if

Regards,
Peter T

Hi all,
I can use
Range("A1:a3").Name = "data"
to define a name object.

but Range("A1:a3").Name will display the address of the name
object

Could you explain why?

Clara
Click to expand...
Click to expand...
Click to expand...
 
Peter,
Succintly put.
I'm glad (most of) the rest of the Object Model is more straight-forward.

NickHK

Peter T said:
I follow the confusion and to a certain extent the oddity
Range("A1").Name="Name1"
what are you actually setting ?
Click to expand...

Not Set'ing but assigning a string name to the range's name-object
property
(the property always exists even if the name doesn't). This in turn adds
the
Name to the Names collection.

Regards,
Peter T

NickHK said:
Peter,
Only that if you can do this to a non-default property of the Name
object,
ie. Name.Name
Range("A1").Name="Name1"
what are you actually setting ?

Small point, but I understand Clara's confusion.

NickHK

Peter T said:
Hi Nick,

Re this bit -

But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo,

Don't see any confusion here or why it "should not work"

A Range object has a Name property which is a Name object, assuming the
range is named.

As you say, the default property of the Name object is RefersTo which
as
expected returns the string range reference (in the case of a range name).
This subject to the max 255 limit (or a bit less) even though the
actual
defined range reference can be considerably more.

But a Name object also has a string Name property, similar to some
other
objects, hence objDefinedName.Name returns the name of the Name.

Regards,
Peter T


Peter, Clara,

From Help:
Name property as it applies to the Font and Range objects.
Returns or sets the name of the object. The name of a Range object is a
Name
object. For every other type of object, the name is a string. Read/write
Variant.
<This does not really help, as what does "the name of the object"
mean in
this case ?>

So, if a Name has been set for the range:
?typename(Range("A1").name)
Name

Which makes sense.
But then
Range("A1").name="Name1"
?Range("A1").Name.name
Name1
?names("name1").RefersTo
=Sheet3!$A$1

should not work, as the default property for a Name is RefersTo, but this
is
clearly setting the Name.Name correctly.
It does seems some short-cut in the implementation, which, whilst useful,
can be confusing.

NickHK

Hi again,

I see what you mean but it's not really an inconsistency

say you did -
Set rng = [a1]
then
v = rng
v returns the default property of the range which is its value, not the
cell
reference or its name if it has one.

If you are trying to return your range's name then you need to return
the
Name property of the Name object.
As before -
On error resume next ' in case the range doesn't have a Name
s = ""
s = Range("A1:a3").Name.Name

similarly
set nm = activeworkbook.names("test")
s = nm.Name ' 'test

Regards,
Peter T


Hi Peter,
Thank you very mcuh!
What I really mean is that there is an inconsistency here since
we use
Range("A1:a3").Name = "data"( Set method) to assign a name, we
can not
retrieve(Get) by referring to Range("A1:a3").Name

Clara
--
thank you so much for your help


:

Hi Clara,

The default property of a Name is RefersTo, hence if the name refers
to
a
range returning the default property will return its address

Maybe you want this -

On error resume next
s = ""
s = Range("A1:a3").Name.Name
on error goto 0
If len(s) then
msgbox s & vbcr & Range("A1:a3").Name
else
msgbox "the range is not defined"
End if

Regards,
Peter T

Hi all,
I can use
Range("A1:a3").Name = "data"
to define a name object.

but Range("A1:a3").Name will display the address of the
name
object

Could you explain why?

Clara
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying a Name's Contents 8
[Q] How to create new worksheets based on an array (via named range) 4
How to count drawing object (text box) in vba 4
Variable SUM range 16
Set non-continuous PrintArea via Function / Name 0
Excel EXCEL - Set non-continuous PrintArea via Function / Name 0
Naming non-contigent cells with vba 5
Select Named range 4

Back
Top