Execution Stops After Adding Worksheet

Question

I am using VBA code in a custom ribbon for Excel 2007 to insert a "helper" worksheet which is to be deleted after execution concludes.

However, under certain circumstances, execution stops after the "Worksheet.Add" function is called with no error, no debug text, and no pop-up message. I have added my own debug code after every line of code and nothing executes after the add function is called.

Code is below:

Private Function Difference(r1 As Range, r2 As Range) As Range
'Purpose: Returns a range containing only the cells which are not shared between the two passed ranges
Application.EnableEvents = False
On Error Resume Next
    Dim s As String
    Dim ws As Worksheet
    Dim diff As Range, zRng As Range, cRng As Range

    If Not r2 Is Nothing Then
    On Error GoTo Sheet_Cleanup
        If Not (r1.Parent Is r2.Parent) Then GoTo Exit_Code

        Set ws = Worksheets.Add
        For Each a In r1.Areas
            Set zRng = chkUnion(zRng, ws.Range(a.Address))
        Next a
        zRng = 0
        For Each b In r2.Areas
            Set cRng = chkUnion(cRng, ws.Range(b.Address))
        Next b
        cRng.Clear

        For Each c In ws.UsedRange.SpecialCells(xlCellTypeConstants).Areas
            Set diff = chkUnion(diff, r1.Parent.Range(c.Address))
        Next c
Sheet_Cleanup:
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    On Error Resume Next
    Else
        Set diff = r1
    End If
    If Not diff Is Nothing Then Set Difference = diff
Exit_Code:
Application.EnableEvents = True
End Function

This is not a desirable error state, as it leaves the worksheet in place, which must be deleted by the user.

What is more strange is that running the code again immediately after produces no such error event.

I have managed to reproduce the error on a specific worksheet, but it only occurs on the first instance of clicking the ribbon button and will not reoccur until the Excel session is closed and both the worksheet and ribbon add-in re-opened.

Whenever VBA code doesn't work as intended, the first thing to do is to remove `On Error Resume Next` and see what error you're shoving under the carpet. Don't use `On Error Resume Next` like this. Ever. — Mathieu Guindon, Jan 31 '18 at 22:17
Hmm, just noticed the other `On Error` statement. What happens if you remove it as well? The `On Error Resume Next` statement in your `Sheet_Cleanup` error-handling subroutine is wrong. Actually the fact that you have an error-handling subroutine executing in the "happy path" feels wrong too. You need to un-tangle the "happy path" and the "error path" and make sure the "error path" only ever runs when there's an error - and `Resume` to the cleanup label. — Mathieu Guindon, Jan 31 '18 at 22:19
The use of 'Resume Next' in this case is so that no user ever sees an error box. I removed the error code in my testing, and likely should not have included it in the snippet above. Removing it does nothing to change the outcome. — Glamador, Feb 01 '18 at 13:42
But it rules out any error that would otherwise have been hidden. — Mathieu Guindon, Feb 01 '18 at 14:45
I understand. I did not do my testing with the error code in place. Printing err.description gives no additional detail at the time of failure. Execution just stops with no explanation. — Glamador, Feb 01 '18 at 18:40
What's your setting for tools/options/general/error traping? Is it "Break on All Errors"? — Mathieu Guindon, Feb 01 '18 at 18:45
I have created the error conditions in both "All Errors" and "Unhandled Errors" settings. Same result, no error messages. I found an old question with a similar issue, but no viable solution here:https://stackoverflow.com/questions/37858167/error-adding-a-new-sheet-to-a-xlsm-macro-excel-file. I do not believe it is an issue with installation, since the error occurs on multiple users' machines, all running Excel 2007. — Glamador, Feb 01 '18 at 22:08

score 0 · Answer 1 · answered Feb 01 '18 at 00:44

I can see some logic flaws in the code. You should remove "On error resume next" first of all. I reorganized the code as follows. You will get a message box with the error.

Private Function Difference(r1 As Range, r2 As Range) As Range
'Purpose: Returns a range containing only the cells which are not shared 
between the two passed ranges
On Error got sub_error

Application.EnableEvents = False   
Dim s As String
Dim ws As Worksheet
Dim diff As Range, zRng As Range, cRng As Range

If Not r2 Is Nothing Then
    If Not (r1.Parent Is r2.Parent) Then GoTo sub_exit

    Set ws = Worksheets.Add
    For Each a In r1.Areas
        Set zRng = chkUnion(zRng, ws.Range(a.Address))
    Next a
    zRng = 0
    For Each b In r2.Areas
        Set cRng = chkUnion(cRng, ws.Range(b.Address))
    Next b
    cRng.Clear

    For Each c In ws.UsedRange.SpecialCells(xlCellTypeConstants).Areas
        Set diff = chkUnion(diff, r1.Parent.Range(c.Address))
    Next c   
Else
    Set diff = r1
End If
If Not diff Is Nothing Then Set Difference = diff

sub_exit:
Application.DisplayAlerts = False
if not ws is nothing then ws.Delete
Application.DisplayAlerts = True
exit function

sub_error:
msgbox (err.description)
resume sub_exit
End Function

I agree with your changes, there was no cause to encapsulate the cleanup in the successful fork of the If-statement, I just put it there because in the other case there is no worksheet to be deleted. In any case, that still produces no pop-up and no error. I put debug code all over the subroutine, and it never even runs the code in sub_error. Execution just...ceases. — Glamador, Feb 01 '18 at 13:44

Execution Stops After Adding Worksheet

1 Answers1