Excel VBA Project Sync Utility

A Python-based tool with a graphical user interface (GUI) for managing and synchronizing VBA projects between an Excel workbook (.xlsm or .xlsb) and external source code files (.bas, .cls). This utility allows developers to use modern version control systems like Git and their preferred external text editors for VBA development.

Compatibility: This tool is for Windows only. It relies on the pywin32 library to interact with Excel's COM object model, which is not available on other operating systems.

Key Features

• Two-Way Syncing: Synchronize standard modules (.bas), class modules (.cls), and Excel document objects (like ThisWorkbook and sheets) between the workbook and local files.
• VBA Component Export: Export all VBA components from your workbook into human-readable text files.
• Workbook Management:
  • Get the open/closed status of the workbook.
  • Open and close the target workbook directly from the utility.
• Backup & Recovery:
  • Create timestamped backups of the entire workbook.
  • Zip the entire project folder for a complete project backup.
  • Recover the workbook from the last five backups.
• Developer Tools:
  • Quick-access buttons to open the project directory in VS Code, PowerShell, or File Explorer.
  • A simple built-in text editor for project notes.

Installation

1. Python: Ensure you have Python 3.x installed on your system.
2. Dependencies: This script requires the pywin32 library to interact with Excel. Install it using pip:

   pip install pywin32

Usage

1. Place the SyncExcelCode.py script in the same directory as your .xlsm or .xlsb workbook.
2. Run the script:

   python SyncExcelCode.py

3. First-Time Use: Use the Export VBA Components button to extract all your existing VBA code into .bas and .cls files.
4. Editing: Edit the exported text files in your preferred code editor (e.g., VS Code).
5. Syncing Back: Use the corresponding "Sync" buttons (Sync Standard Modules, Sync Excel Objects, Refresh Classes) to import your changes back into the workbook.
6. Backup: Always use the Backup or Backup Project feature before making major changes.

Example Files

This repository includes three example Excel files to demonstrate the utility's behavior with different types of workbooks:

• example-xlsBinary.xlsb: A binary workbook that is unlocked. This file is fully compatible and can be manipulated by the utility.
• example-xlsMacros.xlsm: A standard macro-enabled workbook whose VBA project is locked with a password. Attempting to sync or export components from this file will result in a COM error, as the utility cannot bypass the project password.
• example-xlsXstandard.xlsx: A standard Excel file with no macros. This file type is not supported by the utility, as it does not contain a VBA project to interact with.

Troubleshooting & Common Errors

Error: "Programmatic access to Visual Basic Project is not trusted"

This is the most common error and occurs because Excel's security settings are blocking external applications from accessing the VBA project.

Solution: You must enable access in the Excel Trust Center.

1. Open Microsoft Excel.
2. Go to File > Options.
3. Select Trust Center from the left menu, then click the Trust Center Settings... button.
4. In the new window, select Macro Settings.
5. Check the box for "Trust access to the VBA project object model".
6. Click OK on all dialog windows to save the setting.

Security Warning: Enabling this setting lowers your computer's security by allowing applications to interact with your VBA projects. Only do this if you trust the scripts and applications you are running.

Error: "No Workbook Found"

This error appears if the script cannot find a .xlsm or .xlsb file in the same directory it is running from. Ensure your Excel file is located alongside the script.

Error: "Could not open VSCode / PowerShell"

The buttons to open external tools like VS Code or PowerShell depend on those applications being correctly installed and accessible via the system's PATH environment variable. If they fail, you can navigate to the project directory manually.

---

Developed by shalmanassar 2025, Nov 30