Import lines of code

Question

Can we read scripts or lines of code in a module in vba ? Just as we include the include function in php .

For instance:

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

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

Then, during the macro run, we call it as

 Sub my_macro() xyz End Sub 

Basically, I want to repeat a few lines of code, but I don’t want to create another macro and pass parameters.

Answer 1

This can be done using the Microsoft Visual Basic Library for Application Extension 5.3 (VBIDE). There are great examples on CPearson.com . I usually use this to embed code snippets during development. I personally would be uncomfortable executing the code stored on the excel sheet, but I tested this and it works.

My worksheet:

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

I installed an empty routine, which we then insert into the excel sheet.

 Private Sub ProcToModify() End Sub 

And a routine that actually inserts 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 as follows:

 Public Sub main() ModifyProcedure ProcToModify End Sub 

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

I would suggest that, since allowing access to the project object is rightly associated with a security risk.

From cpearson.com, which I contacted earlier:

CAUTION: Many VBA-based computer viruses spread the creation and / or modification of VBA code. Therefore, many anti-virus scanners can automatically and without warning or confirmation remove modules that reference the VBProject object, causing permanent and irrecoverable code loss. See your antivirus software documentation for details.