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
