Check if a named range exists with VBA?

  • Thread starter Thread starter mcolson1590
  • Start date Start date
M

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
 
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
Click to expand...


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
 
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
Click to expand...

That worked great.
 
Back
Top