CODEWITHSUNDEEP
excelvba
Seth Reuter
Seth Reuter

Reputation: 85

Programmatically deleting all the code in my workbook with a macro

I need to delete all the code in my workbook with a macro. I'm using this code, which seems pretty old.

Dim x As Integer
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

I'm getting an error at .VBComponents.Remove .VBComponents(x), where visual basic says, "Run-time error '5': Invalid procedure call or argument." According to this page, this error means that either I'm using my procedure wrong, or this procedure doesn't exist anymore.

How can I fix this macro and make it work with Office 2016?

Upvotes: 1

Views: 1518

Answers (2)

Seth Reuter
Seth Reuter

Reputation: 85

Like sous2187 said, the best method is save the file as a nonmacro file, and let excel remove the macros itself. So I did exactly that.

Sub delhiddensheets()

    For Each sh In Worksheets
        If sh.Visible = xlSheetHidden Then
            sh.Delete
        End If
    Next sh

End Sub

Sub Valuepaste()

    Dim tabs As Object
    For Each tabs In Sheets
        With tabs
            If .Visible = True Then .Select Replace:=False
        End With
    Next tabs
    Cells.Select
    Range("A1").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A20").Select

End Sub

Sub DeleteAllCode()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False

    newname = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1)) & "_VALS.xlsx"
    ChDir ActiveWorkbook.Path
    ActiveWorkbook.SaveAs Filename:=newname, FileFormat:=xlOpenXMLWorkbook
    Valuepaste
    delhiddensheets

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True

End Sub

Upvotes: 2

Andrzej O
Andrzej O

Reputation: 66

The reason for an error is not due to code being old :) :P The reason is that you're deleting all modules ...including the one with the "DeleteAllModules" :P ups. btw just in case you need set referencest to Microsoft Visual Basic for Applications Extensibility 5.3 and set security to "un safe"

for details goto https://www.google.pl/amp/s/christopherjmcclellan.wordpress.com/2014/10/10/vba-and-git/amp/

but just for quick fix

Option Explicit
'@Folder("DevTools") 

Const devTools As String = "devTools" 
'This is the name of module with "RemoveAllModules" and it will be ignored

Private Sub RemoveAllModules()
Dim comp As VBComponent

 For Each comp In Application.VBE.ActiveVBProject.VBComponents
  If comp.Type = vbext_ct_ClassModule Or comp.Type = vbext_ct_StdModule Then
        If Not comp.name = devTools Then
            Application.VBE.ActiveVBProject.VBComponents.Remove comp
        End If
    End If
 Next

End Sub

Upvotes: 3

Related Questions