PDA

View Full Version : Create New Macro Using VBA



sam314159
01-26-2010, 05:49 AM
I have some VBA code written that will create a new workbook and I would like for that VBA code to also create a new macro within that NEW workbook after it creates it.

Is that possible?

Thanks in advance for the help guys.

Bob Phillips
01-26-2010, 06:22 AM
Const vbext_ct_StdModule As Long = 1
Dim wb As Workbook
Dim VBProj As Object
Dim VBComp As Object
Dim LineNum As Long

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")

Set wb = Workbooks.Add

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "MyModule"

With VBComp.CodeModule
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Public Sub MyMacro()"
.InsertLines LineNum + 1, " MsgBox ""Hello World"""
.InsertLines LineNum + 2, "End Sub"
End With

sam314159
01-26-2010, 06:49 AM
Thanks xld, that worked perfectly.

Jan Karel Pieterse
01-26-2010, 07:06 AM
Note that it will not work unless the user has set allow access to visual basic projects and that many virus scanners may give false positives on VBA that uses the VBA Extensibility library.

sam314159
01-26-2010, 02:33 PM
Note that it will not work unless the user has set allow access to visual basic projects and that many virus scanners may give false positives on VBA that uses the VBA Extensibility library.

Thanks a lot for bringing that up Jan.

1. Does "allow access to visual basic projects" mean clicking on "Enable" when the macro warning pops up when the user opens a workbook that
contains a macro?

2. It doesn't seem like the security system, Symantec, I have installed on my machine is having an issue with it. Considering that everyone in our company has the same security system, is it safe to assume that it will not cause an issue for them either?

Bob Phillips
01-26-2010, 03:08 PM
1. Does "allow access to visual basic projects" mean clicking on "Enable" when the macro warning pops up when the user opens a workbook that
contains a macro?

No, its is an application setting, Tools>Macro>Security...>Trusted PUblishers, 'Trust Access To Visual Basic Project' checkbox. You must have it set, otherwise it would not have worked.

sam314159
01-26-2010, 03:13 PM
No, its is an application setting, Tools>Macro>Security...>Trusted PUblishers, 'Trust Access To Visual Basic Project' checkbox. You must have it set, otherwise it would not have worked.

Got it, thanks.

For some reason, that option is checked and the checkbox is disabled on all our machines. I don't know why, but it's good for me.

sam314159
02-01-2010, 02:47 PM
I have a followup question to the original question and I hope it is ok to use my same thread instead of creating a new one.

Is it possible to modify the code that xld posted above to insert VBA code into the 'ThisWorkbook' object instead of to a new module?

Bob Phillips
02-01-2010, 02:59 PM
This creates a workbook_open event procedure



Const vbext_ct_StdModule As Long = 1
Dim wb As Workbook
Dim VBProj As Object
Dim VBComp As Object
Dim LineNum As Long

Set wb = Workbooks.Add

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("ThisWorkbook")
With VBComp.CodeModule

LineNum = .CreateEventProc("Open", "Workbook")
.InsertLines LineNum + 1, " MsgBox ""Hello World"""
End With