MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Placing VBA Code "Live" Into Another WkBook Via VBA

  Asked By: Jasmine    Date: Sep 27    Category: MS Office    Views: 2082

I'm trying to compose VBA code that will create
a new workbook, and then add VBA code to the new
workbook. The "VBA code" to be added will be a
print macro that runs automatically once the new
workbook is saved and re-opened.

My question is aimed at how to add VBA code to a
different open workbook. Example : place Macro1
from workbook A into workbook B which functions
independently of workbook A. By "place", I mean
add the code, not move Macro1 over to workbook B.
And, this "VBA code placement" is to be done via
VBA and not manually, line by line. Last, the "VBA
code placement" is to be invisible to the user.



3 Answers Found

Answer #1    Answered By: Jaime Bowman     Answered On: Sep 27

I've bodged this together from Word VBA code  that I have so I haven't tested
it yet. It should insert and run code into a module called modRunCode.

Sub subAddCode()
' To Add procedure code to a module.
' Must have a reference to the Extenibility Library.

Dim vblCodeMod As CodeModule
Dim slLine2 As String
Dim slLine3 As String
Dim slLine4 As String
Dim slLine5 As String
Dim sl1 As String
Dim vl1 As Variant

vl1 = "vl2"

slLine2 = "sub subRunCode"
slLine3 = ""
slLine4 = "end sub"
slLine5 = ""

Set vblCodeMod = _
Application.Document("Document"). _
VBProject. _
VBComponents("modRunCode"). _

slLine3 = vl1 & " = " & Chr(34) & vl1 & Chr(34)

' Delete old code.
On Error Resume Next
vblCodeMod.DeleteLines 2, 3
On Error GoTo 0

' Insert new code.
vblCodeMod.InsertLines 2, slLine2
vblCodeMod.InsertLines 3, slLine3
vblCodeMod.InsertLines 4, slLine4

' Do it.
Application.Run "subRunCode"

' Clean up.
Set vblCodeMod = Nothing

End Sub

Answer #2    Answered By: Brandon Tucker     Answered On: Sep 27

One of the Excel MVPs, Chip Pearson, has some more information about using
VBA to manipulate Excel macros.


Answer #3    Answered By: Al Allen     Answered On: Sep 27

I will give your
code a try later.

Didn't find what you were looking for? Find more on Placing VBA Code "Live" Into Another WkBook Via VBA Or get search suggestion and latest updates.