Hi
Defining Objects
VB Code:
'VBProject
Dim VBProj As VBProject
Set VBProj = ThisWorkbook.VBProject
'VBComponent
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents("Module1")
'CodeModule
Dim VBCodeMod As CodeModule
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
Importing A Module To A Workbook
There isn't a single method to copy modules from one VBProject to another. Instead, you have to export the module from one project, and then import it into another. The following procedure will copy Module1 from Book2 to Book1.
VB Code:
Sub CopyOneModule()
Dim FName As String
With Workbooks("Book2")
FName = .Path & "\code.txt"
.VBProject.VBComponents("Module1").Export FName
End With
Workbooks("book1").VBProject.VBComponents.Import FName
End Sub
Just change "Module1" to the name of the module you want to copy. If you want to copy all modules (except the ThisWorkbook and Sheet modules), you can use the following code.
VB Code:
Sub CopyAllModules()
Dim FName As String
Dim VBComp As VBIDE.VBComponent
With Workbooks("Book2")
FName = .Path & "\code.txt"
If Dir(FName) <> "" Then
Kill FName
End If
For Each VBComp In .VBProject.VBComponents
If VBComp.Type <> vbext_ct_Document Then
VBComp.Export FName
Workbooks("book1").VBProject.VBComponents.Import FName
Kill FName
End If
Next VBComp
End With
End Sub
Removing/Deleting A Procedure From A Module
The procedure below will delete the procedure called "MyNewProcedure" from the module named "NewModule" in ThisWorkbook.
VB Code:
Sub DeleteProcedure()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
StartLine = .ProcStartLine("MyNewProcedure", vbext_pk_Proc)
HowManyLines = .ProcCountLines("MyNewProcedure", vbext_pk_Proc)
.DeleteLines StartLine, HowManyLines
End With
End Sub
Removing/Deleting All Code From A Module
The procedure below will delete all code from a module name "NewModule".
VB Code:
Sub DeleteAllCodeInModule()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With
End Sub
Hope this helps....