How can I create a Code Module in VBA

Question

I want to create a code module with VBA. When I already have a code module I know that I can set it using:

Set cdmdl = wbk.VBProject.VBComponents(codeModuleName).CodeModule

But if the code module does not exist, how can I create it?

I've tried a few lines like:

Set cdmdl = new.wbk.VBProject.VBComponents(codeModuleName).CodeModule
Set cdmdl = create.wbk.VBProject.VBComponents(codeModuleName).CodeModule

But they haven't worked. I've also Googled, but this doesn't seem like a popular topic.

I want to update some Excel files. They already have their VBA code updated. But they don't have the buttons that activate said VBA scripts. — user1283776
– user1283776, Commented Aug 28, 2013 at 22:45
Then shouldn't you want to just copy the buttons from the source worksheet to the destination? — Chel
– Chel, Commented Aug 28, 2013 at 22:47

Mathieu Guindon · Accepted Answer · 2013-08-29 01:26:26Z

6

This worked for me:

Public Function CreateModule(xlwb As Workbook) As VBComponent
    Dim module As VBComponent
    Set module = xlwb.VBProject.VBComponents.Add(vbext_ct_StdModule)
    module.Name = "MyModule"
    module.CodeModule.AddFromString "public sub test()" & vbNewLine & _
                                    "    'dosomething" & vbNewLine & _
                                    "end sub"
    Set CreateModule = module
End Function

You can also AddFromFile if you have a .bas file you've exported and you want to load into a workbook.

edited Aug 29, 2013 at 1:26

answered Aug 29, 2013 at 0:30

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I create a Code Module in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related