excel - Automatically add `Option Private Module` to all modules in VBA

Question

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

Something like the automatic adding of Option explicit when we mark the checkbox in the Extras>Option>editor for declaration of variables?

Because going through all modules and writing it manually somehow seems like the only option.

Sub-question: If you have to add Option Private Module to all modules in 10 applications what would you do?

At first I was thinking to use a simple Replace and to replace the Option Explicit with Option Explicit ^p Option Private Module, but it would replace it in the classes as well and I have to delete it from there.

Ideas to save some 30 minutes here?

Answer 1

This should get you most of the way there, and this example works for all open, unprotected projects. If you need to modify protected projects, just unprotect them first.

Remember that you'll need to explicitly save changes to any add-ins.

See the inline comments for the rationale behind 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 have decided to add to your answer my updated one (I hope you do not mind). It is with late binding, thus no libraries are 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