remove macro

[BruceG]

I work on a network with 150 users with Doc management system. Want to remove
a common macro from all users normal.dot file without disturbing anything
else. Is there a way to create a macro that will just remove the macro?

Thanks

 

[DA]

Hi Bruce

Recently I was playing around with renaming sub routines
and the library that lets you do this can also be applied
to delete code (BTW.. credit and thanks goes to Helmut
W.). I had some instability issues on template files
other than Normal.dot, but you may be OK with this.

To experiment with this code, create a new module under
Normal, and call it "MyModule". Set a reference to the
Microsoft Visual Basic Extensibility library. Paste both
routines as per below. The test sub "MyTestSub()" will
be deleted when you run DeleteMySub().

Make sure you BACK EVERYTHING UP!!!

'------------------------------
Sub DeleteMySub()
Dim aMdl As VBComponent
Dim aPrj As VBProject
Dim lLin As Long
Dim lLin2Del As Long
Dim strSub2Del As String

'Change this string to reflect the
'same name as the procedure you want
'to delete
strSub2Del = "MyTestSub"
Set aPrj = Application.VBE.VBProjects("Normal")
'Specify the module name
Set aMdl = aPrj.VBComponents("MyModule")

With aMdl.CodeModule
iLin = .ProcBodyLine(strSub2Del, vbext_pk_Proc)
iLin2Del = .ProcCountLines(strSub2Del, vbext_pk_Proc)
.DeleteLines iLin, iLin2Del - 1
End With

End Sub
'------------------------------
Sub MyTestSub()
Dim strMyStr As String

strMyStr = "This sub needs to be deleted"
MsgBox strMyStr

End Sub
'------------------------------

Best of luck,
Dennis