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?

If you build an array with all modules you want it replaced, then when using the replace you can check if current module is inside that array before doing the replace. — Shai Rado, Jan 12 '17 at 11:39
@ShaiRado, I did not get your idea. The replace I meant is the one that you get when you press `Ctrl+H` in the VBEditor. — Vityata, Jan 12 '17 at 11:42
I was reffering on building some code with the VBProject editor, but in your case, maybe manual work will be faster — Shai Rado, Jan 12 '17 at 11:43
Look for vba code on the net, to export vba modules, modify a touch to exclude the code you want to do the replace on. Delete the exported classes and then do the replace, then similar code to import from the files created. But not a tickbox solution, possible in under 30 mins, Ron's got a start here http://www.rondebruin.nl/win/s9/win002.htm — Nathan_Sav, Jan 12 '17 at 11:46
@Vityata it took me a little longer than I thought to modify an exisitng code of mine, but I;ve got something below (less than 30 minutes, just 29 :) — Shai Rado, Jan 12 '17 at 12:13

score 9 · Accepted Answer · edited Jan 12 '17 at 13:52

9

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

edited Jan 12 '17 at 13:52

Vityata

42,633
8
55
100

answered Jan 12 '17 at 11:49

ThunderFrame

9,352
2
29
60

1

So far it seems like it works :) Just set a reference to `Microsoft Visual Basic for Applications Extensibility 5.3 library` and probably insert at the second line, because the first one is `Option Explicit` :) – Vityata Jan 12 '17 at 11:53
Another comment - how do you access the text in the module? For the TODO: ? – Vityata Jan 12 '17 at 11:57
1

Take a look at the members of the CodeModule class in Object Explorer. For example: `Property Lines(StartLine As Long, Count As Long) As String` – ThunderFrame Jan 12 '17 at 11:59
Actually I found out - is it something like `comp.CodeModule.Lines(1, 4)` – Vityata Jan 12 '17 at 11:59
This is cute and well within the 30 minutes. Have an upvote! (I'd replace `Next proj` with `Next` though since .NET doesn't support the former.) – Bathsheba Jan 12 '17 at 12:00
If you're interested in a raft of new features for the VBE, you should check out the open-source project [Rubberduck VBA](https://github.com/rubberduck-vba/Rubberduck) (I'm a contributor) – ThunderFrame Jan 12 '17 at 12:01
@ThunderFrame, so far I am an observer there :). However, I have modified your code to make it with early binding, still debuging it -> http://pastebin.com/Cwginmq4 – Vityata Jan 12 '17 at 12:19
take it into [chat](http://chat.stackexchange.com/rooms/14929/vba-rubberducking)? – ThunderFrame Jan 12 '17 at 12:22
@Vityata what about the part of "Trust the VBA Module"...? – Shai Rado Jan 12 '17 at 17:41
@ShaiRado Which part is this one? I don't get it? – Vityata Jan 12 '17 at 17:49
can you run this code without marking **V** the "Trust access to the VBA project object model" in Excel's menu "Macro Security" ? – Shai Rado Jan 12 '17 at 18:05
I see... you are speaking about `VBProj.Protection = vbext_pp_none`. Probably I would add it later. – Vityata Jan 12 '17 at 18:17
The `VBProj.Protection = vbext_pp_none` only relates to projects that aren't password-protected or otherwise unviewable. Unless you have trusted access to the VBA project object model, *any* attempt to access `Application.VBE` or `Workbook.vbProject` will fail, and *none* of the code would work. You'd then have to do the editing by hand, or use a 3rd party COM add-in (like maybe "VBA Code Cleaner") that *does* have access to the VBA project object model. – ThunderFrame Jan 12 '17 at 19:41

Shai Rado · Answer 2 · 2017-01-12T12:42:31.083

Took me a little longer to modify exisitng code of mine, you could try the following code below to repalce all places of "Option Explicit" with "Option Private Module".

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

Seems interesting, thanks. One small remarks - I need the `Option Private Module` in every module, just below the "Option Explicit", my initial idea was to replace "Option Explicit" with "Option Explicit ^p Option Private Module". — Vityata, Jan 12 '17 at 12:22
@ShaiRado you're using `Like` without a wildcard. What happens if there is a comment on the same line, or a line continuation before, between and/or after `Option` and `Explicit`? — ThunderFrame, Jan 12 '17 at 12:32
And if you're looping over lines, you'll save a great deal of processing by only looping until `CountOfDeclarationLines` — ThunderFrame, Jan 12 '17 at 12:33

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

2 Answers2

Linked