none
Use VBA to turn on Option Explicit RRS feed

  • Question

  • Is there a way to use VBA code to insert the Option Explicit statement at the top of every module? and set the option Require Variable Declaration to true.

    Thanks

    Wednesday, February 21, 2018 12:45 PM

Answers

All replies

  • You can certainly add Option Explicit to the top of each module of a document, but I don't know of a means of setting the option from VBA. To add 'Option Explicit' to the top of each module from which it is missing, call the following function.

    The code will work in Excel etc also. However it will be necessary to set the macro security to trust access to the VBA project object model manually before this code will access the VBA project.

    Public Function AddVBACode(oDoc As Object)
    Dim i As Long
    Dim lngLines As Long
        For i = 1 To oDoc.VBProject.VBComponents.Count
            lngLines = oDoc.VBProject.VBComponents.Item(i).CodeModule.CountOfLines
            If InStr(1, oDoc.VBProject.VBComponents.Item(i).CodeModule.Lines(1, lngLines), "Option Explicit") = 0 Then
            oDoc.VBProject.VBComponents.Item(i).CodeModule.InsertLines 1, "Option Explicit" & vbCr
    Debug.Print oDoc.VBProject.VBComponents.Item(i).Name
            End If
            DoEvents
        Next i
    lbl_Exit:
        Exit Function
    End Function


    Graham Mayor - Word MVP
    www.gmayor.com

    • Proposed as answer by Wouter Defour Thursday, February 22, 2018 10:48 AM
    Wednesday, February 21, 2018 1:37 PM
  • How would I call this function (the parameter to pass)

    What would I declare the object as?

    Thanks

    Wednesday, February 21, 2018 5:29 PM
  • oDoc is the document or workbook containing the project in which you want to examine the modules for Option Explicit.

    e.g. For Word - Call it from the application in question, with the document or template you want to examine open e.g.

    Sub Example()
        AddVBACode ActiveDocument
    End Sub

    If the function has been added to (say) the Normal template in Word and you want to examine that template you would replace ActiveDocument with ThisDocument.

    The process is essentially similar from Excel except this time it would be ActiveWorkbook or ThisWorkbook as appropriate.


    Graham Mayor - Word MVP
    www.gmayor.com

    Thursday, February 22, 2018 7:38 AM
  • Sorry to be a pain but I was wondering how to use the function in MS Access.

    For Excel I am using the ActiveWorkbook and that works fine.

    However for MS Access I tried CurrentDB, Workspaces(0), DbEngine,  couldn't get it to work.

    Thanks

    Thursday, February 22, 2018 4:12 PM
  • I don't work with Access - sorry.

    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, February 23, 2018 7:13 AM
  • James,

    Is this what you're looking for:

    Sub VBA_Insert()
        Dim i As Long
        Dim lngLines As Long
        Dim VBC As Object
        For i = 1 To Application.VBE.VBProjects(1).VBComponents.Count
            Set VBC = Application.VBE.VBProjects(1).VBComponents(i).CodeModule
            lngLines = VBC.CountOfLines
            'Insert Option Explicit as second line after Option Compare Database
            If InStr(1, VBC.Lines(1, lngLines), "Option Explicit") = 0 Then
                If InStr(1, VBC.Lines(1, 1), "Option Compare Database") = 0 Then
                    VBC.InsertLines 1, "Option Explicit"
                Else
                    VBC.InsertLines 2, "Option Explicit"
                End If
            End If
            Debug.Print VBC.Name
        Next
    End Sub
    

    Jan

    Sunday, February 25, 2018 8:42 AM
  • This works except for the module where the code is in, is there a way to get around this?

    Thanks

    Monday, February 26, 2018 5:56 PM
  • James,

    Yes, because the code looks for "Option Explicit" in all the lines in the module it will find it in the module with this code and assumes it doesn't need to write it in the module.

    change this line

    If InStr(1, VBC.Lines(1, lngLines), "Option Explicit") = 0 Then

    to

    If InStr(1, VBC.Lines(1, 5), "Option Explicit") = 0 Then

    The code then looks only in the first 5 lines, and that is the place where this text should be.

    Perhaps it is possible, and it would be nicer, to only look in code outside a procedure, but I'm not sure how to do that.

    That is: looking only in the declaration section of a module:

    Sub VBA_Insert()
        Dim i As Long
        Dim lngLines As Long
        Dim VBC As Object
        For i = 1 To Application.VBE.VBProjects(1).VBComponents.Count
            Set VBC = Application.VBE.VBProjects(1).VBComponents(i).CodeModule
            lngLines = VBC.CountOfDeclarationLines
    
            If InStr(1, VBC.Lines(1, lngLines), "Option Explicit") = 0 Then
                If InStr(1, VBC.Lines(1, 1), "Option Compare Database") = 0 Then
                    VBC.InsertLines 1, "Option Explicit"
                Else
                    VBC.InsertLines 2, "Option Explicit"
                End If
            End If
        Next
    End Sub
    

    Jan


    • Edited by jgkzdl Monday, February 26, 2018 6:27 PM
    Monday, February 26, 2018 6:16 PM
  • great thanks, that works fine.
    • Marked as answer by James N San Monday, February 26, 2018 7:33 PM
    Monday, February 26, 2018 7:33 PM