Is Nothing


J

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
 
B

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)
 
Ad

Advertisements

J

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
 
G

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

Advertisements

J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nothing? 2
Not Nothing 5
Date to nothing 3
if range=nothing then 2
If Not c Is Nothing Then 3
If nothing found ... 3
replacing , with nothing ? 2
Range is nothing 6

Top