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

Matt

Chip Pearson describes Named Ranges at:

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

Regards

Trevor


wrote in message
ps.com...
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

wrote in message
ps.com...
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

On Jan 30, 5:41 pm, "Skip" wrote:
wrote in message

ps.com...

I am trying to see if anamedrange exists with VBA. In my
subroutine, I browse for and then open another Excel file. I then set
rangeson that sheet = to defined variables. I would like to verify
that theserangesexiston that sheet before trying to set them (in
order to avoid an error in running). For example, does that sheet
have anamedrange "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

That worked great.