Modify VBA with VBA

[MrKowz]

Hey all,

I have read the article by Chip Pearson (http://www.cpearson.com/Excel/vbe.aspx), and I have a question that isn't quite touched on in there.

Is it possible to edit a specific line in a module through a procedure? I am leaving the company I am with at the end of the week, and I am trying to build the most effective way for my employer to continue to use a program I have written (whose code has several areas that is dependent on the naming convention of the workbooks that are opened).

It WOULD be possible for me to go through the code and adapt it for the workbook name through a variable, but I simply don't have time to accomplish this.

Basically, I need to change the following to a specific inputbox value
Module: Sheet1

• Line 9, Col 72 through Line 9, Col 80
• Line 21, Col 57 through Line 21, Col 65
• Line 32, Col 85 through Line 32, Col 93

In each of those line/columns, I am effectively changing the word "Template" to the inputbox value (variable length). I just don't know how to specifically change those lines through VBA.

If I can get a solution to this, I am confident I can build the solution for other modules.

Thanks in advance!

 

[MrKowz]

Bump

 

[poolhall]

Sub testlkj23ljk4234jkl()
Dim myValue As String

myValue = InputBox(prompt:="Enter value")
    Sheet1.Range("BT9:CB9").Value = myValue
End Sub

 

[MrKowz]

Sub testlkj23ljk4234jkl()
Dim myValue As String
 
myValue = InputBox(prompt:="Enter value")
    Sheet1.Range("BT9:CB9").Value = myValue
End Sub

I don't need to adjust the worksheet, I need to adjust the actual visual basic code.

 

[xenou]

It WOULD be possible for me to go through the code and adapt it for the workbook name through a variable, but I simply don't have time to accomplish this.

I'm not sure I see why adding a variable/function to replace a hardcoded value takes a long time. Just find and replace (find: template replace with: my_input_box_function).

Heck, if its three lines, you don't even need find/replace.

 

[MrKowz]

I'm not sure I see why adding a variable/function to replace a hardcoded value takes a long time. Surely no less troublesome than writing code to write code. Just find and replace (find: template replace with: my_input_box_function).

I won't be able to modify the current code to adapt with a variable/function unless I'm at work due to confidentiality; and I am booked 100% through the end of tomorrow, my last day. The other code I can work on at home since I know what specific line/cols to replace.

 

[xenou]

You can't modify the code with a variable, but you *can* modify the code with more code? Not sure I understand ...

 

[MrKowz]

I'd be able to create the code at home, off the clock, then when I get in tomorrow I'd be able to just copy the code into the current workbook.

Also, this is for my own understanding and education to further my knowledge in Excel.

 

[xenou]

I figured as much from you. But I'm no hand at writing code to write code. It seems the long way around to me (just write the new code, then copy that in tomorrow). But as they say, to each his own ...

 

[Norie]

So you can't find and replace at home?

I can understand you want to learn about this but if this needs to be done quickly then it might be easier to just to find/replace.

At least you'll get it done.

You could even copy the code somewhere else if you want a more powerful find/replace than that available in the VBE.