Tip Printed from ExcelTip.com
Create a new module using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions



With the macro below you can create a new module in a workbook: 
Sub CreateNewModule(ByVal wb As Workbook, _
    ByVal ModuleTypeIndex As Integer, ByVal NewModuleName As String)
' creates a new module of ModuleTypeIndex 
' (1=standard module, 2=userform, 3=class module) in wb
' renames the new module to NewModuleName (if possible)
Dim VBC As VBComponent, mti As Integer
    Set VBC = Nothing
    mti = 0
    Select Case ModuleTypeIndex
        Case 1: mti = vbext_ct_StdModule ' standard module
        Case 2: mti = vbext_ct_MSForm ' userform
        Case 3: mti = vbext_ct_ClassModule ' class module
    End Select
    If mti <> 0 Then
        On Error Resume Next
        Set VBC = wb.VBProject.VBComponents.Add(mti)
        If Not VBC Is Nothing Then
            If NewModuleName <> "" Then
                VBC.Name = NewModuleName
            End If
        End If
        On Error GoTo 0
        Set VBC = Nothing
    End If
End Sub

Example: 
CreateNewModule ActiveWorkbook, 1, "TestModule"