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

BYahr · #1 08-30-2023, 08:18 AM

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 · #2 08-30-2023, 05:07 PM

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 :

Code:

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 · #3 08-30-2023, 05:09 PM

Code:

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 · #4 09-01-2023, 12:58 AM

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 · #5 09-05-2023, 07:14 AM

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 · #6 09-05-2023, 08:23 PM

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 · #7 09-07-2023, 11:19 AM

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 · #8 09-07-2023, 06:01 PM

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 · #9 09-08-2023, 07:19 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
A few macros or functions run in a single macro?	pentagram_31	Word VBA	15	10-29-2018 01:46 PM
Compatibility of 2 macros in mail merge: Delete table rows + save individual PDFs	Btop	Word VBA	26	03-07-2018 01:45 PM
URGENT: Macro to open document and apply other macros successively	Krategy	Word VBA	1	12-02-2016 04:41 PM
VBA Powerpoint Macros: Set String in one Macro, use it in another	Martijn6134	PowerPoint	0	11-09-2016 05:36 AM
Macro to find a word in first row of table and then perform two macros	hmsrose	Word VBA	5	01-30-2015 12:17 AM