MrExcel Publishing
Your One Stop for Excel Tips & Solutions

adding to a macro with a macro

Posted by RoB on November 22, 2001 1:39 AM

I have a small piece of code that i need to add to many files in a directory. I have code that will open each of the files, but im not sure how to add code using a macro. i would like to add the code to the "Thisworkbook" section of the project of each file i open. How do i reference this area with a macro to automatically add it?


Posted by Robb on November 22, 2001 1:57 AM


How to do this depends on a couple of things:
- Are the projects password protected in any of the files to which
you wish to add the code?
- Is there any existing code in any of the ThisWorkbook modules - if there
is in some and not in others, that too will make a difference.

Code will not take long, once these issues are addressed.


Posted by Ivan F Moala on November 22, 2001 2:03 AM

Here is some code I did a while ago that should
help change as required.......
Change it in the routine procedure
Note: This adds the worksheet change event

Sub Modify_Modules()

Dim ModEvent As CodeModule 'Module to Modified
Dim LineNum As Long 'Line number in module
Dim SubName As String 'Event to change as text
Dim Proc As String 'Procedure string
Dim EndS As String 'End sub string
Dim Ap As String 'Apostrophe
Dim Tabs As String 'Tab
Dim LF As String 'Line feed or carriage return

Ap = Chr(34)
Tabs = Chr(9)
LF = Chr(13)
EndS = "End Sub"

'Your Event Procedure OR SubRoutine
SubName = "Private Sub Workbook_SheetChange(ByVal Sh As Object," & _
"ByVal Target As Excel.Range)" & LF

'Your Procedure
Proc = "If Target.Row = 1 Then" & LF
Proc = Proc & _
Tabs & "MsgBox " & Ap & "Testing row number =" & Ap & " & Target.Address" & LF
Proc = Proc & _
"End If" & LF

'Use ActiveWorkbook so that it can act on another open/Active workbook
Set ModEvent = ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

With ModEvent
LineNum = .CountOfLines + 1
.InsertLines LineNum, SubName & Proc & EndS
End With

Set ModEvent = Nothing

End Sub


Posted by RoB on November 22, 2001 2:16 AM

wow you guys are fast :)

1. No project passwords
2. There is some existing code. I would like to put my addition at the end of it all if possible. Most of the workbooks will be the same, BUT there may be some that have no code in the "thisworkbook" section

Posted by RoB on November 22, 2001 2:21 AM

Thanks again! i should be able to get it from this!

Posted by RoB on November 22, 2001 2:30 AM

Question Ivan

This doesnt really relate to my original question, but why did you redefine "Proc" multiple times? couldnt you have just put:
Proc = ...all your code

instead of doing part then redfining it? was there a reason for this?

Just wondering :)

Posted by Ivan F Moala on November 22, 2001 2:40 AM

Re: Question Ivan

Yes you can...BUT I did it like this to make
it easier to read and then change....your note
that to change a line for the procedure
all you have to do is change 1 line
Also there is a limit......

Just do it the way that you find easiest....I just
did it like this for readability and so that
I could change see what was happening line by line.


Posted by RoB on November 22, 2001 2:44 AM

oh I see, another question if you dont mind...

I modified the code and just tried it, but im getting the compile error on the definition of:
Dim ModEvent As CodeModule 'Module to Modify
its telling me :
user-defined type not defined

It is not recognizing CodeModule as a definition. I know I've had this problem before, and there is a way to add it, but i cant find it. Can you tell me? Thanks a million!

Posted by RoB on November 22, 2001 3:00 AM


i changed Codemodule to object and it worked! thanks!