Public Function test()
Dim xlApp As Excel.Application, xlBook As Excel.Workbook, vbCmp As VBComponent
'нужна ссылка на Library VBIDE
'C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
'Microsoft Visual Basic for Applications Extensibility 5.3

  Set xlApp = New Excel.Application
  Set xlBook = xlApp.Workbooks.Add()
  Set vbCmp = xlBook.VBProject.VBComponents.Add(vbext_ct_StdModule)
  vbCmp.Name = "module_test"
  With vbCmp.CodeModule
    'методы :
    '.AddFromFile ...
    '.AddFromString  "Sub New" 
    '.CreateEventProc ...
    '.DeleteLines ...
    '.InsertLines ...
    '.ReplaceLine ...
  End With
  xlApp.Visible = True
  
  Set vbCmp = Nothing: Set xlBook = Nothing: Set xlApp = Nothing
End Function