Delete All Macros
Hi Rob,
You need to set a reference the visual Basic Extensibility library: VBA Menu | Tools | References --- Regards, Norman "Rob" wrote in message ... I have a workbook that contains several macros that manipulate and calculate data for billing to be sent to a client. After all calculations are performed, I will be copying a pasting the worksheets to themselves as values only so that the client will not be able to see the formulas. Afterwards, I would like to delete all the macros as well. I found the following code through the discussion group: 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 However, when I try to run this macro, I get an error message at the very first line that says: Compile Error: User defined type not defined Obviously I would like to know what to change and how to delete all the macros, including the one that is being written to delete the macros. Can anyone help? |
Delete All Macros
Hi Rob,
For additional information, see Chip Pearson's Programming To The Visual Basic Editor page at: http://www.cpearson.com/excel/vbe.htm --- Regards, Norman "Norman Jones" wrote in message ... Hi Rob, You need to set a reference the visual Basic Extensibility library: VBA Menu | Tools | References --- Regards, Norman |
Delete All Macros
Hi Rob,
'------------------ One problem, I am going to be saving this workbook with no macros to different file name. So the next month, I would open the original file with the macros and run those to perform the calculations. When I would finish with this, I would want to delete the macros again. It seems that each time I want to run this macro to delete the modules, I have to set up the reference again. Is there any way to wirte this into the same macro that deletes the modules or is there a way to leave this refernence on. '------------------ If I understand correctly, after adding the reference to the VBA Extensibility library, save the file. --- Regards, Norman |
Delete All Macros
Rob,
You can dispense with the reference to the Extensibility library altogether by declaring the objects As Object, and using the actual constant numbers rather than by their symbolic name: Change Dim VBComp As VBIDE.VBComponent Dim VBComps As VBIDE.VBComponents To Dim VBComp As Object Dim VBComps As Object Change vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule to 1, 3, and 2 repectively. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Rob" wrote in message ... One problem, I am going to be saving this workbook with no macros to different file name. So the next month, I would open the original file with the macros and run those to perform the calculations. When I would finish with this, I would want to delete the macros again. It seems that each time I want to run this macro to delete the modules, I have to set up the reference again. Is there any way to wirte this into the same macro that deletes the modules or is there a way to leave this refernence on. Rob "Norman Jones" wrote: Hi Rob, For additional information, see Chip Pearson's Programming To The Visual Basic Editor page at: http://www.cpearson.com/excel/vbe.htm --- Regards, Norman "Norman Jones" wrote in message ... Hi Rob, You need to set a reference the visual Basic Extensibility library: VBA Menu | Tools | References --- Regards, Norman |
All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com