Searching for the Name of a Range

The Name property of the Range object returns the name of the range, if the range has a name and the RefersTo property of the Name object corresponds exactly to the range.

You might be tempted to display the name of a range rng with the following code:

MsgBox rng.Name

This code fails because the Name property of a Range object returns a Name object. The code will display the default property value of the Name object, which is its RefersTo property. What you want is the Name property of the Name object, so you must use:

MsgBox rng.Name.Name

This code only works if rng has a name. It will return a run-time error if rng does not have one. You can use the following code to display the names of the selected cells in the active sheet:

Sub

TestNameOfRange()

Dim nmName As Name

'See if range has a name

'Ignore errors

On Error Resume Next

'Try to get name

Set nmName = Selection.Name

'Display result

If nmName Is Nothing Then

MsgBox " Selection has no name"

Else

MsgBox nmName.Name

End If

End

Sub

If a range has more than one name, the first of the names, in alphabetical order, will be returned. When this macro is run, the output will look something like Figure 5-7.

Dn. J

n -

-' HamE 1 Insert Pac

c Leycut r-crmNlas

Dnta | Review View | Developer

m

_ * V

B4

Carrie

a

ft

B

C

0

E

F

G

|

H

! i

1

Name

Age

Sex

2

Shelly

26 F

3 i

J

Name

Age

Sex

Microsoft Excel

y

5

John

21 M

6

Mary

23 F

Database

Ï

Harry

16 M

S

Jack

26 M

ÖK

1

Elizabeth

43 F

10

Dan

55 M

11

12

13

_14_

n

8 H Hamad Ranges

Name i esL Name

L'HOGCllIll

n

■H

Average: 30,66666067

Count; 21 Sum: 184 :J|

Tl

ril i

gi lûira Q;

i+t

B

0 0

Post a comment