[Andrew Bauer]

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 ...

[Chip Pearson]

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



"Andrew Bauer" wrote in message
...
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 ...

[Andrew Bauer]

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



"Andrew Bauer" wrote in message
...
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 ...


.

[Dana DeLouis[_5_]]

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
= = = = = = = = = = = = = = = = =


"Andrew Bauer" wrote in message
...
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



"Andrew Bauer" wrote in message
...
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 ...


.

[Steve Culhane [MS]]

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

Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.





--------------------
| From: "Dana DeLouis"
| References:


| Subject: 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:
| 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!TK2MSFTN GP10.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
| = = = = = = = = = = = = = = = = =
|
|
| "Andrew Bauer" wrote in message
| ...
| 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
|
|
|
| "Andrew Bauer" wrote in message
| ...
| 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 ...
|
|
| .
|
|
|
|