if statement

[Guest]

Need an if statement to check if a certain named data range (call it
"testrange") exists or not? I am going to create it if it is not there, and
if it is, I need to delete it. So have to check, otherwise will get run
error.

Thx.

 

[Bob Phillips]

Public Function NameExists(val As String)
Dim nme
On Error Resume Next
nme = ActiveWorkbook.Names(val)
On Error GoTo 0
NameExists = (Not IsEmpty(nme))
End Function


If NameExists("xyz" Then
...

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

One way:

Public Sub ToggleTestRange()
Const sNAME As String = "testrange"
Dim nm As Name
With ActiveWorkbook
On Error Resume Next
Set nm = .Names(sNAME)
On Error GoTo 0
If nm Is Nothing Then
.Names.Add Name:=sNAME, RefersTo:="some value"
Else
nm.Delete
End If
End With
End Sub


Or if you just want to create the name, but delete any existing copy
before creating the new one:

Public Sub MakeTestRange()
Const sNAME As String = "testrange"
With Application
On Error Resume Next
.Names(sNAME).Delete
On Error GoTo 0
.Names.Add Name:=sNAME, RefersTo:="some value"
End With
End Sub

 

[George Nicholson]

So have to check, otherwise will get run error.

Checking to see if something exists usually will generate an error if it
doesn't, so you simply need to plan for the error. Here's one approach among
several. This one depends on "On Error Resume Next" being in effect *at
least* for the line "nm = ...", to avoid the error if Name doesn't exist.
You can turn any "regular" error handling back on *after* that, if desired.


***********************************
Sub SomeCode

On Error GoTo ErrHandler
Dim nm As String

......Other Code

On Error Resume Next
nm = ThisWorkbook.Names("TestRange").RefersTo
On Error GoTo ErrHandler

If Len(nm) = 0 Then
' Doesn't exist. Add it
ThisWorkbook.Names.Add Name:="TestRange", RefersToR1C1:"=Sheet1!R1C1"
Else
' Does exist. Delete it
ThisWorkbook.Names("TestRange").Delete
End If

.....More Code

ExitHere:
Exit Sub
ErrHandler:
MsgBox Err.Number & " " & Err.Description, "ERROR!"
Resume ExitHere
End Sub
******************************************************

HTH,