How to add New Module through coding

[kris]

I am trying the following but getting error messages, please help

Sub AddModule()
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "NewModule"
Application.Visible = True
End Sub

 

[broro183]

hi Kris,

Your code looks like it is the same as some on Chip Pearson's below page:
http://www.cpearson.com/excel/vbe.aspx

At the very top of this page Chip states "In order to use the code on this
page in your projects, you must change two settings....", check it out &
hopefully it will solve your problem.
Note, Chip also has a red warning box discussing virus scanners & how they
may delete modules that reference a VBProject object.

hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Bob Phillips]

Sub AddModule()
Const vbext_ct_StdModule As Long = 1
Dim VBComp As Object
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "NewModule"
Application.Visible = True
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)