Code that removes other code from project (Excel) 1

[kylegjordan]

Is it possible to write a procedure that removes specific code/other procedures within the same project when called or run?

I'm designing a tool in Excel for my sales team that has functionality for use by our reps, but won't be used by the client to whom it is sent. Before it is emailed to the client, I would like to install a finalize button that, when clicked, would delete most of the procedures within my project. This would help to shrink the file down to a size reasonable for emailing through 56k lines.

Any and all advice welcome. Thanks

kj

 

[segmentationfault]

I'm not sure that's possible, but you could move all the modules to a separate workbook and set up both files to refer to each other. You'd have all the functionality you have now, but the VBA modules would never be part of the file in the first place.

Communication isn't tough to do between the two workbooks. The client book just needs to know if the module workbook is opened, and if so, ask for the macros to be run, otherwise act like a normal file.

 

[segmentationfault]

I spoke too soon. Obviously this will remove "Module1". Put in more tests to remove more modules.

Private Sub KillModules()

Dim o
For Each o In ThisWorkbook.VBProject.vbcomponents
Debug.Print o.Name
If o.Name = "Module1" Then VBProject.vbcomponents.Remove (o)
Next

End Sub

 

[kylegjordan]

thanks segmentationfault. i will definitely test this out.

 

[mal2ty]

This functionality would be quite useful to me also. I tried implementing the code, but I received the following error:

Run-time error '424':
Object required

I am still playing with the code to try and figure out how to fix the error, but if there are any more suggestions, please post them.

Thanks

 

[kylegjordan]

i think you have to have certain add-ins, which i can't locate. vbcomponent object; codemodule object; vbproject object are add-ins that are probably necessary. if anyone knows how to access these, please let me know.

kj

 

[mal2ty]

Here it is... This code will remove a module from a VBA project:

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Function KillMacros()
Dim intCount As Integer
Dim intComponentNum As Integer
intCount = 1
intComponentNum = ThisWorkbook.VBProject.VBComponents.Count
Do While intCount <= intComponentNum
Debug.Print Application.VBE.ActiveVBProject.VBComponents(intCount).Name
Select Case Application.VBE.ActiveVBProject.VBComponents(intCount).Name
Case &quot;Main&quot;, &quot;Module1&quot;
Application.VBE.ActiveVBProject.VBComponents.Remove Application.VBE.ActiveVBProject.VBComponents(intCount)
intComponentNum = ThisWorkbook.VBProject.VBComponents.Count
intCount = intCount - 1
End Select
intCount = intCount + 1
Loop
End Function

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

A restriction on this code is that it will not remove the module in which it is placed. If anyone knows how to do that, please let me know.