Need a VBA error handling button to stop the code

Question

I'm creating a template that will contain macros intended for people who don't know anything about VBA and once I'm gone, there will be no one around to debug issues for them, so I'm trying to build in some error handling to help them out if they ever encounter an issue.

In this piece of code, I'm creating a sheet called ConfirmedPivot. At first, I included a simple error handling GoTo 0 which says "This name already exists" but I'm afraid this instruction is not clear enough for users as to how to fix the error.

So I created ConfPivError message which gives better instructions on what to do but I can't figure out what is the best button to go with this message. I want a button that will stop the code so they can delete the duplicate name and start over (equivalent of End button). I tried few buttons but none of them seem to be able to stop the code. All of the OK, Abort and Cancel buttons don't stop the code (so the code tries to run again and the error comes up again).

Do you have any ideas of which button I could use? Or alternatively, could I add something to end the code if this error comes up once the user presses OK?

' Create ConfirmedPivot

Dim wsTest As Worksheet
Const strSheetName As String = "ConfirmedPivot"

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo ConfPivError  ' custom error message

If wsTest Is Nothing Then
    Worksheets.Add.Name = "ConfirmedPivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Recovered_Sheet1!R1C1:R65536C114", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="'ConfirmedPivot'!R1C1", TableName:="PivotTable4" _
    , DefaultVersion:=xlPivotTableVersion10
End If

' end Create ConfirmedPivot

' Error handling experiment

ConfPivError:
    Answer = MsgBox("ConfirmedPivot Sheet already exists in this workbook. If you don't need it, delete it then try the button again" _
    & "(New ConfirmedPivot will be created). If you still need it, rename it and try the button again.", vbOKOnly, "ConfirmedPivot already exists!")

' end experiment

All you have to do is add `Exit Sub` after your `ConfPivError` handler and it will exit the subroutine — mozgov_net, Oct 17 '16 at 15:41

score 2 · Accepted Answer · answered Oct 17 '16 at 16:01

2

It's a little odd to use On Error Resume Next and handle errors that way.

Maybe instead do it like this:

Const strSheetName As String = "ConfirmedPivot"

Dim wsTest As Worksheet

On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0  'clear EORN

If Not wsTest Is Nothing then
    MsgBox "ConfirmedPivot Sheet already exists in this workbook." & _
      " If you don't need it, delete it then try the button again" & _
      "(New ConfirmedPivot will be created). If you still need it, " & _
      " rename it and try the button again.", _
       vbOKOnly, "ConfirmedPivot already exists!"
    Exit Sub
Else
    Worksheets.Add.Name = "ConfirmedPivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
              SourceData:= "Recovered_Sheet1!R1C1:R65536C114", _
               Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="'ConfirmedPivot'!R1C1", _
        TableName:="PivotTable4", DefaultVersion:=xlPivotTableVersion10
End If

answered Oct 17 '16 at 16:01

Tim Williams

154,628
8
97
125

Hi Tim. I'd like to understand what each line does. On Error Resume Next means to ignore the error, right? In your suggestion, there's no code above that line other than declarations, what error would we be ignoring? – Kamiccola Oct 17 '16 at 18:27
I like the If statement idea, btw. I need to test it out first. – Kamiccola Oct 17 '16 at 18:27
It officially works :) Thank you. You also gave me an idea how to handle other possible errors. I could test for all possible errors in the beginning and have the user clean up the workbook before any actual code happens instead of dealing with each error separately. This is great stuff. Thank you. – Kamiccola Oct 17 '16 at 18:40
"there's no code above that line other than declarations" - any `On Error` statement should be *before* the line(s) which might raise an error – Tim Williams Oct 17 '16 at 18:47
@Kamiccola, I guess it'd be better if you use error handling checking for specifics error numbers in the end. But it's up to you to decide. – Marco Oct 17 '16 at 18:58

score 0 · Answer 2 · edited May 23 '17 at 12:00

Another approach for your problem is using a function @Tim Williams posted in another question:

Test or check if sheet exists

Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

     If wb Is Nothing Then Set wb = ActiveWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
End Function

Sub OKButton()
    ' Create ConfirmedPivot
    On Error GoTo ConfPivError  ' custom error message

    Dim wsTest As Worksheet
    Const strSheetName As String = "ConfirmedPivot"

    If SheetExists(strSheetName) Then
        MsgBox "ConfirmedPivot Sheet already exists in this workbook. If you don't need it, delete it then try the button again" _
        & "(New ConfirmedPivot will be created). If you still need it, rename it and try the button again.", vbOKOnly, "ConfirmedPivot already exists!"
    Else
        Worksheets.Add.Name = "ConfirmedPivot"
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Recovered_Sheet1!R1C1:R65536C114", Version:=xlPivotTableVersion10). _
            CreatePivotTable TableDestination:="'ConfirmedPivot'!R1C1", TableName:="PivotTable4" _
        , DefaultVersion:=xlPivotTableVersion10

        ' end Create ConfirmedPivot
    End If

    ' Error handling experiment
    ConfPivError:
        If Err.Number <> 0 Then
            MsgBox Err.Description, vbOKOnly, Err.Number
        End If

    ' end experiment
End Sub

Obs.1: The error handling is only dealing with unexpected errors.

Obs.2: I removed your Answer variable because you weren't using it.

Thank you for the suggestion. I haven't tried any functions yet. I will keep your notes for future once I start playing around with them. — Kamiccola, Oct 17 '16 at 18:41

Need a VBA error handling button to stop the code

2 Answers2