Create a new module
2000-02-05 VBE 0 187
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) ' requires a reference to the Microsoft Visual Basic Extensibility library ' 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 SubExample:
CreateNewModule ActiveWorkbook, 1, "TestModule"