How to put Excel VBA into git

Question

I've inherited some VBA in Excel and want to put it into git. As it stands, git sees it as binary and doesn't want to do file change deltas but duplicate the whole file.

I want to break the individual macros out into files to put them into git. Is there a standard way to do this?

Not sure you can split the VBA macros into separate files without having to put them back in later. Might look into this: http://stackoverflow.com/questions/540535/managing-large-binary-files-with-git — the happy mamba, Mar 15 '16 at 23:50
https://stackoverflow.com/questions/805032/vba-multiple-developers-in-a-project-concurrent-development , https://christopherjmcclellan.wordpress.com/2014/10/10/vba-and-git/ (and links there), https://github.com/hilkoc/vbaDeveloper — max630, Mar 17 '16 at 04:01
Never try but the solution might be RUBBERDUCK Source Control http://rubberduckvba.com/SourceControl — Jean-Marc Flamand, Jun 29 '17 at 23:42
I've been copying and pasting macros into Gist, which does seem to allow you to track changes — Selkie, Aug 11 '17 at 23:16
Most of the solutions already added here seem to apply: https://stackoverflow.com/questions/2996995/how-to-use-version-control-with-vba-code/38297505#38297505. Although this question isn't strictly a duplicate because it specifically targets Git vs. SVN. — Colm Bhandal, Apr 08 '20 at 09:15

score 13 · Answer 1 · answered Nov 14 '19 at 15:25

13

If you use Rubberduck VBA, after clicking

You can use the file menu to "Export Active Project", which exports the form binaries and the code as BAS objects, which are just plain text. Then you can commit to git.

answered Nov 14 '19 at 15:25

Cody Ｇ

8,368
2
35
50

This opensource add-in was great. – WLiu Jul 27 '21 at 03:25

score 5 · Answer 2 · answered Aug 25 '17 at 08:33

5

You should be able to export modules as text to a git folder then commit as follows.

In The VBA Editor Add modules for each macro (Menu Insert/Module) copy each macros code into a module and save as a text file with control + E. Save into your git folder and use the normal git procedures to commit any changes.

When you change the vba code re save (control+E) the module and update git as normal.

answered Aug 25 '17 at 08:33

automate

106
1
6

There's also the possibility of exporting VBE code, but it should trust access to project's code – Bernardo Dal Corno Nov 28 '17 at 15:41

Colm Bhandal · Answer 3 · 2020-05-29T17:17:15.293

I had this problem and solved it by creating a VBA module to export other VBA modules. Usage instructions for this, and the raw code, can be found at the following location:

https://github.com/ColmBhandal/VbaSync.

C# Alternative

There is a C# alternative for putting VBA under Excel version control. The code has been added to a C# library which can be used to do version control e.g. via a CLI tool or via a VSTO AddIn:

https://gitlab.com/hectorjsmith/csharp-excel-vba-sync

Disclaimer

I was involved in the coding of the above repos. They are both free to use and open source.

score 2 · Answer 4 · answered Dec 01 '17 at 11:13

You can create a git pre-commit hook that runs the following Python script to automatically extract your VBA code and add it to your commit (see https://www.xltrail.com/blog/auto-export-vba-commit-hook):

import os
import shutil
from oletools.olevba3 import VBA_Parser


EXCEL_FILE_EXTENSIONS = ('xlsb', 'xls', 'xlsm', 'xla', 'xlt', 'xlam',)


def parse(workbook_path):
    vba_path = workbook_path + '.vba'
    vba_parser = VBA_Parser(workbook_path)
    vba_modules = vba_parser.extract_all_macros() if vba_parser.detect_vba_macros() else []

    for _, _, _, content in vba_modules:
        decoded_content = content.decode('latin-1')
        lines = []
        if '\r\n' in decoded_content:
            lines = decoded_content.split('\r\n')
        else:
            lines = decoded_content.split('\n')
        if lines:
            name = lines[0].replace('Attribute VB_Name = ', '').strip('"')
            content = [line for line in lines[1:] if not (
                line.startswith('Attribute') and 'VB_' in line)]
            if content and content[-1] == '':
                content.pop(len(content)-1)
                lines_of_code = len(content)
                non_empty_lines_of_code = len([c for c in content if c])
                if non_empty_lines_of_code > 0:
                    if not os.path.exists(os.path.join(vba_path)):
                        os.makedirs(vba_path)
                    with open(os.path.join(vba_path, name + '.bas'), 'w') as f:
                        f.write('\n'.join(content))


if __name__ == '__main__':
    for root, dirs, files in os.walk('.'):
        for f in dirs:
            if f.endswith('.vba'):
                shutil.rmtree(os.path.join(root, f))

        for f in files:
            if f.endswith(EXCEL_FILE_EXTENSIONS):
                parse(os.path.join(root, f))

For further details, have a look at https://www.xltrail.com/blog/auto-export-vba-commit-hook.

A side note if you like: if you're bringing Python into the equation, you can just use `openpyxl` Python package now to write the code instead of VBA, which can load Excel workbook data into memory without actually opening Excel, do whatever you do in Excel, and save it again in Excel formats. Then you'd just be using git with python code as normal, and from python could also save the Excel data separately as text or JSON etc which works with git. — Will Croxford, Feb 24 '19 at 21:04

How to put Excel VBA into git

4 Answers4

C# Alternative

Disclaimer

Linked