An XLA or COM add-in is the preferred method for this type of
situation. The data reside in XLS workbooks and all the code, user
interface, etc, resides in the add-in. When a change of code is
required, you just send out a new add-in, thus not disturbing any data
workbooks.
However, if you do need to replace a code module in a workbook, the
best way is to create a workbook that contains the new version of the
module and code to replace the old module with the new module. Create
a workbook named ModuleTransfer.xls and insert two code modules it the
VBProject. The first module in this workbook is the module that is to
be replaced in the other workbooks. The next module contains the code
to transfer the module. It can have any name. In the code below,
change REPLACE_MODULE_NAME to the name of the module that is to be
removed and replaced.
You would then distribute the ModuleTransfer.xls workbook to all the
users with a big fat button on the first sheet to kick off the
UpdateModule procedure.
''''''''''''''''''''''''''''''
' BEGIN CODE
''''''''''''''''''''''''''''''
Sub UpdateModule()
Dim WB As Workbook
Dim FName As Variant
Dim ModFileName As String
Dim N As Long
Const REPLACE_MODULE_NAME = "modSomeModule" '<<<<< CHANGE
ModFileName = ThisWorkbook.Path & "\" & REPLACE_MODULE_NAME & ".bas"
ThisWorkbook.VBProject.VBComponents(REPLACE_MODULE_NAME).Export _
Filename:=ModFileName
FName = Application.GetOpenFilename( _
filefilter:="Excel Files,*.xls;*.xlsx;*.xlsm;*.xlsb", _
MultiSelect:=True)
If IsArray(FName) Then
For N = LBound(FName) To UBound(FName)
Set WB = Workbooks.Open(FName(N))
With WB.VBProject.VBComponents
On Error Resume Next
Err.Clear
If WB.VBProject.Protection = vbext_pp_none Then
.Remove .Item(REPLACE_MODULE_NAME)
If Err.Number = 0 Then
.Import ModFileName
End If
End If
End With
WB.Close savechanges:=True
Next N
Else
Exit Sub
End If
Kill ModFileName
End Sub
''''''''''''''''''''''''''''''
' END CODE
''''''''''''''''''''''''''''''
This code exports REPLACE_MODULE_NAME from this workbook to a text
file located in the same folder as this workbook. Then, it prompts the
user for any number of workbooks whose modules are to be replaced. It
removes REPLACE_MODULE_NAME from the workbook and imports the module
from the temp file. Finally, after all the workbooks have been
processed, it Kills the temp file.
Note that the VBProject must not be locked. If so, it is skipped and
the code module will not be replaced.
You can find tons more information about working with the VBA editor
objects and the code objects of workbooks at
www.cpearson.com/Excel/VBE.aspx
Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com