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

Question

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.

Answer 1

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?