Adding A Procedure To A Module 

The procedure below will add a new procedure called "MyNewProcedure" 
to the module named "NewModule" in ThisWorkbook. 

Sub AddProcedure()

Dim VBCodeMod As CodeModule
Dim LineNum As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
    LineNum = .CountOfLines +  1 
    .InsertLines LineNum, _
"Sub MyNewProcedure()" & Chr( 13 ) & _
" Msgbox ""Here is the new procedure"" " & Chr( 13 ) & _
"End Sub"
End With

Application.Run "MyNewProcedure"

End Sub 

Pay attention to the way in which the .InsertLines method is called.  
The entire procedure is passed as one argument 
 -- a string with embedded Chr(13) characters for the line breaks.  The code statement 
 

Application.Run "MyNewProcedure" 

will run the new procedure.  
You must use Application.Run rather than calling the procedure directly in order to prevent compile-time errors.  
This method will work only if you are adding code to another code module.  
If you are adding code a the same code module, you must use an Application.OnTime method, 
so that control is returned to Excel, and the module can be recompiled and reloaded.  
Using Application.OnTime may have some synchronizations problems, 
so you should avoid calling a procedure that you've just added 
to the same code module without allowing all VBA procedures to come to an end.   

Application.OnTime Now,"NewProcedureName"