Lead

Jul 5 06 11:52 AM

Tags : :

Introduction
If you’re familiar with using VBA code to manipulate the contents of cells on a worksheet, you’ll find that when you get used to the different syntax of the different properties that are needed to manipulate code panes and code lines in the VBE window that:
  • a code pane can be treated in a similar manner to a worksheet, i.e. you can add and remove code panes, name or rename them, clear the entire contents of the code pane, move them, etc...
  • each line of code in a code pane can be treated as being very similar to the contents of one of a number of cells running down one column of a worksheet. In this case though, the number of cells (rows) in that column to be considered is dependent on the number of code lines (which can be counted using the methods shown below) that have been written, and is not a fixed number.
Once this concept is grasped and you become familiar with the various properties available for you to use, then, in addition to the basic concepts of adding, removing, naming, renaming, importing and exporting entire code modules, you can read, write, find, add, delete, replace, … , etc. lines, or part-lines of code using the exact same coding techniques you employ for worksheet coding.

Whether these techniques are needed or are surplus to your personal requirements is quite another matter…

Uses:
There may be many reasons to use code to manipulate code, here's a couple...
  • You may have a project you've developed for distribution as an add-in or template and you may have code that automatically installs your project on the users machine. However, this is essentially a 'once-only' event and when your project's installed on the users machine all the installation procedures and code relating to it is immediately redundant and uses memory resources unnecessarily, so it may as well - and can - be deleted, leaving no trace of any of these procedures. (Depending exactly on how the installation code was written, this also removes procedures that may have cause errors if executed twice) - An example of this is given at the end of this article.

  • Often we need to store a setting, password, or constant for use in a procedure the next time a workbook is opened. There are many ways to do this, but they usually involve saving the setting somewhere fairly public, such as (say) in a cell on the worksheet, as a text file, or as a document property. This may or may not always be satisfactory, but there is another alternative - use a procedure to rewrite the relevant procedure (either as an integral part of the code or as a comment) and 'save' it there.
============================================

PREFACE
Before using the examples given below, you'll need to set a reference to the VBA Extensibility library. To do this manually, open the VBE window, go to the Tools menu > References, and then select "Microsoft Visual Basic For Applications Extensibility 5.3" library (Note: it’s 5.3 in Office 2000, other versions may use another number).

Alternatively, you can use the procedure AddReference given below to add the reference programmatically… In fact, if your workbook contains code to manipulate items in the VBE window (and thus requires this reference), it would be preferable to call AddReference as a first priority in a Workbook_Open event.

CODE
Private Sub AddReference()

     Dim Reference As Object

     With ThisWorkbook.VBProject
           For Each Reference In .References
                 If Reference.Description Like "Microsoft Visual Basic for Applications Extensibility*" Then Exit Sub
           Next
           .References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
     End With
     
End Sub


For Office 2002 or higher you will need to check the "Trust Access to Visual Basic Project" box before adding the reference, if it's not known what Office version an end user may be operating, access can be checked with the following procedure (called from the Workbook_Open procedure) - this gives a message that instructs the end user on the steps needed to enable the setting.
CODE
Private Sub AddRefsIfAccessAllowed()
     'Test to ensure access is allowed
     If Application.Version > 9 Then
           Dim VisualBasicProject As Object
           On Error Resume Next
           Set VisualBasicProject = ActiveWorkbook.VBproject
           If Not Err.Number = 0 Then
                 Msgbox "Your current security settings do not allow the code in this workbook        " & vbNewLine & _
                        " to work as designed and you will get some error messages." & vbNewLine & vbNewLine & _
                        "To allow the code to function correctly and without errors you need" & vbNewLine & _
                        " to change your security setting as follows:" & vbNewLine & vbNewLine & _
                        "    1. Select Tools - Macro - Security." & vbNewLine & _
                        "    2. Click the 'Trusted Sources' tab" & vbNewLine & _
                        "    3. Place a checkmark next to 'Trust Access to Visual Basic Project.'" & vbNewLine & _
                        "    4. Save - then Close and re-open the workbook", _
                        vbCritical
                 Exit Sub
           End If
     End If
           
     Call AddReference

End Sub

Note1: In almost all the examples in the following article, we'll be working with the ThisWorkbook object i.e. we’re working with the VBA components in the workbook that actually contains the code. Of course, you can work with any other open workbook, by using ActiveWorkbook or something that would be along the lines of: Workbooks("SomeOtherBook.xls").

Note2: These procedures can not manipulate code or code modules in a workbook that has had the project locked for viewing – in those cases, attempting to run the procedures will generate an error message informing you that the project is locked and the required action can’t be performed.
============================================

Types Of Code Modules
There are various types of code modules in the VBE window, these modules are identified in code with the use of the ProcKind property (a constant) that is an inherent part of the VBComponent object model, they are as follow…

Constant Name {Alias ProcKind}-----Value-----Description
vbext_ct_StdModule-----------------------1-----------Standard code module.

vbext_ct_Document-----------------------100--------Worksheet, chart sheet, or the ThisWorkbook module.

vbext_ct_MSForm--------------------------3-----------UserForm module.

vbext_ct_ClassModule--------------------2-----------Class module, used to create your own objects.

vbext_ct_ActiveXDesigner---------------11----------ActiveX Designer
  • (Note that the value can be used instead of the constant name, however it’s not really recommended that you use the value in that way {simply because it makes it much more difficult for others to read and/or debug your code}).
  • A workbook always contains a ‘ThisWorkbook’ module and must contain at least one ‘Worksheet’ or ‘Chartsheet’ module, these modules are both type vbext_ct_Document modules.
  • Whether the workbook contains any of the other types of modules depends entirely on whether any have been added to it…
============================================

Declaring And Setting A Reference To An Object In The VBE Window
When programming in the VBE, at times it may be helpful to declare and set variables that refer to the object you need to work with. Some examples of this are given below...

The entire VBProject
Dim Project As VBProject
Set Project = ThisWorkbook.VBProject

A single VBComponent
Dim Component As VBComponent (or, Dim Component As VBIDE.VBComponent)
Set Component = ThisWorkbook.VBProject.VBComponents("Module1")

A single CodeModule
Dim Module As CodeModule
Set Module = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
============================================

The Various Code Lines Properties
So one can properly work with or manipulate lines of code in the code module, a fairly good understanding of the different lines properties is needed, the most commonly used of these are listed below…
  • ProcStartLine (a Long is returned)…

    syntax: object .ProcStartLine(ProcName As String {the procedures name as a String}, ProcKind As vbext_ProcKind) As Long.

    A procedure starts at the first line below the End Sub statement of the preceding procedure. i.e. the ProcStartLine is the number of the line immediately after the previous End Sub. (If the procedure is the very first procedure in the module, ProcStartLine starts at the end of the general Declarations section).
    --------------------------------------------------------------------------------
  • ProcCountLines (a Long is returned)…

    syntax: object .ProcCountLines(ProcName As String {the procedures name as a String}, ProcKind As vbext_ProcKind) As Long.

    The ProcCountLines property returns the count of all blank or comment lines preceding the procedure declaration and, if the procedure is the last procedure in a code module, any blank lines following the procedure. i.e. ProcCountLines is the number of lines counted down from the ProcStartLine (above) to the End Sub in the procedure being considered.
    --------------------------------------------------------------------------------
  • CountOfLines (a Long is returned)…

    syntax: object .CountOfLines

    Returns a Long that gives the number of lines of code in a given code module. (Read-only).

    Example: Debug.Print ThisWorkbook.VBProject.VBComponents.("Module1").CodeModule.CountOfLines
    --------------------------------------------------------------------------------
  • ProcBodyLine (a Long is returned)…

    syntax: object .ProcBodyLine(ProcName As String {the procedures name as a String}, ProcKind As vbext_ProcKind) As Long.

    ProcBodyLine returns the first line of a procedure and is the line number on which the Sub, Function, or Property statement appears.

    Example: Debug.Print ThisWorkbook.VBProject.VBComponents("Module1").CodeModule.ProcBodyLine("Macro1", vbext_pk_Proc)
    --------------------------------------------------------------------------------
  • InsertLines (A Long and a String are required)...

    syntax: object .InsertLines(Line As Long {line number where the code is to be inserted}, String as String {the code to be inserted}).

    If the text you insert using InsertLines is carriage return–linefeed delimited, it will be inserted as consecutive lines.
    --------------------------------------------------------------------------------
  • ReplaceLine

    syntax: object .ReplaceLine(Line As Long {line number where the code is to be replaced}, String as String {the replacement code}).

    If the text you are using in the statement ReplaceLine is carriage return–linefeed delimited, it will be inserted as consecutive lines.
    --------------------------------------------------------------------------------
  • DeleteLines (Two Longs are required)...

    syntax: object .DeleteLines(StartLine As Long {line number where the code is to be deleted}, Count As Long {the number of lines to be deleted}).

    StartLine is the number of the line in the code module to start deleting from, and Count is the number of lines to be deleted.

    Note: When the number of lines is omitted from either the DeleteLines or Lines statements below (and the comma of course) the default number for Count is 1 (i.e. 1 line).
    --------------------------------------------------------------------------------
  • ProcOfLine (Returns a String)…

    syntax: object .ProcOfLine(StartLine As Long, ProcKind As vbext_ProcKind) As String.

    ProcOfLine returns the name of the procedure that the specified line is in.
    --------------------------------------------------------------------------------
  • Lines (Returns a String)…

    syntax: object .Lines(Line as Long, Count As Long).

    Lines Returns a string containing the specified number of lines of code specified by Count, where StartLine is the number of the line in the code module to start returning from, and Count is the number of lines to be returned.

    Discussion: Because Lines returns a string, it’s probably one of the most interesting properties of all, as it allows us to read and write code lines, to work with the text and manipulate code in a code module in a very similar manner to that used to manipulate text in worksheet cells.

    For example, when used in conjunction with the If_Then_Else and Like properties, the Lines property can be used to (say) find and replace a line (or any number of lines of code) or, alternatively (using DeleteLines) it can be used to delete a line of code containing the given phrase.

    Alternatively, we can use this property to selectively copy some or all of the lines of code to or from a worksheet (or to/from wherever else we choose).
    --------------------------------------------------------------------------------
============================================

Other Concepts
We’ll now look at the Add, Name and Remove properties and start to put together what we have so far, then introduce a few other basic concepts …

Introducing The Properties: Add and Name

Adding A New Module To A Workbook
In this example we’ll introduce the Add method, and make use of the ProcKind constant, to add a new standard module that will automatically be named ModuleN (where N is an index number) to ThisWorkbook.

CODE
Sub AddModule()

     ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)

End Sub

Now we’ll change the last code slightly to introduce the Name method to both add and name a new standard module that we’ll call "NewModule" to ThisWorkbook.
CODE
Sub AddModule()

     ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule).Name = "NewModule"

End Sub

You can also rename any code modules like this…
CODE
ThisWorkbook.VBProject.VBComponents("Module1").Name = "NewModule"

Or, for sheet modules and the ThisWorkbook module:
CODE
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "MyWorkbook"
--------------------------------------------------------------------------------

Introducing The Property: Remove

Removing A Given Module From A Workbook
We can use Remove to undo the previous action, we’ll use this to delete the new module named "NewModule" from ThisWorkbook. Note that this will also work when the code’s placed in the module that’s being deleted i.e. it can be used for a self-delete.

Also, Note that you cannot delete the ThisWorkbook module, a sheet module, or a chart module in this manner.

CODE
Sub DeleteModule()

     With ThisWorkbook.VBProject
           .VBComponents.Remove .VBComponents("NewModule")
     End With

End Sub


Index Numbers And Names of VBComponents
It’s interesting to note that in general, VBComponents index numbers (a Long) and their names (a String) are readily interchangeable when coding for the VBE window. For instance, when we typed *.VBComponents( * when writing the previous example (DeleteModule), intellisense comes up with the cue ‘Item(Index) As VBComponent’. But it’s usually much more convenient to use the components name instead of an index number and even though an index number has been indicated, visual basic quite readily accepts either an index number or a valid name without question…
============================================

Using Line Properties

Creating A Procedure In A Given Module
We can use the property InsertLines in the procedure below to create an entirely new procedure in a module, this new procedure called "NewProcedure" is added to the new module named "NewModule" that was created in a previous example (above). The property CountOfLines is used in this example to specify that the new procedure is to be placed as the last procedure in the module.

CODE
Sub AddProcedureToNewModule()

     Dim LastLinePlusOne As Long

     With ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule

           LastLinePlusOne = .CountOfLines + 1

           .InsertLines LastLinePlusOne, "Sub NewProcedure()" & vbNewLine & _
                        " Msgbox ""Here is the new procedure"" " & vbNewLine & _
                        "End Sub"
     End With

     Application.Run "NewProcedure"

End Sub

N.B. Although the example was used to create an entire procedure, it is not restricted solely to this use, the InsertLines property can also be used to insert lines in a pre-existing procedure.

Note the way in which the .InsertLines method is called. The entire procedure is passed as one argument - a string with embedded vbNewLine for the line breaks and the last code statement Application.Run "MyNewProcedure" will run the new procedure.

Special Note: To avoid compile-time errors Application.Run is used rather than Call NewProcedure. (n.b. the Application.Run method given will work only if you are adding code to another code module. When adding code to the same code module, an Application.OnTime method must be used so that control is returned to Excel, and time allowed for the module to be recompiled and reloaded).
--------------------------------------------------------------------------------

Introducing The Property: CreateEventProc

Creating An Event Procedure
Here we’ll use InsertLines and introduce the CreateEventProc method to create a new Workbook_Open event in the ThisWorkbook module. CreateEventProc can be used to create an event procedure in a class module, a sheet module, a forms module, or the ThisWorkbook module.

When creating an event procedure, CreateEventProc has an advantage over InsertLines in that CreateEventProc automatically inserts the complete procedure declaration with all the correct parameters, E.G. for a Workbook_SheetSelectionChange event created by CreateEventProc, (ByVal Sh As Object, ByVal Target As Range) is inserted automatically.

CODE
Sub CreateAnEventProcedure()

     Dim FirstLine As Long

     With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

           FirstLine = .CreateEventProc("Open", "Workbook") + 1
           .InsertLines FirstLine, vbNewLine & "Msgbox ""Hello"",vbOkOnly"

     End With

End Sub

--------------------------------------------------------------------------------

Find Specific Code Text And Delete Found Lines Of Code
In this example we’ll use ProcStartLine, ProcCountLines, DeleteLines, and Lines to search for specific text in the code module and delete any code lines found that contain the text we specify.

CODE
Sub DeleteSpecificLineInProcedure()

     Dim FirstLine As Long, LastLineNumber As Long, N As Long

     With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

           FirstLine = .ProcStartLine("DeleteSpecificLineInProcedure", vbext_pk_Proc)
           LastLineNumber = FirstLine + .ProcCountLines("DeleteSpecificLineInProcedure", vbext_pk_Proc)

           For N = FirstLine To LastLineNumber
                 If .Lines(N, 1) Like "*FirstLine =*" Then .DeleteLines N, 1
                 If .Lines(N, 1) Like "*= .Lines(FirstLine + N, 1)*" Then .DeleteLines N, 1
           Next

     End With

End Sub

(Note: When using this method of finding some given code text, we are not restricted to deleting the line, we could just as easily choose to use the ReplaceLine property to re-write the line rather than using DeleteLines to delete it).
--------------------------------------------------------------------------------

Write All The Code For A Given Procedure On A Worksheet
In this example we’ll use ProcStartLine, ProcCountLines, and the Lines property to transfer each line of code in a procedure to a cell in a row on the ActiveSheet

CODE
Sub TransferProcedureCodeToSheet()

     Dim FirstLine As Long, N As Long, NumberOfLines As Long

     Cells.ClearContents
     Application.ScreenUpdating = False
     With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

           FirstLine = .ProcStartLine("TransferProcedureCodeToSheet", vbext_pk_Proc)
           NumberOfLines = .ProcCountLines("TransferProcedureCodeToSheet", vbext_pk_Proc)

           For N = 0 To NumberOfLines
                 Range("A" & N + 1) = .Lines(FirstLine + N, 1)      '< 1 means 1 line at a time
           Next

     End With

     Application.ScreenUpdating = True
End Sub

--------------------------------------------------------------------------------

Write Entire VBA Project Code On A Worksheet
In this example we use the CountOfLines property to tranfer all of the VBA code in the entire project to the ActiveSheet

CODE
Sub TransferAllVBACodeToSheet()

     Dim N As Long
     Dim Component As VBComponent

     Cells.ClearContents
     Application.ScreenUpdating = False

     For Each Component In ThisWorkbook.VBProject.VBComponents
           Range("A" & Rows.Count).End(xlUp).Offset(2, 0) = Component.Name

           With Component.CodeModule
                 For N = 1 To .CountOfLines
                       If .Lines(N, 1) = Empty Then Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = "'"
                       Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = .Lines(N, 1)
                 Next
           End With

     Next

     Application.ScreenUpdating = True
End Sub

--------------------------------------------------------------------------------

Deleting A Given Procedure From A Given Module
Here we use ProcStartLine, ProcCountLines, and DeleteLines to undo a previous action. The procedure below will delete the procedure called "NewProcedure" from the module named "NewModule" in ThisWorkbook.

CODE
Sub DeleteProcedure()

     Dim FirstLine As Long, NumberOfLines As Long

     With ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule

           FirstLine = .ProcStartLine("NewProcedure", vbext_pk_Proc)
           NumberOfLines = .ProcCountLines("NewProcedure", vbext_pk_Proc)

           .DeleteLines FirstLine, NumberOfLines

     End With

End Sub

The method can also be used in a procedure that deletes itself, i.e. it self-deletes as in the second example below where it’s assumed the procedure is in Module1…

CODE
Sub SelfDeleteProcedure()

     Dim FirstLine As Long, NumberOfLines As Long

     On Error Resume Next
     With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

           FirstLine = .ProcStartLine("SelfDeleteProcedure", vbext_pk_Proc)
           NumberOfLines = .ProcCountLines("SelfDeleteProcedure", vbext_pk_Proc)
     
           .DeleteLines FirstLine, NumberOfLines

     End With

End Sub

--------------------------------------------------------------------------------

Deleting All Code From A Given Module
In this example we use the DeleteLines property to delete all code from the module named "NewModule" (Also works as a self-delete)

CODE
Sub DeleteAllCodeInModule()

     Dim FirstLine As Long, NumberOfLines As Long

     With ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule

           FirstLine = 1
           NumberOfLines = .CountOfLines

           .DeleteLines FirstLine, NumberOfLines

     End With
End Sub

--------------------------------------------------------------------------------

Deleting All VBA Code In A Project
The procedure below uses DeleteLines and Remove to delete all the VBA code in a project.

You should use this procedure with care, as it will permanently delete the code. All standard modules, user forms, and class modules will be removed, and code within the ThisWorkbook module and the sheet modules will be deleted. (You may want to export the VBA code before running this procedure. Alternatively, SAVE, Run the procedure, then Exit without saving).

CODE
Private Sub DeleteAllVBAcode()

     Dim Component As VBIDE.VBComponent

     For Each Component In ThisWorkbook.VBProject.VBComponents

           Select Case Component.Type
           Case vbext_ct_StdModule, vbext_ct_MSForm, _
                   vbext_ct_ClassModule
                   ThisWorkbook.VBProject.VBComponents.Remove Component
           Case Else
                 With Component.CodeModule
                        .DeleteLines 1, .CountOfLines
                 End With
           End Select
     Next

End Sub

--------------------------------------------------------------------------------

Listing All Procedures In A Given Module
The example below uses CountOfDeclarationLines and ProcOfLine to list (in a message box) the names of all the procedures in a standard code module called "SaveModule" in ThisWorkbook. Procedures are listed in the order in which they appear in the CodeModule object.

CODE
Sub ListProceduresInModule()

     Dim FirstLine As Long, List As String, ProcName As String

     With ThisWorkbook.VBProject.VBComponents("Module2").CodeModule

           FirstLine = .CountOfDeclarationLines + 1

           Do Until FirstLine >= .CountOfLines
                 List = List & .ProcOfLine(FirstLine, vbext_pk_Proc) & vbNewLine
                 FirstLine = FirstLine + .ProcCountLines(.ProcOfLine(FirstLine, _
                                                         vbext_pk_Proc), vbext_pk_Proc)
           Loop

     End With

     MsgBox List

End Sub

--------------------------------------------------------------------------------

Listing All Modules In A Workbook
The example below will list (in a message box) the names of all the modules in ThisWorkbook. For this example we’ll create a function named ComponentType (given below) to get a string describing the type of module.

CODE
Sub ListAllModules()

     Dim Component As VBComponent, List As String

     For Each Component In ThisWorkbook.VBProject.VBComponents
           List = List & Component.Name & " Type: " & ComponentType(Component) & vbNewLine
     Next

     MsgBox List

End Sub


CODE
Function ComponentType(Component As VBComponent) As String

     Select Case Component.Type
     Case vbext_ct_ActiveXDesigner
           ComponentType = "ActiveX Designer"
     Case vbext_ct_ClassModule
           ComponentType = "Class Module"
     Case vbext_ct_Document
           ComponentType = "Document"
     Case vbext_ct_MSForm
           ComponentType = "MS Form"
     Case vbext_ct_StdModule
           ComponentType = "Standard Module"
     Case Else
     End Select

End Function

============================================

Introducing A New Property: Export

Exporting All Modules In A Project
The example below will export all of the modules in a workbook to text files (.cls, .frm, or .bas). It will save the files in the same folder as the workbook. This can be useful for saving a backup copy of your VBA, or for transferring VBA code from one project to another.

CODE
Sub ExportAllVBAInWorkbook()

     Dim Component As VBIDE.VBComponent, Suffix As String

     For Each Component In ActiveWorkbook.VBProject.VBComponents

           Select Case Component.Type
           Case vbext_ct_ClassModule, vbext_ct_Document: Suffix = ".cls"
           Case vbext_ct_MSForm: Suffix = ".frm"
           Case vbext_ct_StdModule: Suffix = ".bas"
           Case Else: Suffix = Empty
           End Select

           If Suffix <> Empty Then
                 Component.Export Filename:=ActiveWorkbook.Path & "\" & Component.Name & Suffix
           End If
     Next

End Sub

--------------------------------------------------------------------------------

Introducing A New Property: Import

Import A Module
This is an example of how you can import a module into the project.

CODE
Sub ImportOneModule()

     ThisWorkbook.VBProject.VBComponents.Import ThisWorkbook.Path & "\Module1.bas"

End Sub

--------------------------------------------------------------------------------

Transferring Modules Between Different Projects
There isn't a single method to transfer modules from one VBProject to another. Instead, you have to Export the module from one project, and then Import it into another. The following example procedure will copy Module1 from Book2 to Book1.

CODE
Sub ExportAndImportOneModule()

     'export from Book2
     Workbooks("Book2").VBProject.VBComponents("Module1").Export Workbooks("Book2").Path & "\code.bas"

     'import to Book1
     Workbooks("book1").VBProject.VBComponents.Import Workbooks("Book2").Path & "\code.bas"

End Sub

Just change "Module1" to the name of the module you want to copy. If you want to copy all modules (except the ThisWorkbook and Sheet modules), you can use the following code.

CODE
Sub ExportAndImportAllModules()

     Dim FName As String
     Dim Component As VBIDE.VBComponent

     With Workbooks("Book2")
           FName = .Path & "\code.txt"
           If Dir(FName) <> Empty Then
                 Kill FName
           End If

           For Each Component In .VBProject.VBComponents
                 If Component.Type <> vbext_ct_Document Then
                       Component.Export FName
                       Workbooks("book1").VBProject.VBComponents.Import FName
                       Kill FName
                 End If
           Next

     End With

End Sub

============================================

Test For Existence

Function To Test That A Module Exists
When writing code to do something with a named module, to avoid the possibility of errors you may need to test if the module still exists before running your code.

CODE
Function ModuleExists(ModuleName As String) As Boolean

     On Error Resume Next
     ModuleExists = Len(ThisWorkbook.VBProject.VBComponents(ModuleName).Name)

End Function

Example Usage:
CODE
Sub TestForModule()

     If ModuleExists("Module1") Then MsgBox "Module1 exists"

End Sub

--------------------------------------------------------------------------------

Function To Test That A Procedure Exists
When writing code to do something with a named procedure, to avoid possible errors you can use this function in conjunction with ModuleExists (above) to determine whether the procedure still exists in that module.

CODE
Function ProcedureExists(ProcedureName As String, ModuleName As String) As Boolean

     On Error Resume Next
     If ModuleExists(ModuleName) Then
           ProcedureExists = ThisWorkbook.VBProject.VBComponents(ModuleName) _
                             .CodeModule.ProcStartLine(ProcedureName, vbext_pk_Proc)
     End If

End Function

Example Usage:
CODE
Sub TestForModule()

     If ProcedureExists("ProcedureExists", "Module1") Then MsgBox "ProcedureExists exists"

End Sub

============================================


References: Quite a lot of this material was resourced from the VBA Help files, but a goodly proportion came from Chip Pearsons shorter article on programming to the VBE.


Example Usage - Installing a template:
We assume here that there may be some Workbook_Open procedures to be called every time a workbook based on the template is opened, so, instead of relying on a Workbook_Open procedure for the installation, we'll use an Auto_Open procedure (placed in Module1) instead (where we also assume that Module1 is not to be simply removed because there may be other procedures in it.).

Note that in the code that follows, after all code in the code module has been deleted there are still actions pending (such as removing the reference and a 'Save') after all the code's been removed. Obviously these actions can no longer be dictated by what is written in the code pane at the time that the action is being taken.

These actions can only be undertaken by the compiled code. It is therefore essential that your code is fully compiled before distributing your template. This is done as follows:

Set the reference to Microsoft Visual Basic for Applications Extensibility 5.3, and in the VBE window click 'Debug' and then select 'Compile VBA Project' then Save.

You can now either remove or leave the reference (as it's checked and set programmatically if missing anyway).

The reason for these precautions is that if the code is not compiled and the end-user has the 'Background Compile' option checked (in Tools > Options > General) the code is being compiled while it's running and the compilation may still be incomplete at the time the code is deleted - this can easily give rise to a compile problem upon installation on the users machine.


CODE
Option Explicit

'//**********<< TEMPLATE INSTALLATION CODE >>**********\\
Private Sub Auto_Open()
     '-------------------------------------------------------------------------
     'if not referenced, set a reference to Microsoft
     'Visual Basic for Applications Extensibility 5.3
     Call AddRefsIfAccessAllowed
     '-------------------------------------------------------------------------
     '-------------------------------------------------------------------------
     Call InstallAndRemoveInstallCode
     '-------------------------------------------------------------------------
End Sub


Private Sub AddRefsIfAccessAllowed()

     'Test to ensure access is allowed
     If Application.Version > 9 Then

           Dim VisualBasicProject As Object
           On Error Resume Next
           Set VisualBasicProject = ActiveWorkbook.VBProject
           If Not Err.Number = 0 Then
                 MsgBox "Your current security settings do not allow the code" _
                      & " in this workbook " & vbNewLine & _
                      " to work as designed and you will get some error messages." _
                      & vbNewLine & vbNewLine & _
                        "To allow the code to function correctly and without errors " _
                      & "you need" & vbNewLine & _
                      " to change your security setting as follows:" _
                      & vbNewLine & vbNewLine & _
                      " 1. Select Tools - Macro - Security." & vbNewLine & _
                      " 2. Click the 'Trusted Sources' tab" & vbNewLine & _
                      " 3. Place a checkmark next to 'Trust Access to Visual " _
                      & "Basic Project.'" & vbNewLine & _
                      " 4. Save - then Close and re-open the workbook", _
                        vbCritical
                 Exit Sub
           End If
     End If

     Call AddReference

End Sub

Private Sub AddReference()

     Dim Reference As Object

     With ThisWorkbook.VBProject
           For Each Reference In .References
                 If Reference.Description _
                    Like "Microsoft Visual Basic for Applications Extensibility*" _
                    Then Exit Sub
           Next
           .References.AddFromGuid _
                       "{0002E157-0000-0000-C000-000000000046}", 5, 3
     End With

End Sub

Private Sub InstallAndRemoveInstallCode()

     Dim FirstLine As Long, LastLine As Long, N As Long
     With ThisWorkbook.VBProject

           With .VBComponents("Module1").CodeModule

                 '//remove the comment at the top\\
                 For N = 1 To .CountOfLines
                       If .Lines(N, 1) Like "*INSTALLATION CODE*" Then _
                          .DeleteLines N, 1
                 Next

                 '//delete the "Auto_Open" procedure\\
                 FirstLine = .ProcStartLine("Auto_Open", vbext_pk_proc)
                 LastLine = .ProcCountLines("Auto_Open", vbext_pk_proc)
                 .DeleteLines FirstLine, LastLine

                 '//delete the "AddRefsIfAccessAllowed" procedure\\
                 FirstLine = .ProcStartLine("AddRefsIfAccessAllowed", _
                                            vbext_pk_proc)
                 LastLine = .ProcCountLines("AddRefsIfAccessAllowed", _
                                            vbext_pk_proc)
                 .DeleteLines FirstLine, LastLine

                 '//delete the "AddReference" procedure\\
                 FirstLine = .ProcStartLine("AddReference", vbext_pk_proc)
                 LastLine = .ProcCountLines("AddReference", vbext_pk_proc)
                 .DeleteLines FirstLine, LastLine

                 '//delete THIS entire procedure (self-delete)\\
                 FirstLine = .ProcStartLine("InstallAndRemoveInstallCode", vbext_pk_proc)
                 LastLine = .ProcCountLines("InstallAndRemoveInstallCode", vbext_pk_proc)
                 .DeleteLines FirstLine, LastLine

                 'we'll remove the reference to clean up after installation
                 Dim Reference As Object

                 For Each Reference In ThisWorkbook.VBProject.References
                       If Reference.Description Like _
                          "Microsoft Visual Basic for Applications Extensibility*" Then
                             ThisWorkbook.VBProject.References.Remove Reference
                       End If
                 Next

           End With
     End With

     '//now install and save to 'fix' these changes\\
     ThisWorkbook.SaveAs _
                 Filename:=Application.TemplatesPath & _
                           "My Project.xlt", _
                           FileFormat:=xlTemplate

     'let the end-user know how to access the template
     MsgBox "The 'My Project' template has been installed. To complete" & _
            vbNewLine & _
            "the process you should now close this workbook." & _
            vbNewLine & vbNewLine & _
            "To open a new workbook that uses this template, use the" & _
            vbNewLine & _
            "'New Office Document' dialog and select 'My Project' from" & _
            vbNewLine & _
            "the available options." & vbNewLine, _
            vbInformation, "Template Installed"

End Sub

If I irradiate my cat will that give it eighteen half-lives?

The major part of getting the right answer lies in asking the right question...