MrExcel Publishing
Your One Stop for Excel Tips & Solutions

code that cleans up after itself...


Posted by rm on December 24, 2001 1:47 PM

...is there a way for a vba routine to run only once then delete itself from the "ThisWorkbook" object?

I am creating a template that when launched, will create a new pre-designed workbook for the user, run a vba routine, then save as a default name based on a cell's value...the above problem is the last hurdle to complete this project...many thanks in advance...rm


Posted by Jacob on December 24, 2001 1:58 PM

I dont think that code can delete itself, but this workaround will work.

sub

if sheets("SomeSheet").range("SomeRange") = 1 then

your code here


end of your code

sheets("SomeSheet").Range("SomeRange").formulaR1C1= 0

else
end if

end sub

Put 1 in the range you refer to then when the code runs it will change it to a 0 so the code will never trigger again since the If statement will always be false.


Hope this helps

Jacob

Posted by Gary Bailey on December 24, 2001 2:02 PM

Say if you want to call your book "xyz" then the value in A1 try

thisworkbook.saveas "xyz" & range("a1").value

Replacing Range("a1") with wherever your filename is. You could use a named range for clarity.

Gary

Posted by Gary Bailey on December 24, 2001 2:17 PM

I'm not so sure you can't get code to delete itself. Try

Dim objVBP As VBProject
Dim objVBComp As VBComponent

Set objVBP = ActiveWorkbook.VBProject

For Each objVBComp In objVBP.VBComponents
objVBComp.CodeModule.DeleteLines 1, objVBComp.CodeModule.CountOfLines
Next objVBComp

Gary

Posted by Gary Bailey on December 24, 2001 2:24 PM

SORRY FORGOT

I forgot. You'll need a reference to the VB IDE object library for my last post to work.

Tools->References

Microsoft VBA Extensibility library (or something like that!)

Sorry

Gary

Posted by Ivan F Moala on December 24, 2001 3:06 PM

Yes...you actually can cleanup code......BUT you
have to be real careful. The code you gave is a start.....then you have to delete all code in
worksheets + Userforms


Ivan I'm not so sure you can't get code to delete itself. Try Dim objVBP As VBProject

Posted by Gary Bailey on December 24, 2001 3:15 PM

The code below deleted all code behind worksheets and userforms when I tried it. Did it behave differently on your machine?

I guess the main thing is - do it as late as possible and don't call any procedures for the first time after done the deleting.

Merry Xmas

Gary Yes...you actually can cleanup code......BUT you

Posted by Ivan F Moala on December 24, 2001 3:23 PM

Gary
Yes it deletes ALL code but leaves the Modules
and useforms in place....so when loading the
file up it will comeup with enable macros etc.

Have a merry Xmas Gary


Ivan The code below deleted all code behind worksheets and userforms when I tried it. Did it behave differently on your machine? I guess the main thing is - do it as late as possible and don't call any procedures for the first time after done the deleting. Merry Xmas Gary : Yes...you actually can cleanup code......BUT you

Posted by rm on December 24, 2001 7:18 PM

...mahalo! (thank you)

...big thanks to all...have a great holiday!
rm Gary