CODEWITHSUNDEEP
excelvba
user1283776
user1283776

Reputation: 21764

How can I create a Code Module in VBA

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.

Upvotes: 5

Views: 11941

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

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.

Upvotes: 6

Related Questions