Create procedure using VBA in a separate module

[chroniclesofdave]

After cobbling some code together, I stumbled upon this code that works well to Delete a procedure in a different module than the active one.

Private Sub CommandButton1_Click()    Sub DeleteProcedureFromModule()
        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")
        Set CodeMod = VBComp.CodeModule
    
        ProcName = "Button1"
        With CodeMod
            StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
            NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
            .DeleteLines StartLine:=StartLine, Count:=NumLines
        End With
    End Sub

However, at this point I am now trying to paper mache something together that will replace the procedure getting deleted. I found this around the internet:

[COLOR=#000080][FONT=&quot]Sub CreateProcedure()[/FONT][/COLOR]    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    Dim S As String
    Dim LineNum As Long
    
    [COLOR=#008000]' Use the next two lines to create a new module for the code[/COLOR]
    'Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    'VBComp.Name = "NewModule"
    [COLOR=#008000]' OR use the following line to use an existing module for the code[/COLOR]
    'Set VBComp = ThisWorkbook.VBProject.VBComponents("Module2")    
    
    Set CodeMod = VBComp.CodeModule
    LineNum = CodeMod.CountOfLines + 1
    S = "Sub HelloWorld()" & vbCrLf & _
        "    MsgBox ""Hello, World""" & vbCrLf & _
        "End Sub"
    CodeMod.InsertLines LineNum, S [COLOR=#000080][FONT=&quot]End Sub[/FONT][/COLOR]

but i am not sure how that will work to create:

Sub Button1()Dim obj As New DataObject
Dim txt As String


'Put some text inside a string variable
  txt = ""


'Make object's text equal above string variable
  obj.SetText txt


'Place DataObject's text into the Clipboard
  obj.PutInClipboard
End Sub

Any ideas/solutions would be appreciated.

 

[Logit]

.
Here is one way :

Option Explicit


Private mCodeMod As VBIDE.CodeModule

Sub AddModuleToProject()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
        VBComp.Name = "NewModule"
        
        AddProcedureToModule
        
    End Sub
Sub AddProcedureToModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character


        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("NewModule")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Public Sub SayHello()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With
    End Sub

Download workbook : https://www.amazon.com/clouddrive/share/SGh1XeNYlIipiROD3V3FICZTiA3cDj6s60GyXxXl6mX

Does this help ?

 

[chroniclesofdave]

It looks like i will need to modify it to add the procedure to a module that is already in existence, but i am sure i can play around with that and get to where i need it to go. i will reach back out if i get stuck!

.
Here is one way :

Option Explicit


Private mCodeMod As VBIDE.CodeModule

Sub AddModuleToProject()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
        VBComp.Name = "NewModule"
        
        AddProcedureToModule
        
    End Sub
Sub AddProcedureToModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE = """" ' one " character


        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("NewModule")
        Set CodeMod = VBComp.CodeModule
        
        With CodeMod
            LineNum = .CountOfLines + 1
            .InsertLines LineNum, "Public Sub SayHello()"
            LineNum = LineNum + 1
            .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
            LineNum = LineNum + 1
            .InsertLines LineNum, "End Sub"
        End With
    End Sub

Download workbook : https://www.amazon.com/clouddrive/share/SGh1XeNYlIipiROD3V3FICZTiA3cDj6s60GyXxXl6mX

Does this help ?

 

[Logit]

Great !