Скачать или смотреть VBA Create Module from Module

Create a module from a module with vba in Excel 2007, 2010, and 2013.

Enable Visual Basic for Applications Extensibility 5.3 under Tools - References in the Visual Basic Editor to reference module objects.

Code:

Dim proj As VBIDE.VBProject
Dim comp As VBIDE.VBComponent

Set proj = ActiveWorkbook.VBProject
Set comp = proj.VBComponents.Add(vbext_ct_StdModule)
comp.Name = "Module3"

Set codemod = comp.CodeModule

With codemod
lineNum = .CountOfLines + 1
.InsertLines lineNum, "Public Sub Mod3()"
lineNum = lineNum + 1
.InsertLines lineNum, "Range(""A1"").Value = 1"
lineNum = lineNum + 1
.InsertLines lineNum, "erow = activesheet.cells(Rows.count,1).end(xlup).row"
lineNum = lineNum + 1
.InsertLines lineNum, "Range(""B1"").Select"
lineNum = lineNum + 1
.InsertLines lineNum, "ActiveCell.FormulaR1C1 = ""=If(RC[-1]=1,""""Yes"""",""""No"""")"""
lineNum = lineNum + 1
.InsertLines lineNum, "Application.OnTime Now + TimeValue(""00:00:01""), ""counter"""
lineNum = lineNum + 1
.InsertLines lineNum, "End Sub"
End With

End Sub