<

Go Premium for a chance to win a PS4. Enter to Win

x

Automatic Insertion of Procedure Names in VBA and VB6 Code

Published on
16,220 Points
4,920 Views
3 Endorsements
Last Modified:
Martin Liss
Over 40 years of programming experience. Expand my "Full Biography" to see links to some articles I've written.
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
Comment
Author:Martin Liss
  • 2
2 Comments
 
LVL 49

Author Comment

by:Martin Liss
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
0
 
LVL 49

Author Comment

by:Martin Liss
Updated 9/20/2014
Version 3.0.1

Corrected bug where commented out procedures were being considered as active code.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Join & Write a Comment

Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month