|
easy way to test if a Named Range exists
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 ... |
easy way to test if a Named Range exists
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 ... |
easy way to test if a Named Range exists
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 ... . |
easy way to test if a Named Range exists
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 ... . |
| All times are GMT +1. The time now is 09:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com