Macro to delete itself

Is it possible to use VBA to have a Macro delete itself once finished running?

Save the file as xlsx and it's gone

Hi there,

See if the code in the attached workbook does what you need. It contains two methods - one deletes just the routine itself, the other deletes the entire CodeModule which contains the routine.

The first VBA CodeModule contains the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Sub RunAndDeleteCodeModule()

    Const sMODULE_NAME As String = "Module1"

    MsgBox "The VBA CodeModule which contains this routine will now be deleted"

    With ThisWorkbook.VBProject
        .vbComponents.Remove .vbComponents(sMODULE_NAME)
    End With

End Sub

The second VBA CodeModule contains the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Private Sub DummyRoutine_1()

'   This routine will remain after the specified routine has been deleted

End Sub


'=========================================================================================
'=========================================================================================


Sub RunAndDeleteRoutine()

    Const iVBEXT_PK_PROC    As Integer = 0
    Const sROUTINE_NAME     As String = "RunAndDeleteRoutine"
    Const sMODULE_NAME      As String = "Module2"

    Dim objCodeModule       As Object
    Dim iFirstLineNo        As Integer
    Dim iNoOfLines          As Integer

    MsgBox "The VB routine which generated this message will now be deleted"

    Set objCodeModule = ThisWorkbook.VBProject.vbComponents(sMODULE_NAME).CodeModule

    With objCodeModule

        iFirstLineNo = objCodeModule.ProcStartLine(sROUTINE_NAME, iVBEXT_PK_PROC)

        iNoOfLines = objCodeModule.ProcCountLines(sROUTINE_NAME, iVBEXT_PK_PROC)

        .DeleteLines StartLine:=iFirstLineNo, Count:=iNoOfLines

    End With

End Sub


'=========================================================================================
'=========================================================================================


Private Sub DummyRoutine_2()

'   This routine will remain after the specified routine has been deleted

End Sub

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M