VBA to Delete and Insert a module

dawsona

Board Regular
Joined
Jul 28, 2002
Messages
66
I have 21 files that I need to add the same VBA code (module) to that sits in an existing workbook.

What VBA code do I need to remove the existing module from the 21 workbooks and copy the new module in from another workbook?

I am using Excel 97.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Chip Pearsons site is still my best source for all things but an exists create may be as
Code:
' needs reference to VBE extension
' use like
' dim XModule:set xModule = GetMakeComp("MyNewMod",2)
' vbext_ComponentType   1 std  2 class 3 form    11 activeX 100 Doc
'Function GetMakeComp(ModNa$, Typ As vbext_ComponentType ) As CodeModule
Function GetMakeComp(ModNa$, Optional Typ As vbext_ComponentType = 2) As CodeModule
    Dim Li&, Foundit As Boolean
    While Not Foundit And Li < ActiveWorkbook.VBProject.VBComponents.Count
        Li = Li + 1
        Foundit = ActiveWorkbook.VBProject.VBComponents.Item(Li).Name = ModNa
    Wend
    ' the exit and  OnError methods do  Have to look through all items until Found
    If Foundit Then
        If ActiveWorkbook.VBProject.VBComponents.Item(Li).Type = Typ Then
            Set GetMakeComp = ActiveWorkbook.VBProject.VBComponents.Item(Li).CodeModule
        Else
            MsgBox " A Component with Name " & ModNa & " Exists but is of wrong type"
        End If
    Else
        With ActiveWorkbook.VBProject.VBComponents.Add(Typ)
            .Name = ModNa
            Set GetMakeComp = .CodeModule
        End With
    End If
End Function
 
Upvote 0

Forum statistics

Threads
1,214,322
Messages
6,118,888
Members
448,856
Latest member
Eduard_Stoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top