Application.Selection.Name gives "=Sheet3!$A$1"; I want the name

  • Thread starter Thread starter oldyork90
  • Start date Start date
O

oldyork90

x2010

I named a cell 'homecell' (Formulas->Define Name->Name = "homecell")

That cell is selected. The homecell name appears in Name box.

Shouldn't the following vba line then give me "homecell"? -->
Application.Selection.Name

It gives me "=Sheet3!$A$1"

Thank you
 
oldyork90 presented the following explanation :
x2010

I named a cell 'homecell' (Formulas->Define Name->Name = "homecell")

That cell is selected. The homecell name appears in Name box.

Shouldn't the following vba line then give me "homecell"? -->
Application.Selection.Name

It gives me "=Sheet3!$A$1"

Thank you
Click to expand...

Selection returns a 'fully absolute' address of the selected cells. If
you want to get the defined name you'll need to loop the names
collection for a matching absolute address. (This won't work if the
address has any 'relative' refs)

Example1: (uses Intersect function)
If Intersect(Selection, Range("homecell") Then "True" Else "False"

Example2: (compare address)
Select Case Selection.Address
Case Range("homecell").Address: "True"
Case Else: "False"
End Select 'Case Selection.Address

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Oops.., missing a closing parenthesis...

Example1: (uses Intersect function)
If Intersect(Selection, Range("homecell")) Then "True" Else "False"

...sorry about that!

Also, I mentioned looping the names collection but omitted an example
and so...

Dim nam As Variant
For Each nam In ActiveSheet.Names '//assumes local scope
If Selection.Name.Name = nam Then
'//do stuff
Exit For '//quit looking
End If
Next 'nam

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Selection returns a 'fully absolute' address of the selected cells. If
you want to get the defined name you'll need to loop the names
collection for a matching absolute address. (This won't work if the
address has any 'relative' refs)
Click to expand...

Thank you Garry

Ok, then shouldn't I be able to iterate here? (Didn't try to access a
member yet... didn't get past the debugger)

=============
dim n as Name

For Each n In Application.Selection.Names
Debug.Print "Here we are"
Next
===============

The error message says not supported. It's probably something dumb
I'm doing. The name is assigned to an absolute address. I also tried
selecting a multi cell range hoping that maybe the collection of names
would then populate. I may not understand what I see in the object
browser. This looks doable. Set me straight. Thanks again.
 
oldyork90 was thinking very hard :
Thank you Garry

Ok, then shouldn't I be able to iterate here? (Didn't try to access a
member yet... didn't get past the debugger)

=============
dim n as Name

For Each n In Application.Selection.Names
Debug.Print "Here we are"
Next
===============

The error message says not supported. It's probably something dumb
I'm doing. The name is assigned to an absolute address. I also tried
selecting a multi cell range hoping that maybe the collection of names
would then populate. I may not understand what I see in the object
browser. This looks doable. Set me straight. Thanks again.
Click to expand...

Well, it doesn't look anything like the sample I posted and so it
doesn't surprise me that you're not getting different results than your
original post.

VBA is extremely syntax sensitive, and so you can't just make stuff up
and expect it to work. For example, what leads you to believe that
'Application.Selection.Names' is proper VBA syntax for referencing a
'Names' collection?

'Names' (note it's plural) is a collection that belongs to a Workbook
(if it has global scope), or a Worksheet (if it has local scope). That
means you must loop the collection in reference to a workbook object OR
a worksheet object.

Selection is a VBA reference to a cell or group of cells. So if try to
access 'Names' in reference to 'Selection' you get an error because
'Selection' doesn't support 'Names'. It does support a 'Name' property
(note it's NOT plural here) *if it returns an absolute address assigned
to a DefinedName*, otherwise it also will throw an error. The value
returned for "Selection.Name" (as already stated in my original reply)
returns an absolute address. If this address has a defined name then it
can be returned by using "Selection.Name.Name" (as noted in Jim Cone's
reply as well as how used in my code sample for looping the Names
collection)

The sample code in my reply shows the correct way to iterate the names
collection of the active sheet IF the names defined have local
(worksheet-level) scope. This *should be the default scope* unless
there's some highly extenuating reason to define names with global
(workbook-level) scope.

I suspect your defined name "homecell" actually does have global scope
and so it would be a member of the workbooks object. In this caes
you'll need to swap out *ActiveSheet* for *ActiveWorkbook* in the
For..Each loop.

Also, (no big deal!) I chose to use type Variant for the iterator
because this is a habit I got into for using For..Each loops so I don't
have to worry about what type the collection item is in reusable loop
code. IOW, I can use the same code to loop the Worksheets collection
(or any other objects) without having to explicitly modify the code. As
I said, this is no big deal but I just prefer not to proliferate code
with unecessary loops when I can reuse utility code designed for that
purpose.<g>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Garry

Well my question wasn't directly about your answer so I'm not
surprised it doesn't look anything like it. I was trying to
understand something about Selection via restatement. Your code is
fine. Bottom line is I don't understand what I'm seeing (or not
seeing) in the object browser. I need to experiment, and think real
hard, for awhile. Thanks again for your help.

Thanks again.
 
oldyork90 pretended :
Garry

Well my question wasn't directly about your answer so I'm not
surprised it doesn't look anything like it. I was trying to
understand something about Selection via restatement. Your code is
fine. Bottom line is I don't understand what I'm seeing (or not
seeing) in the object browser. I need to experiment, and think real
hard, for awhile. Thanks again for your help.

Thanks again.
Click to expand...

For the most part, using the ObjectBrowser is a good way to get to know
about properties/methods. I find it very useful for straight forward
stuff, and F1 is always ready to display extended info on whatever
happens to be selected.

Unfortunately, you won't see anything that resembles
"Selection.Name.Name" because the OB and Help don't offer much info.
'Selection' is listed under 'Application' as a method that returns a
'Range' object, but it doesn't explain that it does this via its
absolute address. In this case you could see that there are various
possible ways to work with 'Selection'...

Selection.Address Selection.Name

...return the same location as what you see in the NameBox as an
absolute value ("$" sign prefix to col/row label).

At this point you have a fully qualified ref to a 'Range' and so you
can get its 'Name' if one was assigned to the selected range by
extending it like this...

Selection.Name.Name

...the problem with using the above is that it only returns a value IF a
name has been assigned, otherwise it throws an error. This is why I
omitted an example of it in my 1st reply. Also, Ron's contribution
applies here too, and so is why I explicitly ref the named ranges in my
1st reply. Since using the above syntax leaves lots of room for error
and/or ambiguity I wouldn't use it in general coding.

The 2 methods I showed are going to give you reliable results because
they ref specific ranges of interest that you can code for without
guessing what is selected. Notice that there's no reason for either
method to throw an error, AND allows no chance of ambiguity.

You could also assign 'Selection' to a variable typed as Variant,
Object, or Range...

Dim rng As Range
Set rng = Selection

Dim oRng As Object
Set oRng = Selection

...to get a fully qualified ref to the cells. In this usage you can test
for a name as follows:

Dim sRngName As String
On Error Resume Next '//handle error if name doesn't exist
sRngName = rng.Name (OR oRng.Name)
If sRngName = "" Then MsgBox "The selection doesn't have a name!"

-OR-

Dim sRngName As String
On Error GoTo errexit '//handle error if name doesn't exist
sRngName = rng.Name (OR oRng.Name)

NormalExit:
Exit Sub

errexit:
MsgBox "The selection doesn't have a name!"
End Sub


You can use the Variant type if you want to step through each cell.
This is faster than working directly in the worksheet...

Dim vRng As Variant, i As Long, j As Long
Set vRng = Selection '//load cell contents into a 1-based 2D array

If Not IsArray(vRng) Then Exit Sub

'Get the number of Rows/Cols
Dim lRows As Long, lCols As Long
lRows = UBound(vRng): lCols = UBound(vRng, 2)

'Step through each cell col-by-col for each row
For i = LBound(vRng) To UBound(vRng) 'For i = 1 To lRows
For j = LBound(vRng, 2) To UBound(vRng, 2) 'For j = 1 To lCols
'//do stuff to cell in each col of Rows(i)
Next 'j
Next 'i

-OR-

'Step through each cell row-by-row for each col
For i = LBound(vRng, 2) To UBound(vRng, 2)
For j = LBound(vRng) To UBound(vRng)
'//do stuff to cell in each row of Columns(i)
Next 'j
Next 'i

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
oldyork90 pretended :




For the most part, using the ObjectBrowser is a good way to get to know
about properties/methods. I find it very useful for straight forward
stuff, and F1 is always ready to display extended info on whatever
happens to be selected.

Unfortunately, you won't see anything that resembles
"Selection.Name.Name" because the OB and Help don't offer much info.
'Selection' is listed under 'Application' as a method that returns a
'Range' object, but it doesn't explain that it does this via its
absolute address. In this case you could see that there are various
possible ways to work with 'Selection'...

  Selection.Address    Selection.Name

..return the same location as what you see in the NameBox as an
absolute value ("$" sign prefix to col/row label).

At this point you have a fully qualified ref to a 'Range' and so you
can get its 'Name' if one was assigned to the selected range by
extending it like this...

  Selection.Name.Name

..the problem with using the above is that it only returns a value IF a
name has been assigned, otherwise it throws an error. This is why I
omitted an example of it in my 1st reply. Also, Ron's contribution
applies here too, and so is why I explicitly ref the named ranges in my
1st reply. Since using the above syntax leaves lots of room for error
and/or ambiguity I wouldn't use it in general coding.

The 2 methods I showed are going to give you reliable results because
they ref specific ranges of interest that you can code for without
guessing what is selected. Notice that there's no reason for either
method to throw an error, AND allows no chance of ambiguity.

You could also assign 'Selection' to a variable typed as Variant,
Object, or Range...

  Dim rng As Range
  Set rng = Selection

  Dim oRng As Object
  Set oRng = Selection

..to get a fully qualified ref to the cells. In this usage you can test
for a name as follows:

  Dim sRngName As String
  On Error Resume Next '//handle error if name doesn't exist
  sRngName = rng.Name  (OR  oRng.Name)
  If sRngName = "" Then MsgBox "The selection doesn't have a name!"

-OR-

  Dim sRngName As String
  On Error GoTo errexit '//handle error if name doesn't exist
  sRngName = rng.Name  (OR  oRng.Name)

  NormalExit:
  Exit Sub

  errexit:
  MsgBox "The selection doesn't have a name!"
  End Sub

You can use the Variant type if you want to step through each cell.
This is faster than working directly in the worksheet...

  Dim vRng As Variant, i As Long, j As Long
  Set vRng = Selection '//load cell contents into a 1-based 2D array

  If Not IsArray(vRng) Then Exit Sub

  'Get the number of Rows/Cols
  Dim lRows As Long, lCols As Long
  lRows = UBound(vRng): lCols = UBound(vRng, 2)

  'Step through each cell col-by-col for each row
  For i = LBound(vRng) To UBound(vRng)  'For i = 1 To lRows
    For j = LBound(vRng, 2) To UBound(vRng, 2)  'For j = 1 To lCols
      '//do stuff to cell in each col of Rows(i)
    Next 'j
  Next 'i

-OR-

  'Step through each cell row-by-row for each col
  For i = LBound(vRng, 2) To UBound(vRng, 2)
    For j = LBound(vRng) To UBound(vRng)
      '//do stuff to cell in each row of Columns(i)
    Next 'j
  Next 'i

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
Click to expand...

Damn Garry... I feel like I should be paying for this. Thank you so
much. It helps. Thank you.
 
Back
Top