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