Validate Named Range Exists
 

I need a little assistance. I have a workbook that has many named ranges
from single cell to multiple cells. As users do get in and play and screw
things up, I wanted to create a function that will validate a named range
exists before my code tries to go do something with that range. Is there a
way to do that?

Validate Named Range Exists
 

Try something like the following code:


Function IsValidName(Nm As String) As Boolean

Dim N As Name
On Error Resume Next
Set N = ThisWorkbook.Names(Nm)
If N Is Nothing Then
IsValidName = False
Else
If InStr(1, N.RefersTo, "#REF") 0 Then
IsValidName = False
Else
IsValidName = True
End If
End If

End Function


You can then call this function from code like

If IsValidName("TheName") = True Then
' good name
Else
' bad name
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John Jost" wrote in message
...
I need a little assistance. I have a workbook that has many
named ranges
from single cell to multiple cells. As users do get in and
play and screw
things up, I wanted to create a function that will validate a
named range
exists before my code tries to go do something with that range.
Is there a
way to do that?

Validate Named Range Exists
 

John,

Sub TryNow()
Dim NameIsOK As Boolean
NameIsOK = GoodName("TestName")
If NameIsOK Then
MsgBox "It's good"
Else
MsgBox "It's missing"
End If
End Sub

Function GoodName(myName As String) As Boolean
Dim CurName As String

GoodName = False
On Error GoTo NoName
CurName = Range(myName).Name
GoodName = True
NoName:
End Function

HTH,
Bernie
MS Excel MVP


"John Jost" wrote in message
...
I need a little assistance. I have a workbook that has many named ranges
from single cell to multiple cells. As users do get in and play and screw
things up, I wanted to create a function that will validate a named range
exists before my code tries to go do something with that range. Is there a
way to do that?

Validate Named Range Exists
 

This is a simple function to test for a named range... Probably an easier
way... but this works.


Function named_range_exists(nr As String) As Boolean
Dim rChk As Integer
named_range_exists = True
On Error Resume Next
rChk = ActiveSheet.Range(nr).Cells.Count
If Err.Number < 0 Then named_range_exists = False
On Error GoTo 0
End Function



"John Jost" wrote in message
...
I need a little assistance. I have a workbook that has many named ranges
from single cell to multiple cells. As users do get in and play and screw
things up, I wanted to create a function that will validate a named range
exists before my code tries to go do something with that range. Is there
a
way to do that?

Validate Named Range Exists
 

Option Explicit
Function OkRangeName(myName As String) As Boolean
On Error Resume Next
OkRangeName = Not CBool(ActiveWorkbook.Names(myName).RefersToRange Is Nothing)
On Error GoTo 0
End Function

'I named a cell "hi" and didn't name a range "hithere"
'and tested with

Sub testme()
MsgBox OkRangeName("hi")
MsgBox OkRangeName("hithere")
End Sub

John Jost wrote:

I need a little assistance. I have a workbook that has many named ranges
from single cell to multiple cells. As users do get in and play and screw
things up, I wanted to create a function that will validate a named range
exists before my code tries to go do something with that range. Is there a
way to do that?

--

Dave Peterson

Validate Named Range Exists
 

Wow, I'm very thankful for so many options from which to choose. Thanks one
and all.

"John Jost" wrote:

I need a little assistance. I have a workbook that has many named ranges
from single cell to multiple cells. As users do get in and play and screw
things up, I wanted to create a function that will validate a named range
exists before my code tries to go do something with that range. Is there a
way to do that?