How To Create a new module using VBA in Microsoft Excel?

In this tutorial, we will guide you on how to ** create a new module using VBA ** in Microsoft Excel. Let’s get them below!! Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

• Firstly, you need to create a macro to insert a new module in an Excel workbook.

Sample data

• In the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic

• Now, you need to copy and paste the code given below.

Option Explicit
Sub CreateNewModule(ByVal ModuleTypeIndex As Integer, ByVal NewModuleName As String)
'Declaring variables
Dim ModuleComponent As VBComponent
Dim WBook As Workbook
'Creating object of active workbook
Set WBook = ActiveWorkbook
Set ModuleComponent = Nothing 
On Error Resume Next
'Adding new module component
Set ModuleComponent = WBook.VBProject.VBComponents.Add(ModuleTypeIndex)
If Not ModuleComponent Is Nothing Then
    'Renaming the new module
    ModuleComponent.Name = NewModuleName
End If
On Error GoTo 0
Set ModuleComponent = Nothing
End Sub
Sub CallingProcedure()
'Declare variables
Dim ModuleTypeConst As Integer
Dim ModuleName As String
'Getting value of module name and type of module
ModuleTypeConst = CInt(Range("D12").Value)
ModuleName = Sheet1.TextBox2.Value
'Calling CreateNewModule
CreateNewModule ModuleTypeConst, ModuleName
End Sub

• You have to save the code by selecting it and then close the window.

Save the Code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• Then, you need to choose the Macros option in the Code section.

Choose Macro option

• Before running the macro , you need to specify the type and name of the module.

Enter the type and name of the module

• Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the Code

• Finally, after running the macro , you will receive output as class module is added to the workbook.

Wrap-Up

We hope that this tutorial gives you guidelines on how to create a new module using VBA in Microsoft Excel. ** ** Please leave a comment in case of any queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!

Keep Reading:

• How to Modify and Manage Multiple Cell Comments in Excel Office 365?
• Remove/Add Author Name in Comments in Excel 365
• How to Create User Defined Function through Excel VBA?
• Extract the Missing Values through Excel VBA
• How to use Loan Calculator through Excel Office VBA?