Can macro create another macro?

[VK]

Hi, all!
An macro from Personal.xls create a new sheet, now i need another macro
available in that new sheet. How do I do it, that macro create another
macro?
Reg, VK

[Bob Phillips]

If it is an event macro, use something like

Dim iStartAs Long

With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
.InsertLines iStart, _
"Dim ans" & vbCrLf & _
" ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
" If ans = vbNo Then Cancel = True"
End With

If it just another macro in Module1 say, then use

Dim iNext As Long

With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
iNext = .CountOfLines + 1
.InsertLines iNext, _
"Sub myProc()" & vbNewLine & _
" Msgbox ""Tesing new procedure"" " & vbNewLine & _
"End Sub"
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VK" <"myname"@example.invalid> wrote in message
news:3fm3h6F8dua6U1@news.dfncis.de...
> Hi, all!
> An macro from Personal.xls create a new sheet, now i need another macro
> available in that new sheet. How do I do it, that macro create another
> macro?
> Reg, VK

[VK]

> If it is an event macro, use something like
>
> Dim iStartAs Long
>
> With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
> iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
> .InsertLines iStart, _
> "Dim ans" & vbCrLf & _
> " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
> " If ans = vbNo Then Cancel = True"
> End With
>
> If it just another macro in Module1 say, then use
>
> Dim iNext As Long
>
> With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
> iNext = .CountOfLines + 1
> .InsertLines iNext, _
> "Sub myProc()" & vbNewLine & _
> " Msgbox ""Tesing new procedure"" " & vbNewLine & _
> "End Sub"
> End With
>

Thanks! I'll try it out
Reg. VK

[Jef Gorbach]

"VK" <"myname"@example.invalid> wrote in message
news:3fm4vgF8gch8U1@news.dfncis.de...
> > If it is an event macro, use something like
> >
> > Dim iStartAs Long
> >
> > With
ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
> > iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
> > .InsertLines iStart, _
> > "Dim ans" & vbCrLf & _
> > " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
> > " If ans = vbNo Then Cancel = True"
> > End With
> >
> > If it just another macro in Module1 say, then use
> >
> > Dim iNext As Long
> >
> > With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
> > iNext = .CountOfLines + 1
> > .InsertLines iNext, _
> > "Sub myProc()" & vbNewLine & _
> > " Msgbox ""Tesing new procedure"" " & vbNewLine & _
> > "End Sub"
> > End With
> >
>
> Thanks! I'll try it out
> Reg. VK

Would like todo same with about 5 multi-page routines, so wonder if instead
of embedding the new code in the copying macro (per above), if its possible
to copy an existing macro between files, say from personal.xls to book1.xls
??

ie:

Sub test()
Workbooks("Book1").VBProject.VBComponents.Import _
(Workbooks("Personal").VBProject.vbcompoents("aging_report").codemodule)
End Sub

seems logical, but returns "runtime error 438: object does not support this
property or method"

[Bob Phillips]

You cannot import a module from another workbook, you must import from a
file. So you export from Personal.xls to a file, and then import that file
into another book.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jef Gorbach" <Jefgorbach@aol.com> wrote in message
news:OCiqjbhYFHA.2796@TK2MSFTNGP09.phx.gbl...
>
> "VK" <"myname"@example.invalid> wrote in message
> news:3fm4vgF8gch8U1@news.dfncis.de...
> > > If it is an event macro, use something like
> > >
> > > Dim iStartAs Long
> > >
> > > With
> ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
> > > iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
> > > .InsertLines iStart, _
> > > "Dim ans" & vbCrLf & _
> > > " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
> > > " If ans = vbNo Then Cancel = True"
> > > End With
> > >
> > > If it just another macro in Module1 say, then use
> > >
> > > Dim iNext As Long
> > >
> > > With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
> > > iNext = .CountOfLines + 1
> > > .InsertLines iNext, _
> > > "Sub myProc()" & vbNewLine & _
> > > " Msgbox ""Tesing new procedure"" " & vbNewLine & _
> > > "End Sub"
> > > End With
> > >
> >
> > Thanks! I'll try it out
> > Reg. VK
>
> Would like todo same with about 5 multi-page routines, so wonder if
instead
> of embedding the new code in the copying macro (per above), if its
possible
> to copy an existing macro between files, say from personal.xls to
book1.xls
> ??
>
> ie:
>
> Sub test()
> Workbooks("Book1").VBProject.VBComponents.Import _
>
(Workbooks("Personal").VBProject.vbcompoents("aging_report").codemodule)
> End Sub
>
> seems logical, but returns "runtime error 438: object does not support
this
> property or method"
>
>
>
>

[VK]

Bob Phillips wrote:
> If it is an event macro, use something like
>
> Dim iStartAs Long
>
> With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
> iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
> .InsertLines iStart, _
> "Dim ans" & vbCrLf & _
> " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
> " If ans = vbNo Then Cancel = True"
> End With
>
> If it just another macro in Module1 say, then use
>
> Dim iNext As Long
>
> With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
> iNext = .CountOfLines + 1
> .InsertLines iNext, _
> "Sub myProc()" & vbNewLine & _
> " Msgbox ""Tesing new procedure"" " & vbNewLine & _
> "End Sub"
> End With
>

It works with simple macros. I'm using on another mashine some add-on
for Excel with special code to interact with another application. When
"parent" macro try to append another macro to VB Project, I'm getting
errors. It cannot compile unknown commands from add-on. Is it possible
to disable compilation for this part of code?

Thanks anyway!

Reg, VK

[Bob Phillips]

Why not load add-in?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"VK" <"myname"@example.invalid> wrote in message
news:3g1cstF9pjsrU1@news.dfncis.de...
> Bob Phillips wrote:
> > If it is an event macro, use something like
> >
> > Dim iStartAs Long
> >
> > With
ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
> > iStart = .CreateEventProc("BeforeSave", "Workbook") + 1
> > .InsertLines iStart, _
> > "Dim ans" & vbCrLf & _
> > " ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _
> > " If ans = vbNo Then Cancel = True"
> > End With
> >
> > If it just another macro in Module1 say, then use
> >
> > Dim iNext As Long
> >
> > With ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
> > iNext = .CountOfLines + 1
> > .InsertLines iNext, _
> > "Sub myProc()" & vbNewLine & _
> > " Msgbox ""Tesing new procedure"" " & vbNewLine & _
> > "End Sub"
> > End With
> >
>
> It works with simple macros. I'm using on another mashine some add-on
> for Excel with special code to interact with another application. When
> "parent" macro try to append another macro to VB Project, I'm getting
> errors. It cannot compile unknown commands from add-on. Is it possible
> to disable compilation for this part of code?
>
> Thanks anyway!
>
> Reg, VK

[Onfrek]

Because it is on another machine

[bhofsetz]

This is a page on Chip Pearson's website which discusses copying macros between workbooks which you both may find helpful

http://www.cpearson.com/excel/vbe.htm

Hope this helps.

[Bob Phillips]

I just tried to reproduce what you get but I couldn't.

Can you post the code that failed so that I can try it? I have a couple of
ideas.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Onfrek" <onfrek@yahoo.com> wrote in message
news:1117555162.545423.294480@f14g2000cwb.googlegroups.com...
> Because it is on another machine
>