Searching for a Name

If you want to test to see if a name exists in a workbook, you can use the following function. It has been designed to work both as a worksheet function and as a VBA callable function, which makes it a little more complex than if it were designed for either job alone:

Function IsNameInWorkbook(sName As String) As Boolean Dim s As String Dim rng As Range

'See if name exists in workbook

'Force recalculation if used as worksheet function Application.Volatile

'Ignore errors On Error Resume Next

'Try to get reference to cell using function

Set rng = Application.Caller

Err.Clear

If rng Is Nothing Then

'Function was called by VBA code s = ActiveWorkbook.Names(sName).Name

Else

'Function was called by cell s = rng.Parent.Parent.Names(sName).Name

End If

'If no error,

name exists

If Err.Number

= 0 Then IsNamelnWorkbook = True

End Function

IsNamelnWorkbook has an input parameter sName, which is the required name as a string. The function has been declared volatile, so it recalculates when it is used as a worksheet function and the referenced name is added or deleted. The function first determines if it has been called from a worksheet cell by assigning the Application.Caller property to rng.

If it has been called from a cell, Application.Caller returns a Range object that refers to the cell containing the function. If the function has not been called from a cell, the Set statement causes an error, which is suppressed by the preceding On Error Resume Next statement. That error, should it have occurred, is cleared because the function anticipates further errors that should not be masked by the first error.

Next, the function uses an If test to see if rng is undefined. If so, the call was made from another VBA routine. In this case, the function attempts to assign the Name property of the Name object in the active workbook to the dummy variable s. If the name exists, this attempt succeeds and no error is generated. Otherwise an error does occur, but is once again suppressed by the On Error Resume Next statement.

If the function has been called from a worksheet cell, the Else clause of the If test identifies the workbook containing rng and attempts to assign the Name property of the required Name object to s. The parent of rng is the worksheet containing rng, and the parent of that worksheet is the workbook containing rng. Once again, an error will be generated if the name does not exist in the workbook.

Finally, IsNamelnWorkbook checks the Number property of the Err object to see if it is zero. If it is, the return value of the function is set to True because the name does exist. If there is a non-zero error number, the function is left to return its default value of False because the name does not exist.

You could use IsNamelnWorkbook in a spreadsheet cell as follows:

=IF(IsNameInWorkbook("John"),"John is ","John is not ")&"an existing name"

You could use the following procedure to ask the user to enter a name and determine its existence:

Sub TestName(

)

If IsNameInWorkbook(InputBox("What Name")

) Then

MsgBox

"Name exists"

Else

MsgBox

"Name does not exist"

End If

End Sub

Note that if you are searching for a local name, you must include its sheet name, in the form Sheet1!Name, in the previous examples.

If you invoke isNamelnWorkbook as a worksheet function and if the name John is present, you will get output like that shown in Figure 5-6.

fv niqmas.ntem-WitrBscn E -■ r=1

John "

=IF(lsNatme*nWQrkbooli(l,Jotin")rrlJohn is ",

j

"John is not "}&nan exisiing name"

A

B C

D E

l iJohn is ail existing name "

i 1

p

1 1 _ _

i" 4 ► m Named Ranges

name Test iD 'l t ,

1 ■ - ■ «111

Ready ¿31

Ijas] 0, eSP-laHjS

--Pil

Figure 5-6

0 0

Post a comment