Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting code from a macro (by a macro)
Hi there
I want to delete some code from a PRIVATE SUB in the sheet code after it has been used. I copied the following code from Chip Pearson's web site but it comes up with an error (it doesn't seem to like VBIDE very much). I have checked the trust VB code checkbox in security options. Sub DeleteAllVBA() Dim VBComp As VBIDE.VBComponent Dim VBComps As VBIDE.VBComponents Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, _ vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub My preference would be just to delete the code rather than the whole VB Project, but it doesn't really matter. My understanding is that you can't delete code from a Private sub anyway Sub DeleteAllCodeInModule() Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Worksheet_Cha nge").CodeModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With 'Private Sub Worksheet_Change End Sub This doesn't work either. Can someone help please? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting code from a macro (by a macro)
I think you missed this paragraph from Chip's page (near the top):
Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library. In the VBA editor, go to the Tools menu, choose the References item, and put a check next to "Microsoft Visual Basic For Applications Extensibility" library. This enables VBA to find the definitions of these objects. If you are using Excel97, this library will appear in the References list without a version number: "Microsoft Visual Basic For Applications Extensibility". If you are using Excel 2000 or later, it will appear with a version number: "Microsoft Visual Basic For Applications Extensibility 5.3". It is very important that you reference the proper library. If you reference the wrong library, you will receive "Type Mismatch" errors. If you don't reference the extensibility library at all, you will receive "User Defined Type Not Defined Error" messages. Brettjg wrote: Hi there I want to delete some code from a PRIVATE SUB in the sheet code after it has been used. I copied the following code from Chip Pearson's web site but it comes up with an error (it doesn't seem to like VBIDE very much). I have checked the trust VB code checkbox in security options. Sub DeleteAllVBA() Dim VBComp As VBIDE.VBComponent Dim VBComps As VBIDE.VBComponents Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, _ vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub My preference would be just to delete the code rather than the whole VB Project, but it doesn't really matter. My understanding is that you can't delete code from a Private sub anyway Sub DeleteAllCodeInModule() Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Worksheet_Cha nge").CodeModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With 'Private Sub Worksheet_Change End Sub This doesn't work either. Can someone help please? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting code from a macro (by a macro)
Hi Dave, I think you might be right!. Thankyou for that.
Regards, Brett "Dave Peterson" wrote: I think you missed this paragraph from Chip's page (near the top): Before using these procedures, you'll need to set a reference in VBA to the VBA Extensibility library. In the VBA editor, go to the Tools menu, choose the References item, and put a check next to "Microsoft Visual Basic For Applications Extensibility" library. This enables VBA to find the definitions of these objects. If you are using Excel97, this library will appear in the References list without a version number: "Microsoft Visual Basic For Applications Extensibility". If you are using Excel 2000 or later, it will appear with a version number: "Microsoft Visual Basic For Applications Extensibility 5.3". It is very important that you reference the proper library. If you reference the wrong library, you will receive "Type Mismatch" errors. If you don't reference the extensibility library at all, you will receive "User Defined Type Not Defined Error" messages. Brettjg wrote: Hi there I want to delete some code from a PRIVATE SUB in the sheet code after it has been used. I copied the following code from Chip Pearson's web site but it comes up with an error (it doesn't seem to like VBIDE very much). I have checked the trust VB code checkbox in security options. Sub DeleteAllVBA() Dim VBComp As VBIDE.VBComponent Dim VBComps As VBIDE.VBComponents Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, _ vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub My preference would be just to delete the code rather than the whole VB Project, but it doesn't really matter. My understanding is that you can't delete code from a Private sub anyway Sub DeleteAllCodeInModule() Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Worksheet_Cha nge").CodeModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With 'Private Sub Worksheet_Change End Sub This doesn't work either. Can someone help please? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting a macro completely | Excel Discussion (Misc queries) | |||
Deleting rows with macro | Excel Worksheet Functions | |||
Deleting Row Macro | Excel Worksheet Functions | |||
Deleting a macro | Excel Discussion (Misc queries) | |||
Macro is deleting everything! | Excel Discussion (Misc queries) |