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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <skipper1...@yahoo.com> wrote:
> <mcolson1...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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.