VB Excel Macro That Edits Itself Code

Hey Everyone,

I haven't decided to tackle this yet, but I'm just wondering if it is possible to create an Excel VB Macro that can alter/edit the code of another Macro? Maybe there's another way around it, but this is the scenario that has promted me to think about this:

Each day I recieve production reports. At the end of each month I pull certain data from these reports to create a monthly report. Sometimes the format of these reports are different, as a result I've had to construct an extensive VB macro that searches for various rows and columns, and does several complicated checks on each daily report to ensure that the data I am pulling is correct.

As it stands right now Macro 1 is pulling information about Items A & B, but I know new items will be added to these reports. So lets say in 2 months Item C is added, then D, and eventually I have new items being added on a monthly basis.

Is it possible to create a macro that alters the original Macro 1 in such a way that it will begin to pull the new information on new items?

Thanks to everyone for your input.

Best way to do something like that would be to create a range in an empty worksheet

And then in the macro create an array holding those values (Items as you describe them)

     A
1   Item A
2   Item B
etc

This sheet can be updated from a macro or manually

In the macro doing all the various things you then create an array

myThingArray = Sheets("Mythings").Range("A1:A" Sheets("MyThings").UsedRange.Rows.Count).value

Now you can create a loop that does whats required for each item.

Hello ashleys.nl,

Yes you can modify a macro in place with another macro. This macro reads the updated macro text from a file. The entire macro is deleted and then replaced with file contents. This assumes that the macro is always located in the same VBA Module or Worksheet within the active workbook.

Change the file path, file name, module name, and macro name to match what you are using. The macro can be run either automatically or manually. I think automatically would be best. It could be run when the workbook opens and insures that the macro is updated before it is used.

' Thread:  http://www.excelforum.com/excel-programming/804773-vb-excel-macro-that-edits-itself-code.html
' Poster:  ashleys.nl
' Summary: Replaces a macro in the workbook with updated code from a file.
' Author:  Leith Ross
' Written: December 08, 2011

Sub UpdateMacro()

    Dim CountOfLines As Long
    Dim Filename As String
    Dim Filepath As String
    Dim FSO As Object
    Dim MacroName As String
    Dim ModuleName As String
    Dim StartLine As Long
    Dim UpdatedText As String
    Dim VBcode As Object
    Dim VBcomp As Object
    Dim VBproj As Object
    
    
        ModuleName = "Module2"
        MacroName = "TestB"
        
        Filepath = "C:\Test Folder"
        Filename = "Updated Macro.txt"
        
            Set FSO = CreateObject("Scripting.FileSystemObject")
            Set TextFile = FSO.OpenTextFile(Filepath & "\" & Filename, 1, False, -2)
                UpdatedText = TextFile.ReadAll
            TextFile.Close
            
            Set VBproj = Application.VBE.ActiveVBProject
            Set VBcomp = VBproj.VBComponents(ModuleName)
            Set VBcode = VBcomp.CodeModule
        
                StartLine = VBcode.ProcStartLine(MacroName, 0)
                CountOfLines = VBcode.ProcCountLines(MacroName, 0)
                
                VBcode.DeleteLines StartLine, CountOfLines
                VBcode.InsertLines StartLine, UpdatedText
            
End Sub