Locating Each Form Module Which is Missing "Option Explicit" Without Opening Every Form

Question

I have far too many database files and each, has far too many forms, for me to open each form, in order to include "Option Explicit" where it doesn't exist.  How do I find the modules which are missing this clause?

Answer 1

This is very rough code, but it serves to search all modules (standard, class, form, or report) in the current database for any that don't contain the string "Option Explicit" anywhere. That's not to say that a module couldn't contain that string in a comment or in a literal, but it's a start.

'------ start of code ------

Sub ListModulesWithoutOptionExplicit()

    ' Check all modules to see if they have "Option Explicit" specified.

    ' List those that don't in the Immediate Window.

    On Error GoTo Err_Handler

    Dim db              As DAO.Database

    Dim cnt             As DAO.Container

    Dim doc             As DAO.Document

    Dim mdl             As Access.Module

    Dim frm             As Access.Form

    Dim rpt             As Access.Report

    Dim strModuleName   As String

    Dim lngSearchCount  As Long

    Dim lngFoundCount   As Long

    Dim bFound          As Boolean

    Dim bOpenedModule   As Boolean

    Debug.Print "*** Searching standard and class modules for those not containing ""Option Explicit"" ..."

    Set db = CurrentDb

    Set cnt = db.Containers("Modules")

    For Each doc In cnt.Documents

        lngSearchCount = lngSearchCount + 1

        strModuleName = doc.Name

        If CurrentProject.AllModules(strModuleName).IsLoaded = False Then

            DoCmd.OpenModule strModuleName

            bOpenedModule = True

            DoEvents

        Else

            bOpenedModule = False

        End If

        Set mdl = Modules(strModuleName)

        If mdl.Find("Option Explicit", 0, 0, 0, 0, True) Then

            ' Found "Option Explicit", so do nothing

        Else

            Debug.Print strModuleName

            lngFoundCount = lngFoundCount + 1

        End If

        If bOpenedModule = True Then

            DoCmd.Close acModule, strModuleName, acSaveNo

        End If

        Set mdl = Nothing

    Next doc

    Debug.Print "*** Searching form modules ..."

    For Each doc In db.Containers("Forms").Documents

        If CurrentProject.AllForms(doc.Name).IsLoaded = False Then

            DoCmd.OpenForm doc.Name, acDesign, WindowMode:=acHidden

            bOpenedModule = True

        Else

            bOpenedModule = False

        End If

        DoEvents

        strModuleName = doc.Name

        Set frm = Forms(strModuleName)

        With frm

            If frm.HasModule Then

                lngSearchCount = lngSearchCount + 1

                If frm.Module.Find("Option Explicit", 0, 0, 0, 0, True) Then

                    ' Found "Option Explicit", so do nothing

                Else

                    Debug.Print strModuleName

                    lngFoundCount = lngFoundCount + 1

                End If

            End If

            If bOpenedModule Then

                DoCmd.Close acForm, .Name, acSaveNo

            End If

        End With

        Set frm = Nothing

    Next doc

    Debug.Print "*** Searching report modules ..."

    For Each doc In db.Containers("Reports").Documents

        If CurrentProject.AllReports(doc.Name).IsLoaded = False Then

            DoCmd.OpenReport doc.Name, acDesign, WindowMode:=acHidden

            bOpenedModule = True

        Else

            bOpenedModule = False

        End If

        DoEvents

        strModuleName = doc.Name

        Set rpt = Reports(strModuleName)

        With rpt

            If rpt.HasModule Then

                lngSearchCount = lngSearchCount + 1

                If rpt.Module.Find("Option Explicit", 0, 0, 0, 0, True) Then

                    ' Found "Option Explicit", so do nothing

                Else

                    Debug.Print strModuleName

                    lngFoundCount = lngFoundCount + 1

                End If

            End If

            If bOpenedModule Then

                DoCmd.Close acReport, .Name, acSaveNo

            End If

        End With

        Set rpt = Nothing

    Next doc

Exit_SearchModules:

    Set cnt = Nothing

    Set db = Nothing

    Debug.Print "*** Searched " & lngSearchCount & _

                " modules, found " & lngFoundCount & " occurrences."

    Exit Sub

Err_Handler:

    Debug.Print "* ERROR processing module '" & strModuleName & "' : " & Err.Number & ", " & Err.Description

    Resume Next

'    MsgBox Err.Description, vbExclamation, "Error " & Err.Number

'    If MsgBox("Continue?", vbYesNo, "Continue?") = vbYes Then

'        Resume Next

'    End If

    Resume Exit_SearchModules

End Sub

'------ end of code ------

Answer 2

I thank you ALL for your insightful verificative time!  I truly enjoy learning.  I am thirsty for VBA knowledge, and am always excited to have something that works well.  Just to update my brain, while the topic's on the table (LOL), I am trying to remember also, if a "Class Module" is the kind behind a form or report, and a "Standard Module" is a standalone module, which resides in the "MODULES" group, in the Navigation pane, right?

Form and report modules are class modules.  These class modules are not listed in the Nav pane's Modules group.  It's also possible to create your own class modules, separate from the forms and reports, and use them in your code.  If you do, they will be listed in the Modules group of the Navigation pane, along with the standard modules.

The main difference between standard modules and class modules is that there can only be one instance of a standard module, and you don't have to explicitly create an instance.  Any time you refer to a procedure or public variable in a standard module, that module is automatically loaded into memory if it hasn't already been loaded.  By contrast, class modules can have multiple instances, and you have to explicitly create an instance of the module before you can use it.  Then all calls to procedures or public variables in the class module must be qualified with a reference to the specific instance you've created.

Answer 3

I've never seen "Sub" by itself; I've always seen Public Sub or Private Sub.  And so what event runs this? ...a click?  or perhaps a call?

Answer 4

It's possible to write code to search all modules in the database for any that don't contain the text "Option Explicit".  I have something I can adapt; I'll post it.

Answer 5

I put the correct two lines in my copy buffer. Then search for Option Compare Database, and if I don't see the two lines, I hit Paste.