Results 1 to 6 of 6
  1. #1
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41

    Is procedure name property available in VBA?

    I often use the form Name property in Debug.Print statements to associate a line in the Immediate window with the originating form.

    Is the name of the procedure where the code is executing also available programatically so that it could also be included in a Debug.Print command?

    For instance: Debug.Print Me.Name & " " & Me.ProcedureName in the code (or whatever the right term is for the procedure name property) might yield frmTest Form_Load in the Immediate window.

    Can that be done?



    Steve

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    Thanks for that link. I'm pretty low on the VBA learning curve and most of that link's discussion is way over my head to grasp, much less implement. My take away is that the name of the executing VBA sub/function is not available to the programmer. Appreciate your help.

    Steve

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Not true. You definitely can get the procedure name for use in error handling

    I use the following:

    Code:
    Exit_Handler:    
       Exit Sub
        
    Err_Handler:
       Dim strProc As String
       strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
       MsgBox "Error " & Err.Number & " in " & strProc & " procedure: " & Err.Description
       Resume Exit_Handler
    This code requires the VBA reference library Microsoft Visual Basic for Applications Extensibility 5.3
    NOTE:
    This doesn't always work reliably in the Form_Open event - otherwise its worked fine for me for several years

    I originally found that code on the late Chip Pearson's excellent website: http://www.cpearson.com/Excel/VBE.aspx

    EDIT: I see that code was the final suggestion in the link already provided
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Tried that in a button click event, not in error handler code, just Debug.Print line. strProc is empty. With or without the library, code runs but no output. So guessing only useful for error handling because error sets focus to the VBE. So why type all that code instead of simply "Command49_Click":

    MsgBox "Error " & Err.Number & " In " & Me.Name & " Command49_Click procedure: " & Err.Description
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    As I said previously, I often use this in error handling code which is what the OP wanted.
    The advantage is you can reuse the same code in every procedure rather than type out the individual name of each procedure
    I use it as part of my error logging routine whereby details of any program errors are automatically (and silently) emailed to me without the client needing to do anything at all.

    As error handling, the code works without the library but you won't get intellisense.
    Other parts of my error handling code require the VBE library to be in use

    It doesn't work reliably in event code where it may instead pick up the name of the first procedure in a code module ...or nothing at all
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 33
    Last Post: 09-25-2015, 08:39 AM
  2. How to use a sub procedure
    By trevor40 in forum Programming
    Replies: 4
    Last Post: 01-13-2015, 01:06 PM
  3. log in procedure
    By pedjvak in forum Programming
    Replies: 1
    Last Post: 04-16-2013, 11:24 AM
  4. Pass a form property to a procedure
    By trb5016 in forum Programming
    Replies: 2
    Last Post: 02-17-2010, 04:15 PM
  5. How to run VBA procedure
    By bkelly in forum Programming
    Replies: 8
    Last Post: 09-26-2009, 06:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums