Create a new module using VBA in Microsoft Excel

In this article, we will create a macro to insert a new module in an Excel workbook.

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





As one can see in the screenshot, we only have one module in the workbook. In this example, we will add class module to the workbook.


Logic explanation

In this article, we have created two macros, “CreateNewModule” and “CallingProcedure”.

CreateNewModule” macro is used to add a new module, depending on the provided input.

CallingProcedure” macro is used to provide the input and call the main module.

Code explanation

Set ModuleComponent = Wbook.VBProject.VBComponents.Add(ModuleTypeIndex)

The above code is used to add a new module in the VBA project.

ModuleComponent.Name = NewModuleName

The above code is used to rename the inserted component.

ModuleTypeConst = Cint(Range("D12").Value)

The above code is used to get integer value from cell D12.

ModuleName = Sheet1.TextBox2.Value

The above code is used to get value from text box.


Please follow below for the code

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


If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at


  1. Pappu Ansari Alam

    i have some entry like. income and outcome
    i wanna add all income or outcome
    i have used sumif but when i hide row data should be reduce but there is nothing change
    when i use subtotal its sum add total of income and outcome.

    • Hi Michael,

      We have updated the code here only. What you need to do is to only add the references by going..

      Tools-->References-->Check "Microsoft Visual Basic for Applications Extensibility"

      Happy Learning,
      Site Admin

      • Thank you very much for your quick response . I have tried your suggestion and it works , Great !!!
        Would you be able to to help me for the following : i have a text in Excel worksheet that i want to copy and paste into Excel VB Standard Module . Currently I can do it using "Cntl C" to copy and then Cntl V " to paste into Excel VB Module . Can you please suggest some other method of how it can be done without pushing the above buttons .

        • Hi Michael,

          Glad it worked for you! 🙂

          Regarding your another query, we have already reverted you on the following link.

          Kindly go through and let us know if that too works and meets your requirement.

          Team Excel Tip

          • Your most welcome Michael. 🙂

            Keep visiting us to learn more crazy stuff. Also, we are now available on YouTube too. You can watch step by step video tutorials there as well.

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.