vba - 导入代码行

Question

我们可以将脚本或代码行读取到 vba 中的模块吗？ ？就像我们在 php 中有 include 函数一样.

例如:

我们将其存储在 Excel 中的某处并将范围称为 xyz

line 1 of code
line 2 of code
line 3 of code

然后在运行宏时我们称其为

Sub my_macro()
  xyz
End Sub

基本上我想重复运行几行代码，但不想创建另一个宏并传递参数。

Answer 1

这可以使用 Microsoft Visual Basic for Applications Extensibility 5.3 (VBIDE) 库来完成。有一些 great examples at CPearson.com .我通常在开发时使用它来插入代码片段。我个人不太愿意执行存储在 Excel 工作表中的代码，但我对此进行了测试，它确实有效。

我的工作表:

  A
1 MsgBox "I'm a test."
2 MsgBox "So am I."

我设置了一个空的子例程，然后我们将从 Excel 工作表中插入该子例程。

Private Sub ProcToModify()

End Sub

以及实际将代码插入 ProcToModify 的子例程:

Sub ModifyProcedure()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim StartLine As Long
        Dim NumLines As Long
        Dim ProcName As String

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1") ' specify module to modify
        Set CodeMod = VBComp.CodeModule

        Dim ws As Worksheet
        Dim rng As Range
        Dim cell As Range

        Set ws = ThisWorkbook.ActiveSheet 'change this accordingly
        Set rng = ws.Range("A1:A2") 'and this

        For Each cell In rng
            ProcName = "ProcToModify"
            With CodeMod
                StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
                NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
                .InsertLines StartLine + NumLines - 2, cell.Value 'insert each line at the end of the procedure to get them in the correct order.
            End With
        Next cell
End Sub

像这样在运行时调用:

Public Sub main()
    ModifyProcedure
    ProcToModify
End Sub

一个大问题:在运行此代码之前，您需要转到 Excel>>文件>>选项>>信任中心>>信任中心设置>>宏设置并勾选“信任对 VBA 项目对象模型的访问”。

我想那是因为允许访问项目对象是一个相当令人担忧的安全风险。

来 self 之前链接的 cpearson.com 网站:

CAUTION: Many VBA-based computer viruses propagate themselves by creating and/or modifying VBA code. Therefore, many virus scanners may automatically and without warning or confirmation delete modules that reference the VBProject object, causing a permanent and irretrievable loss of code. Consult the documentation for your anti-virus software for details.