导入代码行 [英] Import lines of code

问题描述

我们可以在？就像我们在 php 的问题中的include功能一样。

Can we read scripts or lines of code to a module in vba? Like we have the include function in php.

例如：

我们将其存储在Excel中，并将范围称为xyz

We store this in Excel somewhere and call the range as xyz

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

然后运行一个宏时，我们称之为

Then while running a macro we call this like

Sub my_macro()
  xyz
End Sub

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

Basically I want to run a few lines of code repetitively but don't want to create another macro and pass the parameters.

推荐答案

这可以使用Microsoft Visual Basic应用程序可扩展性5.3（VBIDE）库。在CPearson.com有一些很好的例子。我在开发过程中通常会使用它来插入代码段。我会亲自感到执行代码存储在excel表中，但我测试了这个并且它的工作。

This can be done using the Microsoft Visual Basic for Applications Extensibility 5.3 (VBIDE) library. There's some great examples at CPearson.com. I typically use this to insert snippets of code while I'm developing. I would personally be uncomfortable executing code stored in an excel sheet, but I tested this and it does work.

我的工作表：

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

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

I set up an empty subroutine that we will then insert into from the excel sheet.

Private Sub ProcToModify()

End Sub

将实际将代码插入到 ProcToModify 中的子例程：

And the subroutine that will actually insert the code into 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

One Big Gotchya：
运行此代码之前，您需要转到Excel >>文件>>选项>>信任中心>>信任中心设置>>宏设置并检查信任访问VBA项目对象模型。

One Big Gotchya: Before running this code, you need to go to Excel>>File>>Options>>Trust Center>>Trust Center Settings>>Macro Settings and check the "Trust access to the VBA project object model".

我想象的是，因为允许访问项目对象是一个相当的安全风险。

I would imagine that's because allowing access to the project object is a fairly concerning security risk.

从cpearson.com网站链接到更早：

From the cpearson.com site I linked to earlier:

小心：许多基于VBA的计算机病毒通过
来创建和/或修改VBA代码。因此，许多病毒扫描程序可能会自动
，没有警告或确认删除模块，
引用VBProject对象，导致永久性且不可恢复的
代码丢失。有关详细信息，请参阅您的防病毒软件
的文档。

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.

这篇关于导入代码行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！