CODEWITHSUNDEEP
vbaexcel
neelsg
neelsg

Reputation: 4842

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

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.

Upvotes: 4

Views: 20301

Answers (3)

Peter Albert
Peter Albert

Reputation: 17475

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

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

Upvotes: 5

Floris
Floris

Reputation: 46365

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?

Upvotes: 5

Floris
Floris

Reputation: 46365

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

Upvotes: 1

Related Questions