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