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 info@exceltip.com
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.
I've attempted to use the value 0, however ModuleComponent still takes on the value of Nothing.
Can you let me know what is in Cell D12 (is there a download to this example)?
Cint(Range(“D12?).Value)
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.
Hello all, i just tried this code and receiving this error: ” User_defined type not defined” can you pleas help me?
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.
https://www.exceltip.com/applications-word-outlook-in-vba/copy-worksheet-information-to-word-using-vba-in-microsoft-excel.html
Kindly go through and let us know if that too works and meets your requirement.
Regards,
Team Excel Tip
Thank you again for your quick response and help . Really appreciated
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.
Is it possible to view your code without the google ad obliterating the right side?
It's OK. I found I was able to select it all and copy it anyway. Thanks