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.

ArrowMain

 

ArrowRaw

 

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.

ArrowOutput

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 info@exceltip.com

Users are saying about us...

  1. Pappu Ansari Alam

    Greetings
    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.

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube