VBA Tutorial CodeAdd Module from Module
Adding modules from modules is useful in VBA for two reasons.
1. The more you know, the more flexibility you have when working on large projects.
2. You can add lines, remove lines, remove modules, and count modules with VBA.
Steps:
1. Enable Microsoft Visual Basic for Applications Extensibility 5.3 under the Visual Basic Editor's Tools –> References. (Scroll down; it's in alphabetical order.)
Video to watch me do it:
Code to Add Module from Module:
Sub Mod1()
‘add Module
Dim proj As VBIDE.VBProject
Dim comp As VBIDE.VBComponent
Set proj = ActiveWorkbook.VBProject
‘below adds the Module 2
Set comp = proj.VBComponents.Add(vbext_ct_StdModule)
‘below renames the Module
comp.Name = "Module2"
Set codemod = comp.CodeModule
With codemod
lineNum = .CountOfLines + 1
.InsertLines lineNum, "Sub Mod2()"
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
Excel VBA Filter
