Andrew,
Looks like you're getting great advice. Here's a sample I put together
from all the code that these nice folks have given you. It puts it all
together
so you can detect a Name, RangeName, or None of the above.
Function NameExists(WhatName As String, Optional WB As Workbook) As Boolean
Dim N As Long
On Error Resume Next
N = Len(IIf(WB Is Nothing, ThisWorkbook, WB).Names(WhatName).Name)
NameExists = (Err.Number = 0)
End Function
Function IsNameARange(WhatName As String, Optional WB As Workbook) As
Boolean
Dim NamedRange As Range
Dim TestWorkbook As Workbook
Set TestWorkbook = IIf(WB Is Nothing, ThisWorkbook, WB)
If NameExists(WhatName, TestWorkbook) = True Then
On Error Resume Next
Set NamedRange = TestWorkbook. _
Names(WhatName).RefersToRange()
IsNameARange = (Err.Number = 0)
Else
IsNameARange = False
End If
End Function
Sub TestRangeName()
Dim NameRange As Range
Dim TestRange As Range
Dim Test1 As String
Dim Test2 As String
Dim Test3 As String
Test1 = "pi"
Test2 = "MyTestRange"
Test3 = "Bob"
ActiveWorkbook.Names.Add Test1, "=3.14159"
Set NameRange = ActiveWorkbook.Worksheets("Sheet1").Range("a1:b3")
NameRange.Name = Test2
If NameExists(Test1) = True Then
If IsNameARange(Test1) = True Then
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c1").Formula = Test1 & " is a range"
Else
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c1").Formula = Test1 & " is a Name"
End If
Else
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c1").Formula = Test1 & " is Not a Name"
End If
If NameExists(Test2) = True Then
If IsNameARange(Test2) = True Then
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c2").Formula = Test2 & " is a Range Name"
Else
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c2").Formula = Test2 & " is a Name"
End If
Else
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c2").Formula = Test2 & " is Not a Name"
End If
If NameExists(Test3) = True Then
If IsNameARange(Test3) = True Then
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c3").Formula = Test3 & " is a Range Name"
Else
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c3").Formula = Test3 & " is a Name"
End If
Else
ActiveWorkbook.Worksheets("Sheet1"). _
Range("c3").Formula = Test3 & " is Not a Name"
End If
End Sub
Stephen Culhane
(e-mail address removed)
Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| From: "Dana DeLouis" <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
<
[email protected]>
| Subject: Re: easy way to test if a Named Range exists
| Date: Wed, 9 Jul 2003 16:11:03 -0400
| Lines: 82
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <
[email protected]>
| Newsgroups: microsoft.public.excel.programming
| NNTP-Posting-Host: adsl-21-139-16.mia.bellsouth.net 66.21.139.16
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:396159
| X-Tomcat-NG: microsoft.public.excel.programming
|
| Looks like you are all set, but just be aware that a workbook name could
| refer to a Constant, Formula, or a Named Range.
|
| Sub demo()
| ActiveWorkbook.Names.Add _
| Name:="pi", _
| RefersTo:="=3.14159"
| End Sub
|
| I am guessing from your Subject line that you are testing if it refers to
a
| "Range."
| If so, you may want to also include "RefersToRange"
|
| Names("pi").RefersToRange ...etc
|
| --
| Dana DeLouis
| Windows XP & Office XP
| = = = = = = = = = = = = = = = = =
|
|
| | > I get it: Suppress errors and try the name. Return True
| > if no error appeared. I guessed Microsoft forgot to
| > implement a test like NameExists. Thanks!
| >
| > >-----Original Message-----
| > >Andrew,
| > >
| > >Try a function like
| > >
| > >Function Name(What As String, _
| > > Optional WB As Workbook) As Boolean
| > >
| > >Dim N As Long
| > >On Error Resume Next
| > >N = Len(IIf(WB Is Nothing, ThisWorkbook, WB).Names
| > (WhatName).Name)
| > >NameExists = (Err.Number = 0)
| > >
| > >End Function
| > >
| > >Then, you can call this with code like
| > > If NameExists("SomeName") = True Then
| > > ' name exists
| > > Else
| > > ' name does not exist
| > > End If
| > >
| > >
| > >--
| > >Cordially,
| > >Chip Pearson
| > >Microsoft MVP - Excel
| > >Pearson Software Consulting, LLC
| > >
www.cpearson.com (e-mail address removed)
| > >
| > >
| > >
| > >| > >> Is there an easier way to test if a Named Range exists
| > >> than this loop:
| > >>
| > >> 'a long-winded way to check if "ToolVersion" exists...
| > >> fnd = False
| > >> For Each x In ActiveWorkbook.Names
| > >> If x.Name = "ToolVersion" Then
| > >> fnd = True
| > >> Exit For
| > >> End If
| > >> Next x
| > >>
| > >> I need something like this:
| > >> if ActiveWorkbook.Names.Item("x").Exists then ...
| > >
| > >
| > >.
| > >
|
|
|
