Automatic Insertion of Procedure Names in VBA and VB6 Code

Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.
Published:
Updated:
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't interested in and/or not all of the procedures that led up to arriving at the break point. Adding Debug.Print statements or MsgBox statements in procedures is another way but adding them manually can be very tedious. The add-in I've attached resolves those problems by automatically adding  statements in all selected procedures that contains "Debug.Print" or whatever else you want along with the name of the procedure. 
When the add-in is loaded this toolbar will be added to the Visual Basic IDE in VB6 or the VBE (Visual Basic Environment) in Excel.
Insert-Procedure-Names-Toolbar.jpgThe button on the right in the toolbar is the one that you use to set the prefix value and when it is clicked this window is displayed.
Choose-Code.jpgThe default for the prefix string is "Debug.Print" but you can choose "Const Proc_Name = " or set the prefix to anything else you want including "Msgbox" by choosing 'Other' and entering the value. Note that the 'Const PROC_NAME' prefix can be very valuable if you want to be able to include procedure names in your error routines.
And this shows the VB6 version of the window that appears if you click the first button in the toolbar. You use it to select the procedures to be modified. (The VBA version uses different icons which match those that are used in Excel.) In this case shown below all the procedures in frmMain will have the statements added to them but I could have chosen any or all procedures in any or all modules. Had the statements been previously added, the 'Remove' button would have been enabled and clicking it would remove the added statements from your program.
Insert-Procedures-VB6.jpgInstalling the Add-Ins
VB6
Download the InsertProcNameVB6.txt attachment and change the name to InsertProcNameVB6.dll. It doesn't matter where you store it. Once you have the file renamed you need to register the dll. To do that go to Start|Run and enter regsvr32 C:\InsertProcNameVB6.dll on the 'Open:' line and press OK. You should get a message saying that the registration was successful.
InsertProcNameVB6.txt
The final step is to go to the VB6 IDE and select the Add-Ins|Add-In Manager… menu item and select 'Insert Procedure Names VB6' from the list. Under 'Load Behavior' I suggest selecting both available options.

Excel
Download the InsertProcNameVBA.txt attachment and change the name to InsertProcNameVBA.dll. It doesn't matter where you store it. Once you have the file renamed you need to register the dll. To do that go to Start|Run and enter regsvr32 C:\InsertProcNameVBA.dll on the 'Open:' line and press OK. You should get a message saying that the registration was successful.
InsertProcNameVBA.txt
The final step is to go to the Excel VBE and select the Add-Ins|Add-In Manager… menu item and select 'Insert Procedure Names VBA' from the list. Under 'Load Behavior' I suggest selecting both available options.

Note that while this is a COM add-in you won't see it listed if you go to either File|Options|Add-Ins|COM Add-Ins (or Excel Add-ins).

If you are interested in learning more about debugging VBA or VB6 code then see my Using the VB6 Debugger tutorial.
 
Finally, if you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. Thanks!
3
7,359 Views
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.

Comments (3)

Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2023

Author

Commented:
Updated 9/3/2014
Version 3.0 - Many bug fixes and performance-improving changes including:
New toolbar and selection icons
Corrected a problem where the WARNING line could be added more than once to the same procedure
Corrected a problem where objects added after the 'Select Procedures' was shown for the first time weren't being included
Previous selections are now remembered each time 'Select Procedures' is shown
Added a second button to the addin toolbar as a replacement for the InputBox that used to be used, so that the user isn't repeatedly asked what marker he wants to use
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2023

Author

Commented:
Updated 9/20/2014
Version 3.0.1

Corrected bug where commented out procedures were being considered as active code.
Thanks for a very useful utility.  I ran the VBA version on my Access 2016 project (which has over 1200 methods) to insert PROC_NAME constants, and it worked mostly correctly.  However, I did find some issues, and I have a couple of suggestions to make.

The issues are:
  1. Clicking the toolbar's "A" icon to select the insert type you wanted did display the form, but if I moved my mouse over a combo-box (like the module's function selector combo-box), the form disappeared.  Therefore, I could not click the radio buttons or the OK button.  I was able to work around the issue by using the keyboard and Tab keys to do what I wanted, but that shouldn't be necessary.  (I didn't discover that the mouse had to move over a combo-box until I was writing this and tried to narrow the cause down; at first I thought it was almost any mouse movement.)
  2. Some functions (maybe 10-20) didn't get the constant added.  I discovered this when I did a global search & replace to change my error message handling function call to use PROC_NAME instead of the hard-coded names.
  3. The instructions could use some updating.  Running regsvr32 failed.  I solved that by opening a command prompt using Run as administrator, then running the regsvr32 call there.

Here's an example of one function that didn't get the PROC_NAME constant added:
' Make a form's Tag property for use by other forms
' strAction -- Operation type of tag to create
' strApp    -- Item "name" being operated on (usually an application); it can be a Tester's Name when used in Edit Profile
' strID     -- ID of some sort
' strOpts   -- Additional form-specifc items
' NOTE:  strOpts can be separated into separate options with strLineSep
Public Function MakeTag(strAction As String, Optional strApp As String = "", Optional strID As String = "", Optional strOpts = "") As String

On Error GoTo ERR_FUNC

EXIT_FUNC:
    MakeTag = strAction & strOptionSep & strApp & strOptionSep & strID & strOptionSep & strOpts
    Exit Function

ERR_FUNC:
    Call ErrorMessage(ERR.Description, "CFormHandlers/MakeTag", ERR.Number)
    Resume EXIT_FUNC
    
End Function ' MakeTag

The suggestions are:
  1. Allow adding a private module-level constant for the module name (for example, MOD_NAME) to each module.  PROC_NAME would then be constructed as MOD_NAME & ".[proc_name]".  This would allow refactoring code by moving it from one module to another without having to change PROC_NAME.  It would also be useful any time you wanted to use just the module name somewhere.
  2. Allow the option to also add the selected item to class properties.  I noticed that my Get and Set properties didn't have the constant added (which is mostly fine, but there are a few properties I might have wanted it added to).

Thanks again for a very useful utility.

Steve

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.