» Delete all macros in a workbook/document using VBA in Microsoft Excel
VBA macro tip contributed by
Erlandsen
Data Consulting offering Microsoft Excel Application development,
template customization, support and training solutions
When you want to delete all macros from a workbook or document you can use the macro below.
The procedure can be used in both Excel and Word without any editing.
Sub RemoveAllMacros(objDocument As Object)
' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
' requires a reference to the
' Microsoft Visual Basic for Applications Extensibility library
Dim i As Long, l As Long
If objDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objDocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
End Sub
Excel 2003 is not allowing this macros work
Daniel Flores wrote on December 31, 1969 18:00 EST
I test this macro on Excel 97, first I make a copy of a workbook with some VBA code and in the new copy the VBA code is not necessary, so I use the macro submited by Erlandsen Data Consulting and as I said early in Excel 97 it works OK but in Excel 2003 it just does not work.
Any suggetion to delete macros from an Excel 2003 Workbook using VBA code?
On Error
Dale Lavin wrote on December 31, 1969 18:00 EST
I am haveing a problem using On Error in a macro. It works the first time through, but on the second pass it errors out.
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
The Accounting Game : Basic Accounting Fresh from the Lemonade Stand
