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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
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
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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.
 

JPDO

Board Regular
Joined
Apr 10, 2002
Messages
140
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.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942

ADVERTISEMENT

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.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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.
 

JPDO

Board Regular
Joined
Apr 10, 2002
Messages
140

ADVERTISEMENT

Thanks a lot DAN an TOM.
It works fine !!!!!!!!!!!!!!
 

Maurizio

Well-known Member
Joined
Oct 15, 2002
Messages
682
Office Version
  1. 2007
Platform
  1. Windows
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
 

d_amaralc

New Member
Joined
Feb 4, 2011
Messages
4
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!
 

hiep hieper

New Member
Joined
Jul 26, 2013
Messages
2
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:

Forum statistics

Threads
1,144,050
Messages
5,722,229
Members
422,417
Latest member
Johhny

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
Top