Delete all VBA with code

Juriemagic · Sep 2, 2019

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.

Fluff13 · Sep 2, 2019

Easiest way is to save the file as an .xlsx file.
That will remove all the code.

herofox · Sep 3, 2019

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 · Sep 4, 2019

Fluff13 said:
Easiest way is to save the file as an .xlsx file.
That will remove all the code.

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..

Juriemagic · Sep 4, 2019

herofox said:

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 · Sep 4, 2019

herofox said:

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 · Sep 4, 2019

Juriemagic said:
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 · Sep 4, 2019

Fluff13 said:
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 · Sep 4, 2019

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 · Sep 4, 2019

Fluff13 said:
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...

Fluff13 · Sep 4, 2019

Do you get any error messages?
As that code works for me.

Juriemagic · Sep 4, 2019

Fluff13 said:
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 · Sep 4, 2019

In that case can you please supply a demo file, with the relevant code?

Juriemagic · Sep 5, 2019

Fluff13 said:
In that case can you please supply a demo file, with the relevant code?

Fluff`13,

I'm not at work today...will send tomorrow. I am not sure the size of the file, hope I will be able to send as is..

Fluff13 · Sep 5, 2019

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 · Sep 5, 2019

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 · Sep 6, 2019

Marc L,

thank you, I am going to look at that....Thank you..

Juriemagic · Sep 6, 2019

Fluff13 said:
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 · Sep 6, 2019

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 · Sep 6, 2019

vletm said:
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!!

Fluff13 · Sep 6, 2019

Glad you've sorted it & thanks for the feedback

vletm · Sep 6, 2019

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!

Delete all VBA with code

New Member

Active Member

Active Member

New Member

New Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

Excel Ninja

New Member

New Member

Excel Ninja

New Member

Active Member

Excel Ninja