is there any macro delete specific macros when save file as xlsm?

MKLAQ

Active Member
Joined
Jan 30, 2021
Messages
387
Office Version
  1. 2016
Platform
  1. Windows
hello
I hope to find answering to my question. the question is . is there macro to delete specific macros when save file as xlsm ? so if I have many codes are existed in module1, module2,module3
I want just delete module1,2 and save the file as xlsm
thanks
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
thanks for this usfeul article. it helped me , but now I need from experts how adapt the code by using array when delete more than one module

VBA Code:
Sub DeleteModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
    
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        VBProj.VBComponents.Remove VBComp
    End Sub
 
Upvote 0
Untested here :

VBA Code:
Sub DeleteModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        
        Dim VBComp2 As VBIDE.VBComponent
    
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        VBProj.VBComponents.Remove VBComp
        
        Set VBComp2 = VBProj.VBComponents("Module2")
        VBProj.VBComponents.Remove VBComp
        
    End Sub
 
Upvote 0
@Logit thanks but it gives error in last line in the code
1.PNG
 
Upvote 0
Try macro below
Code:
Option Explicit
Sub DeleteModulesExcept()
'http://www.cpearson.com/excel/vbe.aspx
   
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
     
    Set VBProj = ActiveWorkbook.VBProject
     
    For Each VBComp In ActiveWorkbook.VBProject.VBComponents
        Debug.Print "Checking " & VBComp.Name
        If VBComp.Type = vbext_ct_StdModule Then
            If VBComp.Name = "Module1" Or VBComp.Name = "Module2" Then 'delete all except this modules
            Else
                Debug.Print "Removing " & VBComp.Name
                VBProj.VBComponents.Remove VBComp
            End If
        End If
    Next
End Sub
Check ON in VBE -> Tools -> References -> 'Microsoft Scripting Runtime' and 'Microsoft Visual Basic for Applications Extensbility 5.3'
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top