Is it possible in Excel VBA to change the source code of a module in another module
I have an Excel.xlam file that adds a button to the ribbon to do the following:
- ActiveSheet scan for some preset options
- Take the original text (string value hard-coded directly into the VBA module) and replace the selected areas with the parameters you got from step 1
- Generate a file containing the computed text
I keep the original text this way because it can be password protected and I don't have to drag and drop another file wherever the .xlam file is. The original text is saved in a separate module called "Source", which looks something like this (thanks to VBA for missing the 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 that if I want to update the original, I now have to manually do it in the .xlam file. What I would like to do is build something like Sub ImportSource()
in another module that will parse some file, programmatically rebuild the Source module, and then replace that module with my calculated source code. I don't know if there is / how to replace the source code of the module with some value in the string variable.
It's like metaprogramming in its worst and most philosophical sense, I am against it, right down to my core. In practice, however, I would like to know if and how to do this.
source to share
Now I understand that you really want to store some values in your document in a way that is accessible to your VBA, but that is not readable to the spreadsheet user. Following Charles Williams' suggestion for storing the value in a named range in the sheet, and noting that you don't want the user to have access to the values, you would need to encrypt the string ...
The "correct way" for this is described in this article - but it is quite a lot of work.
A shorter procedure is found here . It just uses simple hard-keyed XOR encryption - but that should be sufficient for "most purposes". The key will be "hidden" in your macro and therefore not visible to prying eyes (well, not easy).
Now you can use this function, call it encrypt(string)
to convert your string to 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 are using version XOR
(second link) encrypt
and decrypt
will be the same function ...
Does this suit your needs?
source to share
As @brettdj already pointed out with a link to cpearson.com/excel/vbe.aspx , you can change your VBA code programmatically with the VBA Extensibility library! To use it, select the library in the editor VBA Tools-> References. Note that you also need to change the options in your Trust Center and select: Excel Options-> Trust Center-> Trust Center Settings-> Macro Options-> Trust Access to VBA Project Object Model
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
source to share
You can "export" and "import" .bas files programmatically. In order to do what you ask, this must be the approach. I don't find it possible to modify the code in memory. See in this article
source to share