Automatically add `Option Private Module` to all VBA modules

Question

Is there a way to automatically add Option Private Moduleto all modules in VBA?

Something like automatic adding Option explicitwhen we put a checkbox in the Extras> Option> editor to declare variables?

Because, going through all the modules and recording it manually, it somehow seems to be the only option.

Sub-question: If you need to add Option Private Moduleto all modules in 10 applications, what would you do?

At first I thought to use a simple Replace and replace it Option explicitwith Option Explicit ^p Option Private Module, but it will replace it in the classes, and I have to remove it from there.

Ideas to save about 30 minutes here?

Answer 1

This should do you most of the way, and this example works for all open, insecure projects. If you need to change protected projects, first open them.

Remember that you need to explicitly save changes to add-ons.

See inline comments for rationale for various checks.

Sub Foo()

  'Add a reference to Visual Basic for Applications Extensibility
  Dim proj As VBIDE.VBProject
  Dim comp As VBIDE.VBComponent
  For Each proj In Application.VBE.VBProjects

    'Check the project isn't protected
    If proj.Protection = vbext_pp_none Then

      For Each comp In proj.VBComponents
        'Check we're working with a standard module
        If comp.Type = vbext_ct_StdModule Then
          'TODO: Check that Option Private Module doesn't already exist
          comp.CodeModule.InsertLines 1, "Option Private Module"
        End If

      Next comp

    End If

  Next proj

End Sub

Edit from OP (@vityata): I decided to add my updated one to your answer (I hope you do not mind). This is due to late binding, so libraries are not needed:

'---------------------------------------------------------------------------------------
' Method : AddOptionPrivate
' Author : stackoverflow.com
' Date   : 12.01.2017
' Purpose: Checking for "Option Private Mod~" up to line 5, if not found we add it in
'           every module
'---------------------------------------------------------------------------------------
Sub AddOptionPrivate()

    Const UP_TO_LINE = 5
    Const PRIVATE_MODULE = "Option Private Module"

    Dim objXL               As Object

    Dim objPro              As Object
    Dim objComp             As Variant
    Dim strText             As String

    Set objXL = GetObject(, "Excel.Application")
    Set objPro = objXL.ActiveWorkbook.VBProject

    For Each objComp In objPro.VBComponents
        If objComp.Type = 1 Then
            strText = objComp.CodeModule.Lines(1, UP_TO_LINE)

            If InStr(1, strText, PRIVATE_MODULE) = 0 Then
                objComp.CodeModule.InsertLines 2, PRIVATE_MODULE
            End If

        End If
    Next objComp

End Sub

Answer 2

It took me a little longer to change my exisitng code, you can try the following code below to display all the places of "Option Explicit" with "Private Private Module".

the code

Option Explicit

Sub ReplaceOptionExplicitInModules()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim i As Long

' loop though all open projects
For Each VBProj In Application.VBE.VBProjects

    If VBProj.Protection = vbext_pp_none Then

        ' loop through all modules, worksheets and other objects in VB Project
        For Each VBComp In VBProj.VBComponents

            If VBComp.Type <> vbext_ct_ClassModule Then  ' <-- check if module type is not class (to replace also on sheet and workbook events)
                Set CodeMod = VBComp.CodeModule

                ' loop through all code lines inside current module
                For i = 1 To CodeMod.CountOfLines
                    If Len(CodeMod.Lines(i, 1)) > 0 Then
                        ' if line contents is "Option Explicit*" 
                        If CodeMod.Lines(i, 1) Like "Option Explicit*" Then
                            CodeMod.ReplaceLine i, "Option Private Module"
                        End If
                    End If
                Next i
            End If
        Next VBComp
    End If
Next VBProj

End Sub