Replacing a Module with an Updated Version

The example in this section demonstrates how to replace a VBA module with a different VBA module. Besides demonstrating three VBComponent methods ( Export , Remove , and Import ), the procedure also has a practical use. For example, you might distribute a workbook to a group of users and then later discover that a macro contains an error or needs to be updated. Because the users could have added data to the workbook, it's not practical to replace the entire workbook. The solution, then, is to distribute another workbook that contains a macro that replaces the VBA module with an updated version stored in a file.

This example consists of two workbooks:

•  UserBook.xlsm : Contains a module ( Module1 ) that needs to be replaced .

•  UpdateUserBook.xlsm : Contains VBA procedures to replace Module1 in  UserBook.xlsm with a later version of Module1 (which is stored in  UpdateUserBook.xlsm ) .

The BeginUpdate procedure follows . This macro is contained in the  UpdateUserBook.xlsm workbook, which would be distributed to users of  UserBook.xlsm . This procedure ensures that  UserBook.xlsm is open . It then informs the user of what is about to happen with the message shown in Figure 28-5.

Figure 28-5: This message box informs the user that a module will be replaced.

 Sub BeginUpdate()     Dim Filename As String     Dim Msg As String     Filename = "UserBook.xlsm" '   Activate workbook     On Error Resume Next     Workbooks(Filename).Activate     If Err <> 0 Then         MsgBox Filename & " must be open.", vbCritical         Exit Sub     End If     Msg = "This macro will replace Module1 in UserBook.xlsm "     Msg = Msg & "with an updated Module." & vbCrLf & vbCrLf     Msg = Msg & "Click OK to continue."     If MsgBox(Msg, vbInformation + vbOKCancel) = vbOK Then         Call ReplaceModule     Else         MsgBox "Module not replaced,", vbCritical     End If End Sub 

When the user clicks OK to confirm the replacement, the ReplaceModule procedure is called. This procedure replaces Module1 in the  UserBook.xlsm with the copy of Module1 in the  UpdateUserBook.xlsm file:

 Sub ReplaceModule()     Dim ModuleFile As String     Dim VBP As VBIDE.VBProject '   Export Module1 from this workbook     ModuleFile = Application.DefaultFilePath & "\tempmodxxx.bas"     ThisWorkbook.VBProject.VBComponents("Module1") _       .Export ModuleFile '   Replace Module1 in UserBook     Set VBP = Workbooks("UserBook.xlsm").VBProject      On Error GoTo ErrHandle     With VBP.VBComponents         .Remove VBP.VBComponents("Module1")         .Import ModuleFile     End With '   Delete the temporary module file     Kill ModuleFile     MsgBox "The module has been replaced.", vbInformation     Exit Sub ErrHandle: '   Did an error occur?     MsgBox "ERROR. The module may not have been replaced.", _       vbCritical End Sub 

This procedure performs the following actions:

1. It exports Module1 (the updated module) to a file. The file has an unusual name to reduce the likelihood of overwriting an existing file.

2. It removes Module1 (the old module) from  UserBook.xlsm , using the Remove method of the VBComponents collection.

3. It imports the module (saved in Step 1) to  UserBook.xlsm .

4. It deletes the file saved in Step 1.

5. It reports the action to the user. General error handling is used to inform the user that an error occurred.

CD-ROM  

This example is available on the companion CD-ROM. It requires two workbooks:  UserBook.xlsm and  UpdateUserBook.xlsm .