Listing Validating Names Using the Range NameExists Procedure

' Checks for the existence of a named range on a worksheet Function RangeNameExists(ws As Worksheet, sName As String) As Boolean Dim s As String

On Error GoTo ErrHandler s = ws.Range(sName).Address

RangeNameExists = True Exit Function

ErrHandler:

RangeNameExists = False End Function

Sub Va1idateNamedRangeExamp1e()

If RangeNameExists(ThisWorkbook.Worksheets(1), "Test") Then MsgBox "The name exists, it refers to: " & _ ThisWorkbook.Names("Test").RefersTo, _ vbOKOnly

Else

MsgBox "The name does not exist", vbOKOnly End If

If RangeNameExists(ThisWorkbook.Worksheets(1), "djfs") Then MsgBox "The name exists, it refers to: " & _

ThisWorkbook.Worksheets(1).Names("djfs").RefersTo, _

vbOKOnly

Else

MsgBox "The name does not exist", vbOKOnly End If

End Sub

RangeNameExists returns a Boolean value that is true if the range name exists, false otherwise. You may recognize the basic structure of this procedure from the chapter covering the Worksheet object. It's nearly identical to the procedure you use to test for the existence of a worksheet. This procedure relies on error handling to catch the error that occurs if the name doesn't exist and returns the appropriate value.

One catch with RangeNameExists is that it's set up to validate worksheet-named ranges. It also works on workbook-named ranges, provided you supply the worksheet on which the named range is defined. The ValidateNamedRangeExample procedure just demonstrates how you might call Range-NameExists from another procedure.

0 0

Post a comment