Check if a named range exists with VBA?

[mcolson1590]

I am trying to see if a named range exists with VBA. In my
subroutine, I browse for and then open another Excel file. I then set
ranges on that sheet = to defined variables. I would like to verify
that these ranges exist on that sheet before trying to set them (in
order to avoid an error in running). For example, does that sheet
have a named range "Name"?

Thanks for any help,

Matt

 

[Trevor Shuttleworth]

Matt

Chip Pearson describes Named Ranges at:

http://www.cpearson.com/excel/named.htm

Regards

Trevor

 

[Skip]

I am trying to see if a named range exists with VBA. In my
subroutine, I browse for and then open another Excel file. I then set
ranges on that sheet = to defined variables. I would like to verify
that these ranges exist on that sheet before trying to set them (in
order to avoid an error in running). For example, does that sheet
have a named range "Name"?

Thanks for any help,

Matt

Try this function. You have to supply the range name you are interested in,
it returns True/False

Public Function RangeNameExists(argRangeName As String) As Boolean
' Returns TRUE if the range name exists
' Include the sheet name in argRangeName.
' i.e. "Sunday!freezer25" where the sheet name is Sunday and the range
name is freezer25
Dim n As Name
RangeNameExists = False
For Each n In ActiveWorkbook.Names
If UCase(n.Name) = UCase(argRangeName) Then
RangeNameExists = True
Exit Function
End If
Next n
End Function

 

[mcolson]

Try this function. You have to supply the range name you are interested in,
it returns True/False

Public Function RangeNameExists(argRangeName As String) As Boolean
' Returns TRUE if the range name exists
' Include the sheet name in argRangeName.
' i.e. "Sunday!freezer25" where the sheet name is Sunday and the range
name is freezer25
Dim n As Name
RangeNameExists = False
For Each n In ActiveWorkbook.Names
If UCase(n.Name) = UCase(argRangeName) Then
RangeNameExists = True
Exit Function
End If
Next n
End Function

That worked great.