Create a new module using VBA in Microsoft Excel

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


CreateNewModule ActiveWorkbook, 1, "TestModule"

2 thoughts on “Create a new module using VBA in Microsoft Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

eight − = 1

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>