none
Disable "Debug" when macro triggers an error RRS feed

  • Question

  • Would like to disable the Debug button when a certain macro triggers an error, so that people cannot see the code behind when they click Debug.

    Is this possible?

    Thanks.

    Sunday, July 24, 2016 10:00 PM

Answers

  • There are plenty of error routines out there but the following is a very simple routine and then below is a more extensive routine that I use.

    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error GoTo errorRoutine
       
        Dim ws As Worksheet
       
        Set ws = Worksheets("Anything")    'For testing Worksheet "Anything" does not exist so errors
       
        Exit Sub    'If no errors then exits at this point

    errorRoutine:      'Label. Note the colon suffix
        MsgBox "An error occurred in module Sheet1, Private Sub Worksheet_Change" & _
        vbCrLf & "Error #: " & Chr(9) & Chr(9) & Err.Number & _
        vbCrLf & "Error description: " & Chr(9) & Err.Description   'Chr(9) is a tab.

    End Sub

    The following is a more extensive routine that writes the error report information to a text file. If the text file does not exist then it is automatically created. You can open the text file with Notepad and view the errors that have occurred. I use this method because Users sometimes do not report errors and it allows me to monitor. All of the errors reports can be deleted in the text file after viewing or simply delete the file altogether and the system will create a new one next time the routine is called.

    The main Sub Error_Routine(strModule As String, strSubName As String, lngErrNumb As Long, strErrDescript As String) goes in a standard module.

    The calling sub can be any sub in the project.

    You will need to edit every sub in the project and add the "On Error GoTo errorRoutine" at the start of the sub and then add the label at the bottom of the sub and edit the parameters with the module name and sub name.

    If you use anything like On Error Resume Next to test situations in your code then you need to repeat the On Error GoTo errorRoutine after the test routine.

    The first sub below is simply a test sub with a built in error so that it will call the error routine and append to the text file. The text file can be viewed with Notepad.

    Sub TestErrorCall()
        'This sub used by Developer to call Error_Routine for testing

        On Error GoTo errorRoutine
        Dim ws As Worksheet
        Set ws = Worksheets("Sheet4")       '"Sheet4" non existent so errors
       
        Exit Sub        'If gets to here without errors then exits the sub

    errorRoutine:           'Label. Note the colon suffix.
        'In following line "Module1" and "Sub TestErrorCall" need to be editied in every sub
        Call Error_Routine("Module1", "Sub TestErrorCall", Err.Number, Err.Description)
       
        'Ends the processing entirely. See the following link re the End statement
        'http://analystcave.com/vba-end-exit-reset-vba-macro/
        End
    End Sub


    Sub Error_Routine(strModule As String, strSubName As String, lngErrNumb As Long, strErrDescript As String)
        'Called sub when an error occurs and writes error info to Text File.
        Dim strPath As String
        Dim strTextFile As String
        Dim strTextPathFile As String
        Dim strNow As String
       
        strNow = Format(Now(), "dd mmm yyyy hh:mm AMPM")    'Edit Date format to preferred format
       
        strPath = ThisWorkbook.Path         'Can edit and insert an actual path if preferred
        strTextFile = "XL Error Log.txt"    'Edit to preferred text file name
       
        strTextPathFile = strPath & "\" & strTextFile
       
        Open strTextPathFile For Append As #1
        Print #1, "User name:" & Chr(9) & Chr(9) & Environ("UserName")  'Chr(9) are tabs
        Print #1, "Date and Time:" & Chr(9); Chr(9) & strNow
        Print #1, "Module:" & Chr(9) & Chr(9) & Chr(9) & strModule
        Print #1, "Procedure:" & Chr(9) & Chr(9) & strSubName
        Print #1, "Error Number:" & Chr(9) & Chr(9) & lngErrNumb
        Print #1, "Error Description:" & Chr(9) & strErrDescript
        Print #1, " "
        Print #1, "End of error report"
        'Note: Each Error report is separated with an asterisk line in the Text file.
        Print #1, "***********************************************************"
        Print #1, " "       'Inserts additional line between error reports
        Close #1
       
        'Edit OssieMac next line to required name to contact re the error
        MsgBox "An error has occurred in the VBA Program." & Chr(13) & _
                "Please advise the Administrator, OssieMac." & Chr(13) & _
                "The error details have been saved in a text file for reference."

    End Sub


    Regards, OssieMac

    Wednesday, July 27, 2016 2:26 AM

All replies

  • You can use an On Error routine and send the code to a routine that displays a message Box and then ends the code. If you google "Excel on error routines" you will find ample information on how to handle errors so they do not display the debug popup.

    Alternatively you can lock the VBA project with a password and then the code cannot be viewed. VBA editor is simply blank behind the popup and Debug button is disabled so the user must select End.

    1. Select any one of the items in the VBA Project Explorer (Left column)
    2. Right click the item and Select "VBA Project Properties"
    3. Select the "Protection" tab
    4. Check the box against "Lock for viewing"
    5. Enter the password (twice)
    6. The project is not locked until after the workbook is saved, closed and re-opened.
    7. To unlock the project just select a visible item in the Project explorer and enter the password. The project remains unlocked until it is saved, closed and re-opened.
    8. To remove the password is the similar to locking but clear checkbox against "Lock for viewing" and then clear the password fields and then save the workbook.

    Regards, OssieMac

    Monday, July 25, 2016 2:47 AM
  • Thank you for the reply.

    Have googled "send the code to a routine that displays a message Box and then ends the code" and although it returns a *lot* of answers, cannot seem to find anything that addresses this specifically.

    A code assist would be much appreciated.

    Thanks again.

    Monday, July 25, 2016 6:21 PM
  • There are plenty of error routines out there but the following is a very simple routine and then below is a more extensive routine that I use.

    Private Sub Worksheet_Change(ByVal Target As Range)
        On Error GoTo errorRoutine
       
        Dim ws As Worksheet
       
        Set ws = Worksheets("Anything")    'For testing Worksheet "Anything" does not exist so errors
       
        Exit Sub    'If no errors then exits at this point

    errorRoutine:      'Label. Note the colon suffix
        MsgBox "An error occurred in module Sheet1, Private Sub Worksheet_Change" & _
        vbCrLf & "Error #: " & Chr(9) & Chr(9) & Err.Number & _
        vbCrLf & "Error description: " & Chr(9) & Err.Description   'Chr(9) is a tab.

    End Sub

    The following is a more extensive routine that writes the error report information to a text file. If the text file does not exist then it is automatically created. You can open the text file with Notepad and view the errors that have occurred. I use this method because Users sometimes do not report errors and it allows me to monitor. All of the errors reports can be deleted in the text file after viewing or simply delete the file altogether and the system will create a new one next time the routine is called.

    The main Sub Error_Routine(strModule As String, strSubName As String, lngErrNumb As Long, strErrDescript As String) goes in a standard module.

    The calling sub can be any sub in the project.

    You will need to edit every sub in the project and add the "On Error GoTo errorRoutine" at the start of the sub and then add the label at the bottom of the sub and edit the parameters with the module name and sub name.

    If you use anything like On Error Resume Next to test situations in your code then you need to repeat the On Error GoTo errorRoutine after the test routine.

    The first sub below is simply a test sub with a built in error so that it will call the error routine and append to the text file. The text file can be viewed with Notepad.

    Sub TestErrorCall()
        'This sub used by Developer to call Error_Routine for testing

        On Error GoTo errorRoutine
        Dim ws As Worksheet
        Set ws = Worksheets("Sheet4")       '"Sheet4" non existent so errors
       
        Exit Sub        'If gets to here without errors then exits the sub

    errorRoutine:           'Label. Note the colon suffix.
        'In following line "Module1" and "Sub TestErrorCall" need to be editied in every sub
        Call Error_Routine("Module1", "Sub TestErrorCall", Err.Number, Err.Description)
       
        'Ends the processing entirely. See the following link re the End statement
        'http://analystcave.com/vba-end-exit-reset-vba-macro/
        End
    End Sub


    Sub Error_Routine(strModule As String, strSubName As String, lngErrNumb As Long, strErrDescript As String)
        'Called sub when an error occurs and writes error info to Text File.
        Dim strPath As String
        Dim strTextFile As String
        Dim strTextPathFile As String
        Dim strNow As String
       
        strNow = Format(Now(), "dd mmm yyyy hh:mm AMPM")    'Edit Date format to preferred format
       
        strPath = ThisWorkbook.Path         'Can edit and insert an actual path if preferred
        strTextFile = "XL Error Log.txt"    'Edit to preferred text file name
       
        strTextPathFile = strPath & "\" & strTextFile
       
        Open strTextPathFile For Append As #1
        Print #1, "User name:" & Chr(9) & Chr(9) & Environ("UserName")  'Chr(9) are tabs
        Print #1, "Date and Time:" & Chr(9); Chr(9) & strNow
        Print #1, "Module:" & Chr(9) & Chr(9) & Chr(9) & strModule
        Print #1, "Procedure:" & Chr(9) & Chr(9) & strSubName
        Print #1, "Error Number:" & Chr(9) & Chr(9) & lngErrNumb
        Print #1, "Error Description:" & Chr(9) & strErrDescript
        Print #1, " "
        Print #1, "End of error report"
        'Note: Each Error report is separated with an asterisk line in the Text file.
        Print #1, "***********************************************************"
        Print #1, " "       'Inserts additional line between error reports
        Close #1
       
        'Edit OssieMac next line to required name to contact re the error
        MsgBox "An error has occurred in the VBA Program." & Chr(13) & _
                "Please advise the Administrator, OssieMac." & Chr(13) & _
                "The error details have been saved in a text file for reference."

    End Sub


    Regards, OssieMac

    Wednesday, July 27, 2016 2:26 AM
  • Thank you.

    Presume the first sub goes into the specific worksheet.   ???

    Wednesday, July 27, 2016 2:28 AM
  • Thank you.

    Presume the first sub goes into the specific worksheet.   ???


    Yes. It is a worksheet change event sub.

    Regards, OssieMac

    Wednesday, July 27, 2016 3:28 AM