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" <(E-Mail Removed)> wrote in message
news:3141FA03-13EC-4372-952C-(E-Mail Removed)...
> 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(?),

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" <(E-Mail Removed)> wrote in message
news:3141FA03-13EC-4372-952C-(E-Mail Removed)...
> 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 here:

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

In article <3141FA03-13EC-4372-952C-(E-Mail Removed)>,
danhattan <(E-Mail Removed)> 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.

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" <(E-Mail Removed)> wrote in message
> news:3141FA03-13EC-4372-952C-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:CD4CD913-D3B8-49CC-AE9D-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:3141FA03-13EC-4372-952C-(E-Mail Removed)...
>> > 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 here:
>
> http://cpearson.com/excel/vbe.htm#DeleteProcedure
>
> In article <3141FA03-13EC-4372-952C-(E-Mail Removed)>,
> danhattan <(E-Mail Removed)> 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.
>

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" <(E-Mail Removed)> wrote in message
> news:3141FA03-13EC-4372-952C-(E-Mail Removed)...
> > 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 here:
> >
> > http://cpearson.com/excel/vbe.htm#DeleteProcedure
> >
> > In article <3141FA03-13EC-4372-952C-(E-Mail Removed)>,
> > danhattan <(E-Mail Removed)> 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 here:
> > >
> > > http://cpearson.com/excel/vbe.htm#DeleteProcedure
> > >
> > > In article <3141FA03-13EC-4372-952C-(E-Mail Removed)>,
> > > danhattan <(E-Mail Removed)> 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
>