Possible to have a macro "Rewrite" another macro?

[Chris Macro]

Is it possible to create a macro that can rewrite (change) another macros code? I feel like this may be a useful concept to implement for changing variable inputs on the fly instead of having to go into my code and adjust variable values. This would be for my personal macros where I couldn't necessarily store values in a workbook.

An example could be that I want to load in the activechart's legend dimensions (top,left, etc...) to a macro that adjusts the legend position. Then run the (newly written) macro to adjust other chart's legends that I select to the dimensions of the legend I "loaded in".

Has anyone ever written something like this before?

[Kenneth Hobs]

I would find some other way to do that myself.

This may be of help. http://www.cpearson.com/excel/vbe.aspx

This is how I used vbComponents when I was playing with it:

'Kenneth Hobson, http://www.mrexcel.com/forum/showthread.php?t=359785
Sub Test()
'Dim the_Calc As String
'the_Calc = "Range(""B13"").Formula = ""=SUM(D12:D14)"""
'CommandAsString the_Calc
CommandAsString Range("B5").Value
End Sub

Sub CommandAsString(cmdString As String)
'Add: Tools > Reference > Microsoft Visual Basic for Applications Extensibility 5.3 > OK
Dim MyComponent As VBComponent
Set MyComponent = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)

Dim MyCodeString As String: MyCodeString = "Public Sub VBACommandAsString" & vbCrLf
MyCodeString = MyCodeString & cmdString & vbCrLf
MyCodeString = MyCodeString & "End Sub" & vbCrLf

MyComponent.CodeModule.AddFromString MyCodeString

Application.Run "VBACommandAsString"

'Debug.Print ThisWorkbook.VBProject.VBComponents.Count

ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents.Item(MyComponent.Name)
End Sub

[snb]

You might have a look over here:

http://www.snb-vba.eu/VBA_Excel_VBproject_en.html

A short illustration:


Sub M_snb()
ThisWorkbook.VBProject.VBComponents.Add(1).CodeModule.AddFromString Replace(Replace("Sub M_snb()~#msgbox ""You see what I mean ?""~End Sub", "#", vbTab), "~", vbLf)
End Sub

[Chris Macro]

Thank you both for the links. Snb, that is an amazing resource you put together and I will definitely be bookmarking that page! This section is what I used to get to a solution that worked for me. Thanks a bunch!

Macro: Replace Code


Sub Macro_code_vervangen()

With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
.DeleteLines .ProcStartLine("macro3", 0) + 1, .ProcCountLines("macro3", 0) - 2
.InsertLines .ProcStartLine("macro3", 0) + 1, "c00 = " & Chr(34) & "Dit is de nieuwste tekst"
End With

End Sub