Get rid of deleted macros in Excel

  • Thread starter Thread starter donbocce
  • Start date Start date
D

donbocce

I have some Excel files that contain Visual Basic macros. When I
distribute them to other users, I delete the macros and save the file
with a different name. Unfortuantely, when I open the new file, it
still shows Macros as being in the file, even though there are none to
delete. How can I fix this?
 
You need to get rid of the module that contained the macros. Even an empty
module will trigger the macro warning.
 
You need to delete any empty modules that housed those macros.

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you
need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1
Module2
etc etc (You may have just 1 of these)

If you have named your sheets then those names will appear in the brackets above
as opposed to
what you see at the moment in my note.

Right click on the modules and select remove. When prompted with a question re
exporting, just
hit no. Then hit File / Close and return to Microsoft Excel and save the file.
 
Hi ,

I dont know where I found this on the web, but the below macro works
well to remove all code from the workbook, but after running this you
still have to open and do a file save(there is probably a workaround).
After that the users will not see the macros enable/disable prompt.

_________________________________________

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

_________________________

Example : Call RemoveMacros Activeworkbook
 
Back
Top