Is it possible to use a vba macro to delete other macros?

[BYahr]

I have successfully created individual macros that update 20 individual spreadsheets. These macros are all stored in a template used daily. For storage reasons, I want to delete the individual macros when the day comes to an end. No need to store 20 macros with each day's workbook when they are in the template.



Each macro has a button to activate it the I delete (cut) once it has been run. I would like to be able to easily delete all the actual macros (modules) when the day's work is completed. I believe the macros can be 'hidden' since there is a button to activate it but that doesn't reduce the storage space needed. It's not much but after 365 days a year it does add up.

[Logit]

https://www.youtube.com/watch?v=0u9BsdpEO1I

What is the command in Excel VBA to delete a specific macro? - Stack Overflow

Here are two macros :

Option Explicit

'Reference : "Microsoft Visual Basic For Applications Extensibility"

Sub RemoveSheetModule()
    Dim wb     As Workbook
    Dim md     As Module
    Dim str    As String
   
    Application.ScreenUpdating = False
        Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "URL Check - Copy.xlsm")
       
        For Each md In wb
            Select Case md.Name
                Case "Module1"
                    str = md.CodeName
                    With wb.VBProject.VBComponents(str).CodeModule
                        .DeleteLines 1, .CountOfLines
                    End With
                Case Else
            End Select
        Next md
       
        wb.Close True
    Application.ScreenUpdating = True
   
    MsgBox "Done...", 64
End Sub

Sub delModMacro()
Dim wb As Workbook
 Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "CommandButton.xlsm")
    With wb.VBProject.VBComponents("Module1").CodeModule   '<--- change to ThisWorkbook for that module

            .DeleteLines 1, .CountOfLines

    End With
    
 MsgBox "Done...", 64
End Sub

[Logit]

Option Explicit

'Reference : "Microsoft Visual Basic For Applications Extensibility"

Sub RemoveSheetModule()
    Dim wb     As Workbook
    Dim md     As Module
    Dim str    As String
   
    Application.ScreenUpdating = False
        Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "URL Check - Copy.xlsm")
       
        For Each md In wb
            Select Case md.Name
                Case "Module1"
                    str = md.CodeName
                    With wb.VBProject.VBComponents(str).CodeModule
                        .DeleteLines 1, .CountOfLines
                    End With
                Case Else
            End Select
        Next md
       
        wb.Close True
    Application.ScreenUpdating = True
   
    MsgBox "Done...", 64
End Sub

Sub delModMacro()
Dim wb As Workbook
 Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & "CommandButton.xlsm")
    With wb.VBProject.VBComponents("Module1").CodeModule   '<--- change to ThisWorkbook for that module

            .DeleteLines 1, .CountOfLines

    End With
    
 MsgBox "Done...", 64
End Sub

[Guessed]

Whilst you can remove code via VBA wouldn't it be more straightforward to convert the file to xlsx and remove the xlsm version?

[BYahr]

As I researched more into the issue, I am leaning this direction. With this, I'm wondering if it can be set up so that the .xslm document is delete automatically so that the correct document is the one uploaded into the permanent record.

[Guessed]

There will be a Catch 22 with implementing that idea: Not possible to delete the file while it is open AND the file needs to be open while the macro is running.

The usual method to do this would be to create the macro and store it in a different workbook (or your Personal.xlsb) so the code never resides in the template workbook.

[BYahr]

That takes me back to a problem I have tried to get around before. How to call to the workbook from the personal.xlsb when the workbook's name is different each day.

[NoSparks]

you never did acknowledge the response to your posting here, but did it not at least give you an idea of how to go about the changing date part of the file name?

[BYahr]

I have now replied to that particular post. Thanks for the reminder.