vba - Import lines of code

Question

Can we read scripts or lines of code to a module in vba? Like we have the include function in php.

For example:

We store this in Excel somewhere and call the range as xyz

line 1 of code
line 2 of code
line 3 of code

Then while running a macro we call this like

Sub my_macro()
  xyz
End Sub

Basically I want to run a few lines of code repetitively but don't want to create another macro and pass the parameters.

Answer 1

This can be done using the Microsoft Visual Basic for Applications Extensibility 5.3 (VBIDE) library. There's some great examples at CPearson.com. I typically use this to insert snippets of code while I'm developing. I would personally be uncomfortable executing code stored in an excel sheet, but I tested this and it does work.

My worksheet:

  A
1 MsgBox "I'm a test."
2 MsgBox "So am I."

I set up an empty subroutine that we will then insert into from the excel sheet.

Private Sub ProcToModify()

End Sub

And the subroutine that will actually insert the code into ProcToModify:

Sub ModifyProcedure()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim StartLine As Long
        Dim NumLines As Long
        Dim ProcName As String

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1") ' specify module to modify
        Set CodeMod = VBComp.CodeModule

        Dim ws As Worksheet
        Dim rng As Range
        Dim cell As Range

        Set ws = ThisWorkbook.ActiveSheet 'change this accordingly
        Set rng = ws.Range("A1:A2") 'and this

        For Each cell In rng
            ProcName = "ProcToModify"
            With CodeMod
                StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
                NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
                .InsertLines StartLine + NumLines - 2, cell.Value 'insert each line at the end of the procedure to get them in the correct order.
            End With
        Next cell
End Sub

Called at runtime like this:

Public Sub main()
    ModifyProcedure
    ProcToModify
End Sub

One Big Gotchya: Before running this code, you need to go to Excel>>File>>Options>>Trust Center>>Trust Center Settings>>Macro Settings and check the "Trust access to the VBA project object model".

I would imagine that's because allowing access to the project object is a fairly concerning security risk.

From the cpearson.com site I linked to earlier:

CAUTION: Many VBA-based computer viruses propagate themselves by creating and/or modifying VBA code. Therefore, many virus scanners may automatically and without warning or confirmation delete modules that reference the VBProject object, causing a permanent and irretrievable loss of code. Consult the documentation for your anti-virus software for details.