Question

Change VBA code with VBA code

Asked by: PaultheGreat

How do I change my macro code with a macro in a separate workbook?

My situation is this: I made a macro that seemed to work just fine. The user(s) of my macro ran it for a while without experiencing any problems. There are now 30-40 copies of this macro workbook in one directory, each with different information that the macro pulled in (they are for different companies). Suddenly, they realized that something wasn't quite how it was supposed to be, so I had to change about two or three lines of code in my macro. I now have an updated workbook, but I don't want to have to copy and paste the code into all the old workbooks. And I certainly don't want to re-run the new macro to import the data for all 30-40 companies that we already did.

Is there some way that I can simply open the workbook through code (that's easy, I know how to do that), access the VBA code in that workbook, then change that code (or change just a part of it, like one or two lines in one or two subroutines) with my macro?

This is rather urgent, if I can get a response to this question this morning, that would be great. If I don't get a response by around 1-2pm, I'll have to do it all manually (feel free to respond after that, but I'll be busy working, so I probably won't respond or anything for a little while).

Regards,
PtG

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2003-08-28 at 06:54:39ID20722697
Tags

vba

Topic

Microsoft Excel Spreadsheet Software

Participating Experts
4
Points
500
Comments
21

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. excel, vba,  subroutines, getting own name
    vba rookie, xl2000 Is there a way for a currently executing subroutine to obtain its own name?, file name?, file line number?
  2. how to call vba subroutine from a macro
    I tried RunCode MySub("test") but when I run the macro I get error message: Microsoft Access can't find the name 'mySub' you entered in the expression. Here is the subroutine: public sub mySub(s) msgbox s end sub
  3. Excel VBA : How to execute a subroutine in another work…
    Hi, I have a workbook (e.g. macro.xls) contains all VBA codes that need to process our data (no data contained in macro.xls). Yet, cause some of our user workbooks also contain some code and I don't want to copy or rewrite them in my macro.xls, therefore, in my code, I open...
  4. Excel VBA - Run macro in anothe workbook
    Hi In Excel VBA, How would you run a macro or procedure in another Excel workbook Thanks
  5. Remove VBA Code from multiple workbooks
    Hi, I have a weird one. I have thousands of excel workbooks(2000/2003) with various revisions of VBA(password protected) embedded. To clean things up Ive created an xla with all the required code taken from the xls/vba- all the xls were originally created from a template wh...
  6. vba word 2003 AutoExec subroutine not working
    vba word 2003 AutoExec subroutine not working I have a simple AutoExec in global module.

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: bruintjePosted on 2003-08-28 at 06:56:50ID: 9240408

try

title: programming to the vbe
source : http://www.cpearson.com/excel/vbe.htm

hope this helps a bit

 

by: dragontoothPosted on 2003-08-28 at 06:59:29ID: 9240426

Good Tip!

 

by: brettdjPosted on 2003-08-28 at 07:02:49ID: 9240457

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

 

by: brettdjPosted on 2003-08-28 at 07:03:46ID: 9240469


I seem to be operating on Bruintje time + 6 miuntes

 

by: brettdjPosted on 2003-08-28 at 07:09:31ID: 9240530

Here is another link if you get bored with Mr Walkenbach & Mr Pearson

http://www.erlandsendata.no/english/index.php?t=envbavbe

 

by: turePosted on 2003-08-28 at 07:20:32ID: 9240629

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

 

by: PaultheGreatPosted on 2003-08-28 at 07:39:36ID: 9240837

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.

 

by: PaultheGreatPosted on 2003-08-28 at 07:42:22ID: 9240868

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" :-)

 

by: PaultheGreatPosted on 2003-08-28 at 07:49:54ID: 9240943

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! :-)

 

by: bruintjePosted on 2003-08-28 at 08:01:04ID: 9241079

ok what is in the eventlog of NT?

maybe log in as administrator and try again

 

by: bruintjePosted on 2003-08-28 at 08:01:30ID: 9241086

or better post the code you use here

 

by: PaultheGreatPosted on 2003-08-28 at 08:11:20ID: 9241192

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.

 

by: turePosted on 2003-08-28 at 08:19:12ID: 9241283

Perhaps this is causing your problems:

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

/Ture

 

by: PaultheGreatPosted on 2003-08-28 at 08:29:44ID: 9241378

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!

 

by: PaultheGreatPosted on 2003-08-28 at 08:31:49ID: 9241398

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

 

by: bruintjePosted on 2003-08-28 at 08:37:28ID: 9241465

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

try

Dim temp, temp1

without the reference or with reference

 

by: PaultheGreatPosted on 2003-08-28 at 08:42:34ID: 9241520

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

 

by: bruintjePosted on 2003-08-28 at 08:47:18ID: 9241570

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 :)

 

by: PaultheGreatPosted on 2003-08-28 at 09:36:39ID: 9241976

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

 

by: PaultheGreatPosted on 2003-08-28 at 10:36:55ID: 9242388

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...

 

by: PaultheGreatPosted on 2003-08-28 at 11:58:41ID: 9242967

alright, for some reason this code is no longer working... I had it working for a little bit, but when I tried to add more stuff into it, it just quit. I have been trying many more times, copying code into new workbooks, using code that used to work just fine... but all to no avail. i cannot get it to function correctly, i keep getting evil Dr. Watson... so for now I have to do this manually... I would still like to get the macro working so i can use it in the future... btw, i've tried plenty of things like restarting, making sure nothing else is running at the same time, trying it on a different comp... nothing seems to work.

thank you very much, though, bruintje and brettdj, for providing links to resources that helped me learn how i should be able to do what i want to do... if it would work! :-P no wonder people make things like the blaster worm: they get sick of M$ apps failing for no reason and decide to harness the power of millions of computers to attack the M$ website... lol too bad they thought of it before me :-) (not that i could/would do that if it hadn't been done before...)

Regards,
PtG

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...