How to program a code to die

davidm · #1 11-02-2005, 06:14 AM

There are a few instances when, for good reason, I need to have a code to "expire" after a certain date. I have resorted to a workaround using Worksheet_Open event which immobilizes the code at a set date. But surely, there must be a nifty way of going about it-perhaps using Application.Kill ... but I can't get my head around it. I appreciate any help.

davidm · #2 11-03-2005, 02:22 AM

I am following up on my post which seems to have slipped out of traffic. Thanks in advance of any help.

David

#3 11-03-2005, 05:45 AM

Hi davidm, this may be something you are looking for..found after a
quick search
of this ng.... Be careful with it... it will delete all the code you
have written.

Option Explicit
Public Sub DeleteAllVBA()
Dim vbComp As Object
For Each vbComp In ActiveWorkbook.VBProject.VBComponents
With vbComp
If .Type = 100 Then
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
Else
ActiveWorkbook.VBProject.VBComponents.Remove vbComp
End If
End With
Next vbComp
End Sub

Public Sub DeleteAllVBA()
For Each book In Workbooks
If book.Name <> ThisWorkbook.Name Then
Dim vbComp As Object
For Each vbComp In ActiveWorkbook.VBProject.VBComponents
With vbComp
If .Type = 100 Then
..CodeModule.DeleteLines 1, .CodeModule.CountOfLines
Else
ActiveWorkbook.VBProject.VBComponents.Remove vbComp
End If
End With
Next vbComp
End If
Next book
End Sub

Here'a another one you may want to play with, as you can pick the
module to delete

Sub deletemodule()

MsgBox "place your code here"

With ThisWorkbook.VBProject.VBComponents
.Remove .Item("Module1")
End With
End Sub

Hope these help

ste

davidm wrote:
> I am following up on my post which seems to have slipped out of traffic.
> Thanks in advance of any help.
>
> David
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=480988

davidm · #4 11-04-2005, 02:05 AM

Thanks Mac.

Unfortunately, the idea is not just to delete the code. I need the procedure to self-destruct after a specified date.

I have since my last post been able to come up with the following which serves the purpose. (The message boxes are for debugging only and may not be required in actual application).

Sub CodeToExpireAfterDateA()
Dim x As Date

x = #11/05/2008#
If x > Now() Then
MsgBox "Code is alive" 'optional
Call TimedCode
Else
MsgBox "Code has passed used-by date" 'optional
Exit Sub

End If

End Sub

The routine of course returns "Code is alive" in this instance and calls up "TimedCode". But run after midnight of 11/05/2008, the 2nd message should be returned and the procedure exited.

David

David

Welcome to the Excel Forum

Forum Statistics