none
Excel VBA to include 2 error handling RRS feed

  • Question

  • Hi All,

    I have some code which goes to folder and open excel file and do some stuff. I want to add 2 error handling

    1. If user do not have permission to that folder than show msgbox "No Permission"
    2. If user have permission and  after that if there is any error in called procedure then msgbox "Error in the procedure name"

    Below is the code to check the permission

    Option Explicit
    Public ChDirFolder As String
    Sub InitializePath()
    ChDirFolder = "path to folder"
    On Error GoTo ErrHandler:
        ChDir ChDirFolder
    End Sub

    Public ChDirFolder is a global variable and i am calling Sub InitializePath procedure in another procedure  "Sub Refresh()" . So if user have permissions but if anything goes wrong in Sub Refresh procedure then msgbox shall popup "Error in refreshing data"

    Thanks,

    Zaveri



    • Edited by zaveri cc Monday, August 25, 2014 9:05 PM
    Monday, August 25, 2014 9:01 PM

Answers

  • Hello,

    You could use one sub with one error handler and test on the err.number.

    or you could make 2 on error steps:


    Sub InitializePath()
    ChDirFolder = "path to folder"
    On Error GoTo ErrHandler:
       
    ChDir ChDirFolder

    On Error GoTo ErrHandler2: 'rest of your code

    exit sub

    ErrHandler:

    'some error handling

    exit sub

    ErrHandler2:

    other error handling
    End Sub

    Or you could write 2 sub's with both their error handler.

    I would do the last thing: keep your sub's small and reusable.

    Best regards

    • Marked as answer by zaveri cc Tuesday, August 26, 2014 1:52 PM
    Tuesday, August 26, 2014 6:51 AM

All replies

  • Hello,

    You could use one sub with one error handler and test on the err.number.

    or you could make 2 on error steps:


    Sub InitializePath()
    ChDirFolder = "path to folder"
    On Error GoTo ErrHandler:
       
    ChDir ChDirFolder

    On Error GoTo ErrHandler2: 'rest of your code

    exit sub

    ErrHandler:

    'some error handling

    exit sub

    ErrHandler2:

    other error handling
    End Sub

    Or you could write 2 sub's with both their error handler.

    I would do the last thing: keep your sub's small and reusable.

    Best regards

    • Marked as answer by zaveri cc Tuesday, August 26, 2014 1:52 PM
    Tuesday, August 26, 2014 6:51 AM
  • Thanks.

    Tuesday, August 26, 2014 1:52 PM