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.

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)



"danhattan" wrote in message
...
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.

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.


"Bob Phillips" wrote:

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)



"danhattan" wrote in message
...
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.

Take a look he

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

In article ,
danhattan wrote:

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.

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.


"JE McGimpsey" wrote:

Take a look he

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

In article ,
danhattan wrote:

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.

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.

danhattan wrote:

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.

"JE McGimpsey" wrote:

Take a look he

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

In article ,
danhattan wrote:

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.


--

Dave Peterson

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

"Dave Peterson" wrote:

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.

danhattan wrote:

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.

"JE McGimpsey" wrote:

Take a look he

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

In article ,
danhattan wrote:

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.


--

Dave Peterson

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


"danhattan" wrote in message
...
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.

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" wrote:

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


"danhattan" wrote in message
...
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.

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


"danhattan" wrote in message
...
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" wrote:

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


"danhattan" wrote in message
...
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.