Search and replace a Sub for several workbooks

[Maagaard]

Hi,

I have a "master" workbook, where I write all my macros, and do all general changes.

My colleagues then copy this "master" and change different things, but only small things like names and other identifiers.

What I would like to know is, if it is possible to write a sub, that can find a specific sub, in each of my colleagues workbooks, and rewrite it.

Currently they have 47 versions of this workbook, with at least 4 sheets in each of them. This means that I have to update these workbooks 188 separate places each time I make a change to a sub. Hence it will save me an enormous amount of time if I could write a "Mass edit" sub that could rewrite the macros in all of my colleagues workbooks at once.

Is this possible? I'm really desperate for some help here

Thanks in advance!

Maagaard

 

[Col Delane]

Hi Maagaard

The solution to your problem may depend upon what your sub's are used for. Do they manipulate the contents of the sheets in the other users' 'master' workbooks, or are they "general use" macros that act upon other open workbooks?

I have a "personal.xlsm" file (Excel's special file that opens, but is hidden, whenever I launch Excel) in which I hold a library of sub's that can be used to act on other open workbooks. I also use it as my "toolbox" to hold all sorts of Excel and VBA items (e.g. sample formulae, sheets templates, examples of Data Validation, sample VBA routines, often used functions, etc., etc.)

From what I've read, writing code to do stuff on other code (i.e. manipulating the VBE) is tricky and fraught with danger, so is best avoided.
One option is to convert your master into an Add-In and lock it so others cannot change the code, and then deploy a copy to each user. If you need to make changes, you then just distribute an updated copy for the users to save over top of the old version. There are various sources that explain how to create an Add-In (Chip Pearson has a page here Creating An XLA Add In)

HTH

 

[Maagaard]

Hi Col Delane,

Thanks for your response!

That might be a solution, however the problem is, that the other workbooks, where I need to change the code, is used by several hundred different users. The excel skills vary a lot, and I do not think that all of them know how to install an add-inn, and the best solution would be that I can update the workbooks my self instead.

I know that it is possible to write new code via a macro, however my question is if it is possible to find existing macros and rewrite them.

I can add, that no one else, but me, works on and with the existing code.

 

[Col Delane]

I understand your dilemma, but I think you'll find it much easier to create and deploy an Add-in (and subsequent updates) than trying to write code to directly access and update several hundred different workbooks (you have to locate and open each file!!??). You can spell out the steps to install the Add-in (and may be even semi-automate this) and the subsequent update procedure in the email you use to distribute it.

 

[Maagaard]

The problem is not locating the workbooks, as they are stored centrally, so that is just as simple loop to run through each of them.

I see the idea in an add-inn however I think the combined resources spend on me updating each workbook manually vs. having several hundred people installing an add-inn means that I have to update manually.

 

[Col Delane]

Sounds like you're caught between a rock and a hard place!

Chip Pearson, one of the world's Excel gurus who very generously makes freely available a tonne of Excel and VBA material, has a page about "Programming The VBA Editor" ( see Programming In The VBA Editor), including the following topics:

• Adding A Module To A Project
• Adding A Procedure To A Module
• Copy A Module From One Project To Another
• Creating A New Procedure In A Code Module
• Creating An Event Procedure
• Deleting A Module From A Project
• Deleting A Procedure From A Module
• Deleting All VBA Code In A Project
• Renaming A Module

If you exercise complete control over the VBA side of things (i.e. only you can make changes) then rather than trying to edit individual lines in specific procedures, it may be easier to use Chip's code to completely delete the existing sub (or module) and replace (copy from master) it with the updated one.

HTH
Good luck

 

[Rick Rothstein]

If you exercise complete control over the VBA side of things (i.e. only you can make changes) then rather than trying to edit individual lines in specific procedures, it may be easier to use Chip's code to completely delete the existing sub (or module) and replace (copy from master) it with the updated one.

Not if, as the OP said in his opening message "My colleagues then copy this "master" and change different things, but only small things like names and other identifiers." The "and change different things" also bothers me for what the OP is wanting to do as well... making sure the changes the OP wants to make does not "step" on any of changes the users already made is where my concern lies.

 

[Maagaard]

Thank you for all your help!

I will have a look at Chip Pearsons page.

I am not worried about my colleagues, as I've written the code in such a way, that they can only write/changes data in specific areas of the workbook and do not have access to the code it self.

 

[Col Delane]

Hi again Maagaard

I saw this today in a e-newsletter from ExcelTips [http://excelribbon.tips.net/] to which I subscribe, and thought it may offer a solution to your problem. I did suggest that you deploy an Add-in, but I wasn't aware that you could do so via a central folder on a network rather than having each user be sent a copy of the .xlam file.

If you work in a networked environment, you may have a need to make a common set of custom functions available to all the users on your network. For instance, your company may have some specialized functions that perform some financial calculations in a particular way. You may be wondering how to best supply these functions to users on your network, without allowing them to modify the functions themselves.

Perhaps the best way to handle this situation is to put all your functions into a single worksheet, and then compile the worksheet into an Excel add-in. You can then place the add-in on a shared network directory from which everyone can access the add-in. If you need to change the functions in the future, simply update the add-in and copy it to the shared directory. The next time a user starts Excel, the newly updated add-in is loaded, and the updated functions are automatically available.

Also note that you can locate the code to install the add-in within a workbook that you send to the users and have them merely open the workbook and click a button to do the install. (Download a free copy of Jan Karl Pieterse's 'Name Manager' add-in from [http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp] to see how he does it.)


Cheers
Col