Change VBA code with VBA code

Good Tip!

Hi PaultheGreat,

Yes you can
Have a look at these examples

http://www.j-walk.com/ss/excel/tips/tip61.htm
http://www.cpearson.com/excel/vbe.htm

Although be warned, I once created some code to copy code between workbooks and McAfee thought it may have been a virus and it deleted it :)

Cheers

Dave

I seem to be operating on Bruintje time + 6 miuntes

Paul,

Here's an example:

This code will change the VBA code in Module1 of the workbook c:\test\changeme.xls
I believe that you need to set a reference to the library
"Microsoft Visual Basic for Applications Extensibility 5.x"

Sub ChangeVbaCode()
  Dim wb As Workbook
  Dim proj As VBIDE.VBProject
  Dim module As VBIDE.CodeModule
 
  Set wb = Workbooks.Open("c:\test\changeme.xls")
  Set proj = wb.VBProject
  Set module = proj.VBComponents("Module1").CodeModule
 
  module.ReplaceLine 4, "  MsgBox ""Changed message"""
  module.InsertLines 5, "  MsgBox ""A new message"""
 
  wb.Close SaveChanges:=True
End Sub


Make sure that Module1 of ChangeMe.xls contains this code:
Option Explicit

Sub MyProcedure()
  MsgBox "Hi there"
  MsgBox "Hi again!"
End Sub

Ture Magnusson
Karlstad, Sweden

having a little bit of trouble...

Dim VBComp As VBComponent
doesn't work for me... VBComponent is not a type that I can use... is there something I need to reference? I'm using Excel97.

nevermind, I found what I need to reference... it should only take a few more mins now to finalize my macro.

thanks, people, thou shalt receive thy awards in the form of "Expert points" :-)

hey, a little help with this here:

Do you guys know what "Dr. Watson for Windows NT" is? cuz he's driving me nuts! he keeps giving me an access violation error when I try to insert lines into a module... and then an error log is generated and Excel is forced to exit! Please help, the fate of the universe depends on me being able to insert lines into a module! :-)

ok what is in the eventlog of NT?

maybe log in as administrator and try again

or better post the code you use here

lol i'm an intern! i'm only 18 yrs old! unfortunately, a large worldwide company like the one for which I'm working decided not to set me up with an admin account :-)
here's the code:

Dim temp As VBComponent, temp1 As VBComponent
        FName = ThisWorkbook.Path & "\ThisWorkbook.cls"
        curWB.VBProject.VBComponents.Import FName
        Set temp = curWB.VBProject.VBComponents("ThisWorkbook")
        Set temp1 = curWB.VBProject.VBComponents("ThisWorkbook1")
        temp.CodeModule.DeleteLines 1, temp.CodeModule.CountOfLines
        temp.CodeModule.InsertLines 1, temp1.CodeModule.Lines(1, temp1.CodeModule.CountOfLines)
        curWB.VBProject.VBComponents.Remove temp1
       
        FName = ThisWorkbook.Path & "\Module1.bas"
        curWB.VBProject.VBComponents.Import FName
        Set temp = curWB.VBProject.VBComponents("Module1")
        Set temp1 = curWB.VBProject.VBComponents("Module11")
        temp.CodeModule.DeleteLines 1, temp.CodeModule.CountOfLines
        temp.CodeModule.InsertLines 1, temp1.CodeModule.Lines(1, temp1.CodeModule.CountOfLines)
        curWB.VBProject.VBComponents.Remove temp1

curWB is set earlier to a workbook... both files ThisWorkbook.cls and Module1.bas exist, are good.
Dr. Watson pops up on the first line that says "temp.CodeModule.InserLines..." and then Excel closes.

Perhaps this is causing your problems:

Excel XP Macro Security settings, Second tab ("Trusted Sources")
Check "Trust Access to VB Project"

/Ture

Ture,
I'm using Excel 97... and plus, if I can delete lines of code just fine, shouldn't I be able to add lines of code?

any help solving this issue would be greatly appreciated, thanks!

in "General Options" of Excel, "Macro virus protection" is unchecked...

had exactly this problem last year it kept crashing forgot the solution

try

Dim temp, temp1

without the reference or with reference

nope, same thing... ya had to go and forget the sol'n, didn't you? :-) i'll be contacting my support center, but any other ideas are appreciated

Thanks so far!
-PtG

you could also bring it over to a clean workbook to test, i'm sure it had to do with the reference maybe i can find it later at home :)

i appreciate the "maybe i can find it" comment, but i don't really like the continuation: "later at home"...

an interesting thing happened... I did as you suggested, bruintje, and put the code in a clean workbook... it worked! but wait... when i try to include any of my other code, it doesn't work! gah! and i need to include the other code for it to be of any practical use. yes, i realize that by now i could've finished the whole thing manually... but i'm a programmer! i shall not succumb to manual labor! and yes, i realize that since all i'm doing is using a keyboard and mouse with my hands, i am doing manual labor... but back to the problem:

i need to allow the user to select a bunch of workbooks, and then my macro will open each of those workbooks and change the macro in them. this shouldn't be a problem, right? here's my full code [...... I was about to put my code here... then it miraculously worked!]

explanation: I deleted a few lines of COMMENTS! and that FIXED it! What is WRONG with Excel?!? thank you very much for your help! i will split up the points in a little bit when i get time, probably after lunch.

Thank you everyone!
Warmest regards to all who helped,
PtG

hang on... i'm having problems once again... any time i try to add a little code (code that is perfectly fine on it's own, btw), it messes up again... i'll post more when i know more...