Can one VB macro write another macro?

G

Gizmo63

Here's the challenge.
The user's workbooks all rely on macros.
To simplify updating of macros and bug fixing all the macros are kept in a
central storage file.

"workbook A" has it's buttons linked to "Macro Storage" as does "workbook B"
etc etc
So when the user hits a button in "workbook A" it opens "Macro Storage" as
read-only and executes the macro.

The mods that I'm making require some coding to be added to the users
workbook. (Essentially a run-on-load subroutine).

When the mod runs from "Macro Storage" is there any way to create a
"Private Sub Workbook_Open()" in the users workbook and insert the code?

Thanks in advance.

Giz
 
B

Bernie Deitrick

Giz,

You can do it with code, but I often have Excel die when doing so.

That said, try the macro below, with the activeworkbook being workbook A or B - I wasn't sure which
needs the new sub. And note that, as written, the code will remove any code in the ThisWorkbook
object's codemodule....

Also, this code requires a reference to MS VBA Extensibility.

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim myBook As Workbook
Dim myVBA As VBIDE.VBComponent
Set myBook = ActiveWorkbook
Set myVBA = myBook.VBProject.VBComponents(myBook.CodeName)

On Error GoTo NoLines
With myVBA.CodeModule
.DeleteLines 1, .CountOfLines
End With

NoLines:

With myVBA.CodeModule
.InsertLines 1, "Private Sub Workbook_Open()" & Chr(10) & _
"Msgbox ""Hi there from your new macro""" & Chr(10) & _
"End Sub"
End With

End Sub
 
Top