[AccessD] How to automatically add "Option Explicit" toexisting code modules

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________


 
One last thing:

Just for the archives and to help out others, you will need to ensure the reference in the VBE to "Microsoft Visual Basic For Applications Extensibility 5.3" is selected.  This is true anytime you use code to mod code in VBA.  If this ref is not set then the code below will fail.

regards
Darryl




-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Thursday, 11 November 2010 1:50 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] How to automatically add "Option Explicit" toexisting code modules

Absolutely sensational :)

I had to make a minor mod so the code will work in MS Access VBE rather than MS Excel VBE.  But otherwise this is just wonderful.  Thank you very much :)  I have posted the code below with the change for MS Access vs MS Excel.

Many thanks, this has saved me hours of stuffing around.

Cheers
Darryl.


'=========== CODE TESTED & OPERATIONAL IN MS ACCESS 2003 =======================

 Option Explicit
Option Compare Database

Sub AddProcedureToModule()

    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim strOld As String
    Dim strNew As String
    Dim cntLines As Long

    Set VBProj = VBE.ActiveVBProject ' Use if MS Access
    'Set VBProj = ActiveWorkbook.VBProject ' Use if MS Excel

    For Each VBComp In VBProj.VBComponents

Debug.Print VBComp.Name

        If VBComp.CodeModule.CountOfLines > 0 Then

            strOld = VBComp.CodeModule.Lines(1, VBComp.CodeModule.CountOfLines)

            If strOld Like "*Option Explicit*" Then
                ' do nothing
            Else

                strNew = "Option Explicit" & Chr(10) & strOld
                cntLines = VBComp.CodeModule.CountOfLines
                VBComp.CodeModule.DeleteLines 1, cntLines
                VBComp.CodeModule.AddFromString strNew
                strOld = vbNullString
                strNew = vbNullString
            End If

        End If

    Next

End Sub
'===========================================================================================
_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended 
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review, 
retransmission, dissemination or other use of, or taking of any action in reliance 
upon this information, by persons or entities other than the intended recipient is 
prohibited.

If you have received this in error, please contact the sender and delete this e-mail 
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute 
the information contained in this e-mail and any attached files, with the permission 
of the sender.

This message has been scanned for viruses.
_______________________________________________________________________________________