Is Nothing

[John]

Hi there,

I'm always getting into trouble with the Is Nothing statement. At the
moment, I want to delete a specific sheet if it exists prior to adding
another of the same name, so:

If Not wkb.Sheets("TempSheet") Is Nothing Then
wkb.Sheets("TempSheet").Delete
End If

I'm getting a subscript out of range error on the first line (above) and a
err code of 9 (PS can anyone point me to a list of error codes?)

Can anyone tell me what I'm doing wrong?

Thanks

John

 

[Bob Phillips]

Not quite like that, but right principle

Dim sh As Worksheet

On Error Resume Next
Set sh = wkb.Sheets("TempSheet")
On Error Goto 0
If Not sh Is Nothing Then
wkb.Sheets("TempSheet").Delete
End If

This sets a worksheet variable to that worksheet, and tests if that object
variable is nothing. The Onerror Resume Next is just in case it does not
exist, then it resets.

You could always jus do

On Error Resume Next
wkb.Sheets("TempSheet").Delete
On Error Goto 0

on the basis that if you are going to delete it, no need to test.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[John]

Thanks very much Bob, it works perfectly.

One question, is will the "On Error Goto 0" reset ALL error handling or just
that one? I assume saying this that I could add another "On Error GoTo
errHandler" afterwards to reset (I want to catch a 457 error later on for
adding dupes to a collection)? Something like:


On Error GoTo errHandler
........
On Error Resume Next
wkb.Sheets("TempSheet").Delete
On Error Goto 0
On Error GoTo errHandler


Have I got this right?

Thanks again

John

 

[Guest]

BTW: the reason for your error: Is Nothing is not the same as "does not
exist". Is Nothing means you have an object variable allocated but it does
not yet contain a specific object. So it only refers to a variable, not a
specific thing. When your code says If Not wkb.Sheets("TempSheet"), VBA
looks for "TempSheet" but there is no such thing and so it gives you an error.

And as for your other question: there is only ever one error handler in
effect for a given procedure, so On Error Goto 0 necessarily resets all error
handling (but if your procedure was called from another procedure, the
calling procedure's error handling is still in effect). And yes, you can
then set up another error handler further on in the code.

 

[John]

Hi K,

Thanks for the explaination. I'm a lot clearer now. It's a shame that
there isn't a "If xExists" type method for all collections as standard. I
think Visio has a CellExists for a shape's shapesheet, which saves all the
error handling or looping to check. Anyway, perhaps I'll put that in a wish
list somewhere.

Thanks also for the "error" response. I checked Help, but the scope of
"...Goto 0" wasn't clear, so that helps.

Best regards

John