• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete all VBA with code

Juriemagic

New Member
Hi guys,

I have been searching the internet for a code which will delete all vba. There are thousands of codes, yet, nothing works for me. could someone please assist me with a code that will do what I want done, please. I am using Excel 2013. All help will be greatly appreciated. Thank you all.
 

herofox

Member
if you want by code <you can make by this code
Code:
Option Explicit
 
Sub DeleteAllCode()
    
     'Trust Access To Visual Basics Project must be enabled.
     'From Excel: Tools | Macro | Security | Trusted Sources
    
    Dim x               As Integer
    Dim Proceed         As VbMsgBoxResult
    Dim Prompt          As String
    Dim Title           As String
    
    Prompt = "Are you certain that you want to delete all the VBA Code from " & _
    ActiveWorkbook.Name & "?"
    Title = "Verify Procedure"
    
    Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
    If Proceed = vbNo Then
        MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
        Exit Sub
    End If
    
    On Error Resume Next
    With ActiveWorkbook.VBProject
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(x)
        Next x
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents(x).CodeModule.DeleteLines _
            1, .VBComponents(x).CodeModule.CountOfLines
        Next x
    End With
    On Error GoTo 0
    
End Sub
 

Juriemagic

New Member
if you want by code <you can make by this code
Code:
Option Explicit

Sub DeleteAllCode()
   
     'Trust Access To Visual Basics Project must be enabled.
     'From Excel: Tools | Macro | Security | Trusted Sources
   
    Dim x               As Integer
    Dim Proceed         As VbMsgBoxResult
    Dim Prompt          As String
    Dim Title           As String
   
    Prompt = "Are you certain that you want to delete all the VBA Code from " & _
    ActiveWorkbook.Name & "?"
    Title = "Verify Procedure"
   
    Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
    If Proceed = vbNo Then
        MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
        Exit Sub
    End If
   
    On Error Resume Next
    With ActiveWorkbook.VBProject
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(x)
        Next x
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents(x).CodeModule.DeleteLines _
            1, .VBComponents(x).CodeModule.CountOfLines
        Next x
    End With
    On Error GoTo 0
   
End Sub
Hello Herofox,

this is one of the codes I have tried...it works every other time..meaning....aftet the file is saved as xlsm, and I click the button to run the code, iteither execute or it doesn't, and when it does not, you see the circle going on for and on...when it works, it executes in less that a second...but here it is...at the times when the code does not work...I open the vba editor to basically look at the code, then close the editor...and then it executes...I do not understand this...I have tried several other codes too, no luck...
 

Juriemagic

New Member
if you want by code <you can make by this code
Code:
Option Explicit

Sub DeleteAllCode()
   
     'Trust Access To Visual Basics Project must be enabled.
     'From Excel: Tools | Macro | Security | Trusted Sources
   
    Dim x               As Integer
    Dim Proceed         As VbMsgBoxResult
    Dim Prompt          As String
    Dim Title           As String
   
    Prompt = "Are you certain that you want to delete all the VBA Code from " & _
    ActiveWorkbook.Name & "?"
    Title = "Verify Procedure"
   
    Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
    If Proceed = vbNo Then
        MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
        Exit Sub
    End If
   
    On Error Resume Next
    With ActiveWorkbook.VBProject
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(x)
        Next x
        For x = .VBComponents.Count To 1 Step -1
            .VBComponents(x).CodeModule.DeleteLines _
            1, .VBComponents(x).CodeModule.CountOfLines
        Next x
    End With
    On Error GoTo 0
   
End Sub
herofox,

as expected..this code just circles...
 

Fluff13

Active Member
Hi Fluff13,

I did this, but then the workbook cannot be opened...file extension/format error..any way around this maybe?..thank you for your time..
Did you use "SaveAs" and select .xlsx as the file type?
If so you should have gotten a warning about "The following features cannot be saved...", at which point you click "Yes" to continue.
 

Juriemagic

New Member
Did you use "SaveAs" and select .xlsx as the file type?
If so you should have gotten a warning about "The following features cannot be saved...", at which point you click "Yes" to continue.
I use this code to do the saving:
Code:
ActiveWorkbook.SaveCopyAs "G:\RIDGE GAMING\RIDGE TECHNICAL\STOCK TAKE REPORTS\Technical Stock - .XLSM"
If I change the extension to XLSX, it copies and saves without a problem....but then I cannot open it..
 

Fluff13

Active Member
You cannot change the file type using SaveCopyAs, you have to use SaveAs
Like
Code:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "G:\RIDGE GAMING\RIDGE TECHNICAL\STOCK TAKE REPORTS\Technical Stock - .XLSx", 51
Application.DisplayAlerts = True
 

Juriemagic

New Member
You cannot change the file type using SaveCopyAs, you have to use SaveAs
Like
Code:
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "G:\RIDGE GAMING\RIDGE TECHNICAL\STOCK TAKE REPORTS\Technical Stock - .XLSx", 51
Application.DisplayAlerts = True
I have copied and pasted your code as is...when I click the button, the button responds, but going to the folder, the file is not there. Maybe if I may just mention something...it is not really necessary for all the code to be deleted, the only reason I'm going this way is because I am not successful with just deleting a few lines in a sheet module either. If I could delete the first 4 lines of the sheet activation module, that would be perfect...
 

Juriemagic

New Member
Do you get any error messages?
As that code works for me.
no error messages...what I did now was this:

I created a small file with an open code.. then inserted a button with your code above. Upon clicking I get a message saying this: You cannot save this type of file with the selected extension...and then it goes on to prompt I need to change it.....

So, Excel refuses to save it as xlsx.

with all other codes that I have tried, excel saves the file but I cannot open it...your code in my file, does nothing...I'm at the point of giving up...

a last resort for me would be to clear some lines in a sheet module...but with that I am also not successful...I really do not know anymore..
 

Fluff13

Active Member
Ok, that's fine.
If the file is too large, you can strip out the data & formulae as they shouldn't make a difference.
 

Marc L

Excel Ninja
Hi !​
As a reminder, when the file type is present when using the SaveAs method​
the file extension can be omitted so just try without …​
But if the code is within the workbook to 'save as' you must first use SaveCopyAs keeping the original extension​
then open this copy then SaveAs to change the file type then delete the copy …​
 

Juriemagic

New Member
Ok, that's fine.
If the file is too large, you can strip out the data & formulae as they shouldn't make a difference.
Fluff13,

I am going to try Marc L's suggestion, however, I would first like to solve the current approach, it's just easier...
so, having said that, I am sooo close now. I found this code:

Code:
With ThisWorkbook.VBProject.VBComponents("Sheet1")
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End With
This works but only if I unprotect the sheet. So, I manually un-protect the sheet, run the code and it works.
I added an un-protect code line and now I get an error message saying, that the vbproject is protected...

I am looking for code that will unprotect the vbproject now...(sigh)..sorry to go back and forth with this...can you kindly help me?
 

vletm

Excel Ninja
Juriemagic
As Fluff13 has written save the file as an .xlsx file.
>> Why do You want to use VBA if You want to delete code?
Try this way:

1) Open Your file which has VBA-code which You would like to delete
2) From menu ... Select File > SaveAs...
3) Modify name if needed
4) Take care that format is .xlsx
5) Reply to further questions as needed that You can save it as .xlsx ( and same time all VBA-code will delete)
 

Juriemagic

New Member
Juriemagic
As Fluff13 has written save the file as an .xlsx file.
>> Why do You want to use VBA if You want to delete code?
Try this way:

1) Open Your file which has VBA-code which You would like to delete
2) From menu ... Select File > SaveAs...
3) Modify name if needed
4) Take care that format is .xlsx
5) Reply to further questions as needed that You can save it as .xlsx ( and same time all VBA-code will delete)
vletm,

I was about to explain things a bit better. The first thing is that the ribbon is hidden, all "save", insert, delete functionalities are greyed out on the sheet tab menu. this to ensure that certain actions are complied with before the save button becomes active. So, the user cannot save as.

What I have decided was to remove the vbproject code. nobody needs to know that. anycase, I believe the guys are trustworthy enough not to mess with something they know absolutely nothing about. If they do, it will be to their detriment.

So, everything is working fine with the above code provided.

I want to thank everyone who assisted with this thread, but I think let me not waste anyone's time on this anymore...

I DID need to delete code with code..with vbproject safety still intact..but I accept this is maybe not possible. Thanx again to everyone!!
 

vletm

Excel Ninja
Juriemagic
Your I DID need to delete code with code..
... that sounds suicide of code.
You could do that with basic Excel menu ... almost same as Save ...
... the ribbon ... I didn't give ANY steps to use something like that!
 
Top