Delet Sheets with out Automation Error

[rogge]

here is my awesome code:

Option Private Module
Option Compare Binary
Option Explicit
Option Base 0

Public Const strDeleteSheet = "delSht"

Private Sub deleteSheets()

Dim xlSheet As Excel.Worksheet

Application.DisplayAlerts = False

For Each xlSheet In Application.ActiveWorkbook.Worksheets
If (xlSheet.CodeName Like strDeleteSheet & "*") Then
wkbActive.Worksheets(xlSheet.Name).Delete
End If
Next xlSheet

Application.DisplayAlerts = True

Set xlSheet = Nothing

End Sub

A not trappable error occurs: xlSheet.Name = <Automation error>. The code
does not stop processing. I know this is due to changing the items in
ActiveWorkbook.Worksheets.

This error resets globally declared variables to empty/false/etc...

Any suggestions to delete sheets without resetting global variables?

Thank you.
rogge

 

[Dave Peterson]

First, instead of:
wkbActive.Worksheets(xlSheet.Name).Delete

You may want to use:
Application.ActiveWorkbook.Worksheets(xlSheet.Name).Delete

Actually, I'd use:
xlSheet.Delete

In the code you posted, there's nothing that declares or sets wkbActive.

 

[rogge]

wkbActive is declared elsewhere...
I do like xlSheet.Delete...

but i had one of our excel experts look at the code, etc.. the public
variables are being reset by some other code... and the "watch" is not
finding when the value changes.

 

[Dave Peterson]

What kind of code are you using that resets that variable?

Maybe it's better to fix that.

======
But for this situation, I would be surprised (but it's possible), that you
really meant the activeworkbook:

It would be pretty weird (but not unheard of) to loop through one workbook to
delete stuff in another.

 

[rogge]

All of the code is affecting items in the activeworkbook. I don't use code
in this workbook to change objects in another workbook.

Only callbacks (procedures executed from the ribbon) set this boolean
variable.

I am going to ask this resetting question in a separate thread because i
have found that ending the code execution resets the boolean.... please
search for "rogge"

thanks for your help.

 

[Dave Peterson]

I don't use the End command (not End sub and not end if ...) in code. It ends
the running macro, but doesn't do it gracefully (as you've seen).