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

Darryl Collins Darryl.Collins at iag.com.au
Wed Nov 10 20:49:50 CST 2010


_______________________________________________________________________________________

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



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
'===========================================================================================

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

Now that IS the sneaky cheeky stuff!

I'm keeping it!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kevin Waddle
Sent: Wednesday, November 10, 2010 6:59 PM
To: 'Access Developers discussion and problem solving'
Cc: Darryl.Collins at iag.com.au
Subject: Re: [AccessD] How to automatically add "Option Explicit" toexisting
code modules

Darryl,

 

This should work.Just make sure you backup and try it on a safety copy
first.

 

Option Explicit

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 = ActiveWorkbook.VBProject

    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

            Else

                strNew = "Option Explicit" & Chr(10) & strOld

                cntLines = VBComp.CodeModule.CountOfLines

                VBComp.CodeModule.DeleteLines 1, cntLines

                VBComp.CodeModule.AddFromString strNew

                strOld = ""

                strNew = ""

            End If

        End If

    Next

End Sub

 

Kevin Waddle
thewaddles at sbcglobal.net

When God speaks, oftentimes His voice will call for an act of courage on our
part. ~ Charles Stanley 

From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Wednesday, November 10, 2010 3:59 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] How to automatically add "Option Explicit" to existing
code modules

 

 

____________________________________________________________________________
___________

 

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

____________________________________________________________________________
___________

 

 

Hi folks,

 

"How to add "Option Explicit" to existing code modules"

 

I am not optimistic about this one as a Google search was fairly fruitless,
although I did find one tool suite that claimed to do this, but is is $200+
for a single user license so I lost interest at that point.  Maybe I am not
searching with the best keywords.

 

I am working on databases largely built by others and none of them use
"Option Explict".  As a result there are dozens of little code errors that
reveal themselves when explict is turned on. 

 

Of course the first thing I do is turn this option on, but that only applys
to any new modules added.  Clearly I can manually paste in 'Option Explicit'
into every existing form, report & code module, but there are hundreds of
the little buggers and I would prefer not to have to do this manually.

 

Does anyone has some sneaky cheeky code that will check for option explict
and write it into the form/module etc if it is missing?  I would of thought
this would have been a fairly useful thing to do - and a standard option to
have from the VBE, but hey, what do I know :)

 

cheers

Darryl

____________________________________________________________________________
___________

 

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.

____________________________________________________________________________
___________

 

--

AccessD mailing list

AccessD at databaseadvisors.com

http://databaseadvisors.com/mailman/listinfo/accessd

Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________________________________________________

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.
_______________________________________________________________________________________




More information about the AccessD mailing list