Using VBA to Update Your Code Modules
Many people don't realize it, but Excel 97 (and later versions) has the ability to update VBA code modules dynamically. In other words, you can write VBA code that writes VBA code.
Referring to the module
The first step is to create an object reference for the code module that you'll be modifying. The CodeModule property returns an object that represents the code behind the VB component. The statement below demonstrates. In this case, WBCodeMod is an object variable that represents the code in Module1 in the active workbook.
Set MyCodeMod = ActiveWorkbook.VBProject. _
VBComponents("Module1").CodeModule
Counting lines in the module
You can use the CountOfLines property to determine how many lines of code exist in the module (the line count includes blank lines). You can use this to determine if a module is empty. Here's an example:
MsgBox MyCodeMod.CountOfLines
Check the Help system
The Excel Visual Basic Help file contains complete details on the properties and methods that you can use to manipulate code modules. You'll find that methods are available to search for text in a module, count the number of lines is a procedure, insert lines, delete lines, and so on.
Download an example
I created an add-in that demonstrates some of the code module manipulation techniques. This add-in, called AddPath, makes it easy for a novice user to insert a VBA event procedure into a workbook.
In this example, the event procedure is triggered by the BeforePrint event. The code in this procedure inserts the workbook's full path into a specified header or footer location. As you probably know, a major limitation of Excel is its inability to print a file's path in a header or footer. This can be accomplished by trapping the BeforePrint event. The AddPath add-in simply creates the event procedure for the user -- eliminating the need for the user to activate the VB Editor.
The add-in is not protected, so you can examine the underlying code.