Runtime Error 13 with Worksheet.Delete

[AlForester]

Why does the following code produce an error 13 (Excel 2007) after the sheet
is deleted and when the end function statement is executed?

Public Function SheetDelete(varSheet As Variant) As Boolean

' var sheet is the sheet object to delete, not the index or name

SheetDelete = varSheet.Delete

End Function

The sheet is deleted but the runtime error occurs. The function returns the
correct value of the delete.

Any thoughts would help.

 

[Billy]

Why does the following code produce an error 13 (Excel 2007) after the sheet
is deleted and when the end function statement is executed?

Public Function SheetDelete(varSheet As Variant) As Boolean

    ' var sheet is the sheet object to delete, not the index or name

    SheetDelete = varSheet.Delete

End Function

The sheet is deleted but the runtime error occurs. The function returns the
correct value of the delete.

Any thoughts would help.

I have tested your code and did not receive any errors, my thinking
would be that the type mismatch error is coming from the function call
hence why its getting to the bottom of the function before erroring,
how are you calling the function? The other thing to note is the
function is declared to return a boolean but as it just deletes the
sheet then the function will return nothing.

James

 

[Dave Peterson]

Just a guess...

Maybe excel is recalculating and there's a calc event firing.

I'd try:

application.enableevents = false
on error resume next 'in case it can't be deleted
varsheet.delete
if err.number <> 0 then
err.clear
sheetdelete = false
else
sheetdelete = true
end if
on error goto 0
application.enableevents = true

======
In fact, I'd try this variation (without the .enableevents lines)--just to see
if that worked.

 

[Patrick Molloy]

varSheet.Delete
does that returna true/false? this is a boolean function after all