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