Getting a SUB's name

  • Is there a way (other than doing it manualy) to get the sub name and the form name that the error occured on.
    I mean something like this -I know the synatax is not right of what i am writing-

    on error goto err_handler
    sub_name =
    form_name =
    msgbox(sub_name & form_name)

    Is there something as simple as that that I did not run across yet??

    I figured out how to get the form name.

    1. f_name = Me.Name

    but still no success with getting the sub name

  • Re: Getting a SUB's name

    yes you are, but as you know, errors will happen for a veriety of reasons including but not limited to:
    - the user changed certain data on the sheet that is used by the form (division by zero error)
    - a cell in the sheet was changed by the user which will cause the find line in the code to cause an error
    - a file has been deleted by the user, which will cause an error in open file

    and much much more.....

    I thought(and read in many vba websites and books): one of the good VBA writing practices is handeling errors, next is being able to see if the error is you programming or the user did something, and how can you update the program to be able to avoid this error if possible, and this can only be done, when you know where the error happened.

  • Re: Getting a SUB's name (still no answer)

    Since you are going to put in a " On error goto errhandler / errhandler:" routine why not just have the msgbox name the process it's in? ie:

    Sub dosomething ()

    On error goto errhandlr

    ...some code...


    msgbox " Hi! I'm Sub "dosomething" and I scrwd up on form " & fname & "!"

    End Sub



  • Re: Getting a SUB's name (still no answer)

    Hi Laythss,

    As far as I know there is no way of automatically detecting the name of the routine that has generated an error.

    What I tend to do, where there is a need for some custom error-handling, is to have a generic function that handles the error-logging. This function has as one of its arguments the name of the routine that has given the error. The error-handler in the routine passes its name (hard-coded I'm afraid) to the function.

    Here is an example of what I use in my add-in:
    [vba]Sub TestErrorHandler()
    'not used in add-in : just for testing error-handler
    On Error GoTo ErrorHandler
    Kill "Anon-existantfilename"

    Exit Sub
    Select Case ProcessError("modErrorHandler : TestErrorHandler", Err.Number)
    Case Is = True: Resume
    'try again
    Case Is = False: Resume ExitRoutine
    'give up
    End Select

    End Sub

    Function ProcessError(strProc As String, iErrNo As Integer) As Boolean
    'use info from calling procedure to produce an error log and user message

    Dim strMessage As String
    Dim intStyle As Integer
    Dim iFile As Integer

    Const strTitle As String = "Warning : Error"
    Const strMsg1 As String = "The following error has occurred:"
    Const strMsg2 As String = "Do you wish to retry?"

    strMessage = vbNewLine & "Error number : " & iErrNo & _
    vbNewLine & "Error description : " & Error(Err.Number) & _
    vbNewLine & "Procedure : " & strProc & vbNewLine
    'the basic error info

    iFile = FreeFile()
    Open ThisWorkbook.Path & "\RPS Add-in_Error.Log" For Append As #iFile
    Print #iFile, Now, ThisWorkbook.Name, strMessage
    Close #iFile
    'add the error message to the error log

    strMessage = strMsg1 & vbNewLine & strMessage & vbNewLine & strMsg2
    intStyle = vbExclamation + vbRetryCancel
    'the full error message

    ProcessError = _
    (MsgBox(prompt:=strMessage, Buttons:=intStyle, Title:=strTitle)) = vbRetry
    'display warning to user and get response (Retry or Cancel)

    End Function[/vba]HTH

    Edit : By the way, you can get the name of a form because it is an object, like a worksheet. A sub-routine is not an object so that approach won't work.

  • Re: Getting a SUB's name

    I use something very similair to Richie's error handlign routine. With a bit of difference. I have the Error handling function in it's own code module: the two arguments being [ sModule as string, sProc as string ]

    At the top of every code module in the workbook, minus the Error Handler, I use Constants to list the Module Name, then Constants for all the procedure names. Yes it is hard coded, and if you add a procedure to the module, you need to add a constant for the procedure name, but for me it works and keeps a listing at the top of the module of all the procedures in it. a little reference kind of.

    1. Const Mod1 as String = "Module 1"
    2. Const Proc1 as String = "TestSub"

    Within each procedure (TestSub) is the Error Handler, which calls the function:

    1. ErrorHandler:
    2. if CentralErrorHandler(Mod1, Proc1) then 'this calls the central err handler
    3. resume next 'Or whatever else
    4. end if

    the CentralErrorHandler function then uses the 2 arguements to to grab the module name, plus the subs name, and writes it to the log, along with whatever else your central error handler would need to do.

    I agree with Norie that you should write code that produces no errors, but it is also best practice to have an error handling routine and log, because you just never know.

    I don;t have my full example of a central Error handler here (it's at work) but if you would like to see it, I can post it later if your interested.

  • Re: Getting a SUB's name

    Quote from norie

    ... So it's bad advice to suggest that code should be written to avoid/deal with any errors you might foresee?

    OK, let's be frank here (no, not Frank!, frank ;) ) nobody deliberately includes errors in their code. And yes, where we can foresee that there may be errors we should, of course, deal with them.

    However, it is the unforeseen errors that are often the problem and this sort of custom error-handling is typically reserved for add-ins that are to be used by people less experienced in VBA than we are. The sort of error messages that the VBE produces are pretty meaningless to the uninitiated and they certainly wouldn't know how to go about debugging the error-producing code. Therefore, it makes sense to handle the errors for them (ideally logging them for later reference).

    Justin - by all means if you have some code that illustrates a slightly different approach then please post it. Variety is the spice of VBA (or something like that) :)

  • Re: Getting a SUB's name

    Sorry for the delay, here is what I use in most applications I build at work. This is a modified piece from Rob Bovey and Stephan Bullen's book

    I used something similiar, but their book helped clean it up significantly: This one doesnt use the iFreeFile method, havent got around to converting it yet.

    In a Standard Module:

    in any other Modules, add constants at the top, or within each procedure, these constants are used only for what gets logged.

    1. Private Const msModule As String = "mdlMainCalls" 'Module name
    2. Private Const sSource As String = "Clearing for New Week" '3 subs in the module, or description of the sub
    3. Private Const sBackup As String = "Backup Copy"
    4. Private Const sPasteScores As String = "VLookup-Copy"

    Call it within each sub, specifiying the constants as the functions arguments:

    1. ErrHandler:
    2. If bCentralErrorHandler(msModule, sBackup) Then
    3. End If
    4. Resume Next

    Modify if you want ErrHandler to deliver a message to the user, just continue with the code, etc....
    Primarily I only deliver a message if the procedure cannot continue. Its part of my everyday job to chekc error logs everyday so this method allows me to see what heppened, with who, what time, and where it came from. This allows the user to continue working, but I can fix issues without any interupting the user too much. And if it is a consitent offending user, get that user some more training, etc...

    The book link I posted above is highly recommended, I have only been learning VBA since July or 04, but it really put things into perspective about programming practices. Sorry to post back so late with the code, hope it sparks some ideas,