Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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.