Removing VBA-code in THISWORKBOOK

JPDO

Board Regular
Joined
Apr 10, 2002
Messages
140
Dear All,

I have a problem to create a macro that should remove the VBA-code in THISWORKBOOK.
I already tried the information on the site of cpearson.com that handles about "Programming the Visual Basic Editor", but i can not figure out how to get it working on the object THISWORKBOOK.
I also did a search on this forum, but i did not found anything on this.
Can someone help me in this matter ?

Thanks in advance
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi,

This code will delete everything from the ThisWorkbook code module:-

Code:
Sub RemoveCode()
'Remove all code from ThisWorkbook code module
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
        ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines

End Sub

Hopefully the code is self-explanatory. Please post back with any further questions.
 
Upvote 0
Thanks for your answers.
I tried the code of DK but it does not work.
I made a VBA-macro in an XLS-file that should do the following:
1- With GETOPENFILE indicating which file to open
2- Setting the enableevents to FALSE
3- openening the indicated file
4- Here i pasted your REMOVECODE

Nothing happens.
I like to remove the macro coded in ThisWorbook of the in step 3 opened file.
This macro is not one that figures in the modules-window of the VBProject.
It is associated to THISWORKBOOK and it is linked to the workbook_open event.
(Reason why i setted ENABLEEVENTS to False is to prevent its execution by opening)

Can someone figure out what I do wrong ?

Thanks.
 
Upvote 0
The code I posted will delete the code from the workbook that you put my macro in. To delete it from the active workbook change the code to :-

ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines

Include this in your current macro, after the part which opens the file. If you post back make sure you post your code.
 
Upvote 0
JPDO --
dk's code ought to work for you. It worked for me.

Just in case you are having further difficulty though, here is a similar but different line of code that does the same thing:

Sub DeleteThisWorkbookCode()
Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines
End Sub

Also, using this method, you can specify which line(s) of code to delete if you want. The following example shows how to delete lines 1 and 3 of VBA code in the ThisWorkbook module:

Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, 3

Both dk's and mine worked (tested on Excel2000), so between the two of these examples you should be good to go. But, post back if not.
 
Upvote 0
Try that Hans W. Herber suggests:

Sub RemoveAllVBAElements()


Dim vbc As Object
Dim wks As Worksheet
Dim dlg As DialogSheet

With ActiveWorkbook.VBProject
For Each vbc In .VBComponents
Select Case vbc.Type
Case 1, 2, 3
.VBComponents.Remove vbc
Case 100
vbc.CodeModule.DeleteLines 1, vbc.CodeModule.CountOfLines
End Select
Next vbc
End With

'For old MACRO Excel4MacroSheets
Application.DisplayAlerts = False
For Each wks In Excel4MacroSheets
wks.Delete
Next
For Each dlg In DialogSheets
dlg.Delete
Next
Application.DisplayAlerts = True

MsgBox "All program-elements are deleted!", vbExclamation, "Hi, Ciao, Valete!"


End Sub
 
Upvote 0
JPDO --
dk's code ought to work for you. It worked for me.

Just in case you are having further difficulty though, here is a similar but different line of code that does the same thing:

Sub DeleteThisWorkbookCode()
Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines
End Sub

Also, using this method, you can specify which line(s) of code to delete if you want. The following example shows how to delete lines 1 and 3 of VBA code in the ThisWorkbook module:

Application.VBE.ActiveVBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, 3

Both dk's and mine worked (tested on Excel2000), so between the two of these examples you should be good to go. But, post back if not.


Hello!!

Do you have a way to delete macro written on Sheet1 when you open a workbook?

I really need the code above is not working for me. I use Excel 2003.

Tks a lot!
 
Upvote 0
You cannot remove VBA code from a workbook without manuall going in there and doing it. There isn't a method to do this. VBA-code isn't an object that you can reference.

Questions?

http://www.excelquestions.com

Maybe you better pay some attention, instead of submitting bald statements that are incorrect. In Windows and thus Office almost everything is an object and so are the code modules.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top