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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

HarryS

Board Regular
Joined
May 2, 2008
Messages
202
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,492
Members
417,028
Latest member
JFCLUK

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
Top