是否可以在Excel VBA中更改另一个模块中的Module的源代码 [英] Is it possible in Excel VBA to change the source code of Module in another Module

问题描述

1. 扫描ActiveSheet以获取一些预先的功能参数


2. 取我的源文本（字符串值，直接在VBA模块中直接编码），并使用从步骤1检索的参数替换指定区域


3. 生成包含计算文本的文件

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

 '源模块
公共函数GetSource（）As String 
 Dim s As String 
s =
 
s = s& 这是我的源文本的第一行& vbCrLf 
 s = s& 这是参数{par1}& vbCrLf 
 s = s& 这是另一回事& vbCrLf 
 
 GetSource = s 
结束函数
  

精细。我的问题是如果我想更新源文本，我现在必须手动在.xlam文件中。我想做的是在另一个模块中构建类似于 Sub ImportSource（）的东西，该模块将解析一些文件，以编程方式重建源模块，然后将该模块替换为我计算的源代码。我不知道的是/如何/如何用字符串变量中的某些值替换模块的源代码。

这就像元编程在最糟糕的和哲学上我反对这样做是为了我的核心。实际上，我想知道是否和如何做到。

解决方案

我意识到你真正想要的do是以您的VBA可访问的方式存储您的文档中的某些值，但这对于电子表格的用户是不可读的。按照Charles Williams的建议，将值存储在工作表中的命名范围中，并解决了您不希望用户访问值的担忧，您将不得不加密字符串...

这篇文章 - 但它有很多工作。

发现一个更短的例程 here 。它只是使用简单的XOR加密与硬编码密钥 - 但它应该足以大多数目的。关键将在您的宏中隐藏，因此无法窥视眼睛（嗯，不容易）。

现在您可以使用此功能，让我们称之为 encrypt（string）将您的字符串转换为电子表格中的值：

  range（mySecretCell）。value = encrypt（懒狗跳过狐狸）
  

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

 公共函数GetSource（）
 GetSource =范围（mySecretCell）。value）
结束函数
  

如果您使用 XOR 版本（第二个链接）， encrypt 和 decrypt 将是相同的功能...

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

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

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

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):

'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.

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.

解决方案

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...

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).

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

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 XOR version (second link), encrypt and decrypt would be the same function...

Does that meet your needs better?

这篇关于是否可以在Excel VBA中更改另一个模块中的Module的源代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！