VBA TUTOR NEWSLETTER ~ TUTORIAL ELEVEN: USING VBA to DELETE MACROS

>GRSoftware >VBA Tutor >Newsletter Tutorials >Tutorial 10 >Tutorial 12

VBA TUTOR NEWSLETTER ~ TUTORIAL ELEVEN:

Using VBA to delete macros (a "run once" AutoOpen macro)

The following VBA code which removes all code from an excel workbook was developed by a VBA Tutor Newsletter reader, Lachlan Miller. Miller.Lachlan@saugov.sa.gov.au

Lachlan's problem was to run a macro on the "workbook_open" event that was to collect various info from the user and set up the workbook accordingly. Basically, a "run once" macro.

Be aware that this works very well and will remove ALL code in your workbook. So, take care if you are using this on a large project.

Lachlan suggests ... "calling this as the last thing to be called from your workbook_open event. Its a bit tough to get it into the workbook close event because it removes itself as you exit. You will need to make your template read only or it will remove the code from the template as well as the resulting workbook. If you need to get help on some of these objects you will need to go to tool_references and select MS Visual Basic for applications extensibility."

In a default workbook the code modules are indexed as follows ..

thisWorkbook(1)
sheet1(2)
sheet2(3)
sheet3(4)

Each subsequent module, class or worksheet you add, by default, stay in the order you add them.

THE CODE! (For Excel)

Sub removeAllCode()
Dim awi 'activeWorkbookItem(index)
Dim awcl As Integer 'activeWorkbook Component CountOfLines
Dim count As Integer 'how many potential code modules
Dim i As Integer 'loop counter
On Error Resume Next
count = ActiveWorkbook.VBProject.VBComponents.count
For i = 1 To count
Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i)
awcl = awi.CodeModule.CountOfLines
awi.CodeModule.DeleteLines 1, awcl
Next i
Set awi = Nothing ' Release the object
End Sub

THE CODE! (For Word)

Taking the code sent by Lachlan, it can be easily altered to work in a WORD document.

In a default document the code modules are indexed as follows ..

ThisDocument(1)

Then each subsequent module you add, by default, stay in the order you add them.

Module1(2)
Module2(3)
etc.

The following code will delete a "AutoOpen" macro contained in the "ThisDocument" module.

Sub AutoOpen()
' **** rest of AutoOpen code goes here ****
Dim awi
Dim awcl As Integer
On Error Resume Next
Set awi = ActiveDocument.VBProject.VBComponents.Item(1)
awcl = awi.CodeModule.CountOfLines
awi.CodeModule.DeleteLines 1, awcl
Set awi = Nothing ' Release the object
End Sub

NOTE [ 1 ]: You can still run an "AutoOpen" macro later by placing a permanent one in "Module1" (or whatever). Word will run the temporary "AutoOpen" macro in the "ThisDocument" module first, ignoring the permanent one. After it has been deleted and the document saved and exited, the next time the document is opened, Word will run the first "AutoOpen" it finds, working its way through the modules in order.

NOTE [ 2 ]: In Excel, it is Auto_Open().

I would like to thank Lachlan for sharing this excellent code with us.

HOPEFULLY, THESE CODE EXAMPLES WILL ADD TO YOUR ABILITY TO BECOME A VBA POWER USER!