Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing all code from a Project
Chip Pearson gives the following code on his website to
remove all VBA code in a Project: Private 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 For this to work, I have set a reference to MS Visual Basic For Applications Extensibility 5.3. I'm currently using this with Excel 2000 under Win 2k. This code is in the ThisWorkbook module, and runs under a Workbook Before_Save event where a test is made to determine if the user's work is done. If so, then the 'strip VBA' code runs.and then saves the workbook .....except it doesn't run properly. I get the following mixture of results: 1. It works 2. It closes the workbook without errors but does not delete the code 3. It closes the workbook, then closes excel. 4. I get this error: Compile error: expected End Property If I Ok that message, it asks if I want to save changes to the workbook, so I Cancel. Module1 has been removed and the ThisWorkbook module is empty. Can anyone point to my errors, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing all code from a Project
May have found my error.
When the test to strip code is met, I 'call' the routine (ie I had placed it in another sub). Have now placed it 'line for line' in the Before_Save event code, and it seems to work ....although when the newly-saved book is reopened, then the macro warning message is displayed. Not sure if this has solved everything. If so, can I avoid that warning message? Regards. "Stuart" wrote in message ... Chip Pearson gives the following code on his website to remove all VBA code in a Project: Private 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 For this to work, I have set a reference to MS Visual Basic For Applications Extensibility 5.3. I'm currently using this with Excel 2000 under Win 2k. This code is in the ThisWorkbook module, and runs under a Workbook Before_Save event where a test is made to determine if the user's work is done. If so, then the 'strip VBA' code runs.and then saves the workbook ....except it doesn't run properly. I get the following mixture of results: 1. It works 2. It closes the workbook without errors but does not delete the code 3. It closes the workbook, then closes excel. 4. I get this error: Compile error: expected End Property If I Ok that message, it asks if I want to save changes to the workbook, so I Cancel. Module1 has been removed and the ThisWorkbook module is empty. Can anyone point to my errors, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing all code from a Project
Haven't tested it, but is the code that is doing the deleting getting
deleted before it finishes? Try testing for the ThisWorkbook code module, and bypassing that delete, and then specifically delete that at the end. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Chip Pearson gives the following code on his website to remove all VBA code in a Project: Private 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 For this to work, I have set a reference to MS Visual Basic For Applications Extensibility 5.3. I'm currently using this with Excel 2000 under Win 2k. This code is in the ThisWorkbook module, and runs under a Workbook Before_Save event where a test is made to determine if the user's work is done. If so, then the 'strip VBA' code runs.and then saves the workbook ....except it doesn't run properly. I get the following mixture of results: 1. It works 2. It closes the workbook without errors but does not delete the code 3. It closes the workbook, then closes excel. 4. I get this error: Compile error: expected End Property If I Ok that message, it asks if I want to save changes to the workbook, so I Cancel. Module1 has been removed and the ThisWorkbook module is empty. Can anyone point to my errors, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing all code from a Project
Many thanks.
I think my follow-up post may indicate that I was 'jumping' between modules, which doesn't seem very logical when I think about it <g. Regards. "Bob Phillips" wrote in message ... Haven't tested it, but is the code that is doing the deleting getting deleted before it finishes? Try testing for the ThisWorkbook code module, and bypassing that delete, and then specifically delete that at the end. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Chip Pearson gives the following code on his website to remove all VBA code in a Project: Private 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 For this to work, I have set a reference to MS Visual Basic For Applications Extensibility 5.3. I'm currently using this with Excel 2000 under Win 2k. This code is in the ThisWorkbook module, and runs under a Workbook Before_Save event where a test is made to determine if the user's work is done. If so, then the 'strip VBA' code runs.and then saves the workbook ....except it doesn't run properly. I get the following mixture of results: 1. It works 2. It closes the workbook without errors but does not delete the code 3. It closes the workbook, then closes excel. 4. I get this error: Compile error: expected End Property If I Ok that message, it asks if I want to save changes to the workbook, so I Cancel. Module1 has been removed and the ThisWorkbook module is empty. Can anyone point to my errors, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing all code from a Project
Actually, as soon as I posted it I knew it was a daft answer, as the code
will load into memory at the start, and the deletes will be to the physical workbook, so it won't impact at all. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Many thanks. I think my follow-up post may indicate that I was 'jumping' between modules, which doesn't seem very logical when I think about it <g. Regards. "Bob Phillips" wrote in message ... Haven't tested it, but is the code that is doing the deleting getting deleted before it finishes? Try testing for the ThisWorkbook code module, and bypassing that delete, and then specifically delete that at the end. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Chip Pearson gives the following code on his website to remove all VBA code in a Project: Private 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 For this to work, I have set a reference to MS Visual Basic For Applications Extensibility 5.3. I'm currently using this with Excel 2000 under Win 2k. This code is in the ThisWorkbook module, and runs under a Workbook Before_Save event where a test is made to determine if the user's work is done. If so, then the 'strip VBA' code runs.and then saves the workbook ....except it doesn't run properly. I get the following mixture of results: 1. It works 2. It closes the workbook without errors but does not delete the code 3. It closes the workbook, then closes excel. 4. I get this error: Compile error: expected End Property If I Ok that message, it asks if I want to save changes to the workbook, so I Cancel. Module1 has been removed and the ThisWorkbook module is empty. Can anyone point to my errors, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing all code from a Project
Doesn't seem daft at all ... least not when compared
to how I was trying to run the code. Maybe I've fixed it. We'll see. Regards. "Bob Phillips" wrote in message ... Actually, as soon as I posted it I knew it was a daft answer, as the code will load into memory at the start, and the deletes will be to the physical workbook, so it won't impact at all. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Many thanks. I think my follow-up post may indicate that I was 'jumping' between modules, which doesn't seem very logical when I think about it <g. Regards. "Bob Phillips" wrote in message ... Haven't tested it, but is the code that is doing the deleting getting deleted before it finishes? Try testing for the ThisWorkbook code module, and bypassing that delete, and then specifically delete that at the end. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Stuart" wrote in message ... Chip Pearson gives the following code on his website to remove all VBA code in a Project: Private 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 For this to work, I have set a reference to MS Visual Basic For Applications Extensibility 5.3. I'm currently using this with Excel 2000 under Win 2k. This code is in the ThisWorkbook module, and runs under a Workbook Before_Save event where a test is made to determine if the user's work is done. If so, then the 'strip VBA' code runs.and then saves the workbook ....except it doesn't run properly. I get the following mixture of results: 1. It works 2. It closes the workbook without errors but does not delete the code 3. It closes the workbook, then closes excel. 4. I get this error: Compile error: expected End Property If I Ok that message, it asks if I want to save changes to the workbook, so I Cancel. Module1 has been removed and the ThisWorkbook module is empty. Can anyone point to my errors, please? Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 27/06/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 28/06/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code to check for project references in VBA | Excel Programming | |||
unlock project with VBA code | Excel Programming | |||
Discussion: VBA Project Code Protection | Excel Programming | |||
inserted code into project but can't run it in run dialogbox | Excel Programming |