自动将“选项专用模块"添加到VBA中的所有模块 [英] Automatically add `Option Private Module` to all modules in VBA

问题描述

是否可以自动将Option Private Module添加到VBA中的所有模块?

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

当我们在Extras> Option>编辑器中选中复选框以声明变量时，是否像Option explicit的自动添加?

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.

子问题: 如果必须将Option Private Module添加到10个应用程序的所有模块中，该怎么办?

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

起初，我在考虑使用简单的Replace并将Option Explicit替换为Option Explicit ^p Option Private Module，但是它也会在类中替换它，因此我必须从那里删除它.

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.

想在这里节省30分钟吗?

Ideas to save some 30 minutes here?

推荐答案

这应该可以帮助您解决大部分问题，并且此示例适用于所有未受保护的开放项目.如果您需要修改受保护的项目，只需先取消保护它们即可.

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

从OP(@vityata) 我已决定在我的答案中添加更新后的答案(希望您不要介意).它具有后期绑定功能，因此不需要任何库:

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

这篇关于自动将“选项专用模块"添加到VBA中的所有模块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！