Replacing large pieces of VBA code 1

[Thingol]

Hi there,

I'm trying to replace parts of my code, now doing this by hand takes up a lot of time. I need to replace around 70 identical parts of code, which are some 10 lines long. The CTRL+H command doesn't work with pieces of text that are longer than 1 line. Is there some other way to do this quickly? Moreover, all the pieces that have to be replaced are identical pieces of text, and they will be replaced by pieces of code that are identical to each other as well.

 

[acron]

Can you copy all the code to Word or some editor and repace there and paste back ?

AC

 

[Thingol]

I tried that, but I believe Word doesn't feature a replace function for pieces of text of this size either. Well, I'll just go ahead and copy/paste all the bits of code by hand.
Thanks though!

Martijn.

 

[dsb]

why do you need 70 identical pieces of code ? one subroutine called from 70 different places would be more appropriate.

That way...once you make this change, you will only have to change the functionality of the code in one place, not 70.

 

[Thingol]

OK, That sounds like a plan. I'm new to programming in general though. How can I call subroutines from within other routines?

 

[dsb]

Please post more detail on what is causing this repetition of code.

dsb

 

[JESTAR]

Hi

If you have something like this in a module, it will make it global so you can call it from elsewhere.

Sub Whatever()
Stick that code you want in here
End Sub

Now when you need to call upon that routine, do this. e.g.

Private Sub Button1_Click()
Whatever
End Sub

That's only an example, you will need to customise it, obviously.

Also, put Option Explicit at the top of every page of code to stop conflicts.

 

[Thingol]

Basically, the repetition of the code is the protecting and unprotecting of all the worksheets in the workbook, and also protecting and unprotecting the workbook itself. In each subroutine that alters data in the sheets, the sheets are unprotected. Before the subroutine ends, the protection is turned on again.

 

[Thingol]

What does the option explicit do? And what do you mean by 'at the top of each page'; do you mean at the top of each sub?

 

[dsb]

In a module, Create 2 public subroutines (1: All_Protect, 2: All_UnProtect) and place the appropriate code in each.

Replace the code in each existing routine with calls to the appropriate routine

If you add or delete sheets, there will be only two places to change.

examples

sub sheet_routine_x
ALL_UnProtect
..
.. code for module
..
ALL_Protect
end sub



Public Sub ALL_Protect
....code for "set protected"
End Sub

Public Sub ALL_UnProtect
..... code for "set unprotected"
End Sub

(also, I think you are stuck with changing all 70 by hand, but subsequently, you should only have to chane the 2 subroutines).

 

[Thingol]

Yes, I understand. Thanks!

 

[Loomah]

Option Explicit

This forces you to 'declare' your variables in your projects. This becomes useful at when it comes to debugging your routines as it ensures that you cannot use a variable that is miss spelled ie if you declare a variable as MyString and later miss spell it as MyStrong the error will be recognised as soon as you try to run a routine.

It might be easier to just visit this site:-

http://www.cpearson.com/excel/variables.htm

To ensure Option Explicit is always in your projects open the VBE (Alt+F11) choose Tools>Options and from the Editor tab choose 'Require Variable Declaration'

 

[Thingol]

Oh, ok, I thought it would be something like that. well, I always declare my variable, so that never really gives any problems....

 

[JESTAR]

Not at the top of every sub, but at the top of every page of code, if you will.

 

[Thingol]

How is a page of code defined? I don't see any page seperations? and why at the top of every page?