是否可以在 Excel VBA 中更改另一个模块中模块的源代码

Is it possible in Excel VBA to change the source code of Module in another Module

提问by neelsg

I have an Excel .xlam file that adds a button in the ribbon to do the following:

我有一个 Excel .xlam 文件，它在功能区中添加了一个按钮来执行以下操作：

1. Scan the ActiveSheet for some pre-set parameters
2. Take my source text (a string value, hard coded directly in a VBA Module) and replace designated areas with the parameters retrieved from step 1
3. Generate a file containing the calculated text

1. 扫描 ActiveSheet 以获取一些预设参数
2. 获取我的源文本（一个字符串值，直接在 VBA 模块中硬编码）并用从步骤 1 中检索到的参数替换指定区域
3. 生成包含计算文本的文件

I save the source text this way because it can be password protected and I don't need to drag another file around everywhere that the .xlam file goes. The source text is saved in a separate module called "Source" that looks something like this (Thanks VBA for not having Heredocs):

我以这种方式保存源文本，因为它可以受密码保护，而且我不需要在 .xlam 文件所在的任何地方拖动另一个文件。源文本保存在一个名为“Source”的单独模块中，看起来像这样（感谢 VBA 没有 Heredocs）：

'Source Module
Public Function GetSource() As String
    Dim s As String
    s = ""

    s = s & "This is the first line of my source text" & vbCrLf
    s = s & "This is a parameter {par1}" & vbCrLf
    s = s & "This is another line" & vbCrLf

    GetSource = s
End Function

The function works fine. My problem is if I want to update the source text, I now have to manually do that in the .xlam file. What I would like to do is build something like a Sub ImportSource()in another module that will parse some file, rebuild the "Source" Module programatically, then replace that Module with my calculated source code. What I don't know is if/how to replace the source code of a module with some value in a string variable.

该功能工作正常。我的问题是如果我想更新源文本，我现在必须在 .xlam 文件中手动执行此操作。我想做的是Sub ImportSource()在另一个模块中构建类似 a 的东西，该模块将解析某个文件，以编程方式重建“源”模块，然后用我计算的源代码替换该模块。我不知道是否/如何用字符串变量中的某个值替换模块的源代码。

It's like metaprogramming at its very worst and philosophically I'm against doing this down to my very core. Practically, however, I would like to know if and how to do it.

这就像最糟糕的元编程，从哲学上讲，我反对深入到我的核心。然而，实际上，我想知道是否以及如何做到这一点。

回答by Floris

I realize now that what you really want to do is store some values in your document in a way that is accessible to your VBA, but that is not readable to a user of the spreadsheet. Following Charles Williams's suggestion to store the value in a named range in a worksheet, and addressing your concern that you don't want the user to have access to the values, you would have to encrypt the string...

我现在意识到，您真正想要做的是以 VBA 可访问的方式在文档中存储一些值，但电子表格的用户无法读取这种方式。按照查尔斯威廉姆斯的建议将值存储在工作表中的命名范围内，并解决您不希望用户访问这些值的问题，您必须加密字符串......

The "proper way" to do this is described in this article- but it's quite a bit of work.

本文描述了执行此操作的“正确方法” - 但它需要做很多工作。

A much shorter routine is found here. It just uses simple XOR encryption with a hard coded key - but it should be enough for "most purposes". The key would be "hidden" in your macro, and therefore not accessible to prying eyes (well, not easily).

一个更短的程序找到这里。它只是使用带有硬编码密钥的简单 XOR 加密 - 但它应该足以满足“大多数目的”。密钥将“隐藏”在您的宏中，因此无法被窥探（好吧，不容易）。

Now you can use this function, let's call it encrypt(string), to convert your string to a value in the spreadsheet:

现在您可以使用这个函数，让我们称之为encrypt(string)，将您的字符串转换为电子表格中的值：

range("mySecretCell").value = encrypt("The lazy dog jumped over the fox")

and when you need to use it, you use

当你需要使用它时，你使用

Public Function GetSource()
    GetSource = decrypt(Range("mySecretCell").value)
End Function

If you use the XORversion (second link), encryptand decryptwould be the same function...

如果您使用该XOR版本（第二个链接），encrypt并且decrypt功能相同...

Does that meet your needs better?

这是否更好地满足您的需求？

回答by Peter Albert

As @brettdj already pointed out with his link to cpearson.com/excel/vbe.aspx , you can programmatically change to code of a VBA module using the VBA Extensibility library! To use it, select the library in the VBA editor Tools->References. Note that you need to also change the options in your Trust center and select: Excel Options->Trust Center->Trust Center Settings->Macro Settings->Trust access to the VBA project object model

正如@brettdj 已经通过他指向cpearson.com/excel/vbe.aspx 的链接指出的那样，您可以使用VBA 扩展库以编程方式更改为 VBA 模块的代码！要使用它，请在 VBA 编辑器Tools-> References 中选择库。请注意，您还需要更改信任中心中的选项并选择：Excel 选项->信任中心->信任中心设置->宏设置->信任访问 VBA 项目对象模型

Then something like the following code should do the job:

然后像下面的代码应该完成这项工作：

Private mCodeMod As VBIDE.CodeModule

Sub UpdateModule()
    Const cStrModuleName As String = "Source"

    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent

    Set VBProj = Workbooks("___YourWorkbook__").VBProject

    'Delete the module
    VBProj.VBComponents.Remove VBProj.VBComponents(cStrModuleName)

    'Add module
    Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
    VBComp.Name = cStrModuleName
    Set mCodeMod = VBComp.CodeModule

    'Add procedure header and start
    InsertLine "Public Function GetSource() As String"
    InsertLine "Dim s As String", 1
    InsertLine ""

    'Add text
    InsertText ThisWorkbook.Worksheets("Sourcetext") _
        .Range("___YourRange___")

    'Finalize procedure
    InsertLine "GetSource = s", 1
    InsertLine "End Function"

End Sub

Private Sub InsertLine(strLine As String, _
    Optional IndentationLevel As Integer = 0)
    mCodeMod.InsertLines _
        mCodeMod.CountOfLines + 1, _
        Space(IndentationLevel * 4) & strLine
End Sub

Private Sub InsertText(rngSource As Range)
    Dim rng As Range
    Dim strCell As String, strText As String
    Dim i As Integer

    Const cLineLength = 60
    For Each rng In rngSource.Cells
        strCell = rng.Value
        For i = 0 To Len(strCell) \ cLineLength
            strText = Mid(strCell, i * cLineLength, cLineLength)
            strText = Replace(strText, """", """""")
            InsertLine "s = s & """ & strText & """", 1
        Next i
    Next rng
End Sub

回答by Floris

You can "export" and "import" .bas files programmatically. To do what you are asking, that would have to be the approach. I don't believe it's possible to modify the code in memory. See this article

您可以以编程方式“导出”和“导入”.bas 文件。要做到你所要求的，那将是方法。我不相信可以修改内存中的代码。看这篇文章