Can a sub be deleted via macro?

[Guest]

In the event that a subroutine becomes obsolete due to changes in the
spreadsheet data, can the sub be deleted/erased automatically as another sub
detects the reasons for the obsolescence?

If anyone has insight into this, much thanks.

 

[Bob Phillips]

See http://www.cpearson.com/excel/vbe.htm

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernie Deitrick]

Dan(?),

This will remove the macro named RemoveThisCode

Sub RemoveMacro()
' This shows how to remove a subroutine from a specific book / module
Dim myLineStart As Long
Dim myLineCount As Long
On Error GoTo NotFound:
With Application.Workbooks("VBA Code Examples.xls").VBProject.VBComponents("VBAStuff").CodeModule
myLineStart = .ProcBodyLine("RemoveThisCode", vbext_pk_Proc)
myLineCount = .ProcCountLines("RemoveThisCode", vbext_pk_Proc)
.DeleteLines myLineStart, myLineCount
End With
NotFound:
End Sub

This code requires a reference to MS VBA Extensibility - and it may be flagged as a virus and
automatically deleted by many scanning packages.

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

Take a look here:

http://cpearson.com/excel/vbe.htm#DeleteProcedure

 

[Guest]

Thanks, Bernie.

Was going to try this, but don't see any of the following in the list of
References under the Tools menu: Extensibility, MS VBA Extensibility, VBA
Extensibility. Were you possibly referring to something else?

And yes, it is Dan. Used to live in NYC. Thanks again.

 

[Bernie Deitrick]

Dan,

Look for Microsoft Visual Basic for Applications Extensibility. The file is this:

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi JE.

Tried this but it doesn't compile correctly because it doesn't recognize
CodeModule as a variable type. I started to create a variable just to look at
the list of available variable types and don't see CodeModule in there. Is
there another step needed to make this a valid variable type, or should I be
using another variable type altogether?

Thoughts? Thanks for the reference to the link. It looks really useful.

 

[Guest]

Tried this but it doesn't compile correctly because it doesn't recognize
CodeModule as a variable type. I started to create a variable just to look at
the list of available variable types and don't see CodeModule in there. Is
there another step needed to make this a valid variable type, or should I be
using another variable type altogether?

Also, thanks for the reference to the link. It looks really useful and I've
already bookmarked it. And as always, thanks for the help. You're truly
invaluable to those of us trying to learn our way up the ladder.

 

[Dave Peterson]

Start at the top of that web page and read starting with:
"Before using these procedures, you'll need to set a reference in VBA to the VBA
Extensibility library."

There are other restrictions, too.

 

[Guest]

Thanks. That cleared up a lot of questions I had. Very much appreciated.