数え切れないほどのマクロを使用していて、マクロで現在のExcelブックから既存のコードをすべて消去したい場合は、次の記事がきっと役に立ちます。

ブックまたはドキュメントからすべてのマクロを削除する場合は、以下のマクロを使用できます。

この手順は、編集せずにExcelとWordの両方で使用できます。

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