Copy modules from one workbook to another using VBA in Microsoft Excel

With the macro below you can copy modules from one workbook to anaother:

Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
Workbooks("Book2.xls")
Dim strFolder As String, strTempFile As String
strFolder = SourceWB.Path
If Len(strFolder) = 0 Then strFolder = CurDir
strFolder = strFolder & "\"
strTempFile = strFolder & "~tmpexport.bas"
On Error Resume Next
SourceWB.VBProject.VBComponents(strModuleName).Exp ort strTempFile
TargetWB.VBProject.VBComponents.Import strTempFile
Kill strTempFile
On Error GoTo 0
End Sub

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Create a new module using VBA in Microsoft Excel

With the macro below you can create a new module in a workbook:

Sub CreateNewModule(ByVal wb As Workbook, _
ByVal ModuleTypeIndex As Integer, ByVal NewModuleName As String)
' creates a new module of ModuleTypeIndex
' (1=standard module, 2=userform, 3=class module) in wb
' renames the new module to NewModuleName (if possible)
Dim VBC As VBComponent, mti As Integer
Set VBC = Nothing
mti = 0
Select Case ModuleTypeIndex
Case 1: mti = vbext_ct_StdModule ' standard module
Case 2: mti = vbext_ct_MSForm ' userform
Case 3: mti = vbext_ct_ClassModule ' class module
End Select
If mti <> 0 Then
On Error Resume Next
Set VBC = wb.VBProject.VBComponents.Add(mti)
If Not VBC Is Nothing Then
If NewModuleName <> "" Then
VBC.Name = NewModuleName
End If
End If
On Error GoTo 0
Set VBC = Nothing
End If
End Sub

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Delete a procedure from a module using VBA in Microsoft Excel

With the macro below you can delete an existing procedure from a module:

Sub DeleteProcedureCode(ByVal wb As Workbook, _
ByVal DeleteFromModuleName As String, ByVal ProcedureName As String)
' deletes ProcedureName from DeleteFromModuleName in wb
Dim VBCM As CodeModule, ProcStartLine As Long, ProcLineCount As Long
On Error Resume Next
Set VBCM = wb.VBProject.VBComponents(DeleteFromModuleName).Co deModule
If Not VBCM Is Nothing Then
' determine if the procedure exist in the codemodule
ProcStartLine = 0
ProcStartLine = VBCM.ProcStartLine(ProcedureName, vbext_pk_Proc)
If ProcStartLine > 0 Then ' prosedyren finnes, slett den
ProcLineCount = VBCM.ProcCountLines(ProcedureName, vbext_pk_Proc)
VBCM.DeleteLines ProcStartLine, ProcLineCount
End If
Set VBCM = Nothing
End If
On Error GoTo 0
End Sub

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Delete all macros in a workbook/document using VBA in Microsoft Excel

When you want to delete all macros from a workbook or document you can use the macro below.
The procedure can be used in both Excel and Word without any editing.

Sub RemoveAllMacros(objDocument As Object)
' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
' requires a reference to the
' Microsoft Visual Basic for Applications Extensibility library
Dim i As Long, l As Long
If objDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objDocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
End Sub

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Delete module content using VBA in Microsoft Excel

t's not possible to delete all kinds of modules, you can't delete the codemodules for worksheets,
charts and ThisWorkbook. In these modules you have to delete the content instead of the module itself:

Sub DeleteModuleContent(ByVal wb As Workbook, _
ByVal DeleteModuleName As String)
' deletes the contents of DeleteModuleName in wb
' use this if you can't delete the module
On Error Resume Next
With wb.VBProject.VBComponents(DeleteModuleName).CodeMo dule
.DeleteLines 1, .CountOfLines
End With
On Error GoTo 0
End Sub

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Insert a new module from a file using VBA in Microsoft Excel

With the macro below you can easily insert new modules with
contents in a workbook.
This requires that you have created the new module previously in another (temporary) workbook.
Export the finished module to a text file by rightclicking the module name and select Export file... in the shortcut menu.

Sub InsertVBComponent(ByVal wb As Workbook, ByVal CompFileName As String)
' inserts the contents of CompFileName as a new component in wb
' CompFileName must be a valid VBA component suited for
' import (an exported VBA component)
If Dir(CompFileName) <> "" Then
' source file exist
On Error Resume Next ' ignores any errors if the project is protected
wb.VBProject.VBComponents.Import CompFileName
' inserts component from file
On Error GoTo 0
End If
Set wb = Nothing
End Sub

__________________
V.Vadivelan

There never a wrong time to do the right thing.

Class modules using VBA in Microsoft Excel

A class is the formal definition of an object. The class is a template for the creation of the object during programming,
and defines the properties and methods that decides how the object behaves. Class modules is new in Office97.
With class modules it's possible to create your own objects. These objects can have properties and methods like
the built-in objects, and you can create several copies of an object if you need to.
Programmers have managed without class modules earlier, and the reason for using class modules
may not become obvious before you have used class modules for a while. Here are some of the
advantages when using class modules :

* Class modules makes it possible to separate complicated source code for advanced processes.
This makes it easier for other to use the source code without understanding how the process is performed.
* Class modules makes the development of complicated tasks easier by breaking up the code in smaller
and easier manageable parts. This has been possible earlier, but a class module forces you to separate
the code from the ordinary procedures, resulting in a more obvious function separation.
* Class modules let you create reusable components. Because of the obvious separation between
classes and the procedures using them, the classes contains independent code components that
can easily be shared between different projects.
* Class module sis the foundation of other component technologies, Visual Basic can be used to
create X Automation servers and ActiveX controls.

When to use class modules

Here are some examples on when you would want to use class modules :

* Database management
Class modules makes it easier to create objects that can manage a database with VBA. You can
create a class that contains code for reading or writing to a database table.
This class can be used in your macro without the user knowing how and where the data comes from.
* Wrapping in API procedures
Class modules makes it easier to use Windows API-functions in your macros.
Wrapping the API-functions in a class module makes the development easier for people that are not
that familiar with the more complicated function in Windows.
* Managing reading and writing to text files
This is really not that complicated, but who goes around and remember how this is done in VBA?
By creating a class that manage low level reading to and writing from a text file, the properties and methods in the class can easily be used in your macros.


Class modules can also be used to simplify Registry management, and to create new properties and methods to objects in the applications.

__________________
V.Vadivelan

There never a wrong time to do the right thing.