How do you change code in the VBE without opening the target workb

G

Guest

I am wondering if there is a way of changing the code in the VBE for other
Excel files while the target Excel file is already closed. Is this possible?
How would I code this?
 
N

Norman Jones

Hi Brett,

Chip Pearson shows how to write or delete code in an external project.
However, I think that the other workbook would have to be open - I missed
the 'closed' stipulation.
 
T

Tushar Mehta

You should consider writing code so that it doesn't need replacement at the
source code level. An add-in would be one way to go. From an earlier post
of mine...

I've been meaning to write a tutorial on the subject. Just haven't
gotten around to it. In the meantime, check msdn.microsoft.com.
Specifically,
Microsoft Office XP Developer
Building Application-Specific Add-ins
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/modcore/html/deovrbuildingapplicationspecificaddins.asp
and
Microsoft Office XP Developer
Add-ins, Templates, Wizards, and Libraries
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/modcore/html/deovraddinstemplateswizardslibraries.asp.

Concentrate on the 'Application specific add-ins' and not the 'COM Add-
Ins.'

Basically, in XL, after saving the workbook with the code, save it as
an add-in (File | Save As...) Close the file, then load the add-in
through Tools |Add-Ins...

A key point to remember is that code in the add-in that refers to
ThisWorkbook refers to the add-in workbook, while a reference to
ActiveWorkbook refers to the open workbook, whatever that might be.

***** Please make very very sure you always save the file as a normal
workbook before saving it as an add-in. Otherwise, you may be unable to
access the worksheets / source in the add-in file! *****

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top