Excel VBA to Update ThisWorkbook Code Module

At a previous job I designed an Excel template that was used to create program estimate to complete/estimate at completion. The template would be configured based on the program structure. VBAs were used to help configure the template, retrieve the latest financial data from a SharePoint document library, and check the workbook for errors. Because the VBAs were being updated over time I created a VBA in the ThisWorkbook module that updated the VBA source code, which was also stored on SharePoint.

This has worked well for many years up to the point where the SharePoint site was updated and moved off site. This isn’t a big issue. The file access now requires a web address and the module VBAs can be updated to point to the new addresses. But the VBA in ThisWorkbook module also needs updating since the VBA source files have moved with the financial data. Updating the template is simple, but there are many, many already configured program Excel files need updating. This requires updating the VBA code in the ThisWorkbook module.

To solve this problem I created a Excel VBA tool that uses a file dialog to select one or more files to update the macros in the ThisWorkbook module. This update is different than deleting modules and importing new VBA source code modules. This actually required deleting all the source lines in the ThisWorkbook module and adding the new file.

The code is straight forward. For each workbook assigned to the wb object, delete all the lines inthe code module and add from a file the new source code. I found several examples in my Internet search. I had issues with some of the sample code for DeleteLines that used StartLine and Count parameters as well as using parenthesizes on the method. It caused a compile syntax error requiring an “=” assignment. In the end I just used DeleteLines 1, CountOIfLines, which worked. A sample code snippet is shown below.

With wb.VBProject.VBComponents("ThisWorkbook").CodeModule
    .DeleteLines 1, .CountOfLines
    .AddFromFile "filename"
End With

Overall very simple approach to update the VBA code in the “ThisWorkbook” module using an external Excel VBA.