use vba code to change other vba code

[alontam]

Hi all,

Does anyone know how i can use a vba code to change,edit,modify, other vba code ?

say for example i have the following code :

sub main()

Range("A1")=1

End Sub

and i want to use a new code that will enter the main() subroutine and change Range("A1") to be,say, Range("B1")

Regards,
Alon

[evertjvr]

Will this change be once off or repeated?

When must this code change?

[alontam]

dear evertjvr,

The change will be repeated
the original code will be changed when i choose to run the new code with a command button
the question is how to tell the code to choode the Range("a1") code and change it to Range("b1")

Regards,
Alon

[evertjvr]

Here is possable solution

Please Login or Register  to view this content.

for some reason the variable c needs to be "conditioned" for the column reference and i do that with c = c +1 - 1

The c value can also be populated in various other ways like from a cell value or cell selection.

[TMS]

Or c=c+0, c=--c, c*1, maybe?

[evertjvr]

Why does the variable for c need conditioning when used for column reference but not for row reference?

[TMS]

I don't know how you normally get your row reference.

In this case, c isn't Dimmed as long and InputBox would give you a text value ... which you need to convert to numeric.

Regards, TMS

[MarvinP]

It seems to me you could use and Indirect() function in a sheet to do some simple changing that would not require any VBA change.

I think other than that you'd have to create variables that find their values from somewhere on your sheet or using an input box to change.
see http://www.cpearson.com/excel/TrulyGlobalVariables.htm

You might also look at Global or Public variables or simply having a cell that points you to what you want to grab. That would be like an Indirect().

[mikerickson]

This is one way.

Please Login or Register  to view this content.