Can you create a macro that creates a macro and saves to a worksheet?

[rob.parson]

What I'm doing now:

Opening a file that's saved as an .xml in Excel, and saving it as .xls

What I need to add:

When I open the file, as part of the macro, I need it to create another macro (that I already have the code for), and saves it WITHIN that excel sheet.

So, I have a basic .xml, or .xls if you prefer, without any macro in it. During the macro that opens that file, it needs to write a macro to that excel file and embed it within. (The Macro will create a graph. The client doesn't want to simply create the graph-he wants the macro in there to give him the option of creating the graph or not).

Is this even possible? If so...anyone have any pointers of where to even begin? This request has me completely baffled...

[Leith Ross]

Hello rob.parson,

Welcome to the Forum!

Yes, this is possible using VBA. You would need to post the code for the main macro and the code for creating the chart.

[mikerickson]

You could do something like this

Sub test()
    Dim newBook As Workbook
    Dim newModule As Object
    
    Set newBook = Workbooks.Add
    
    Set newModule = newBook.VBProject.VBComponents.Add(1).CodeModule
    
    newModule.AddFromString myRoutineString
    
End Sub

Function myRoutineString() As String
    myRoutineString = "Sub test()"
    myRoutineString = myRoutineString & vbCr & "    MsgBox ""hello"""
    myRoutineString = myRoutineString & vbCr & "End Sub"
End Function

Alternalty, you could get the argument for AddFromString by reading an existing routine using .CodeModule.Lines