How to put excel vba in git

Question

I have inherited VBA in Excel and want to put it in git. Be that as it may, git considers it binary and does not want to make a file change delta, but duplicates the entire file.

I want to split individual macros into files to put them in git. Is there a standard way to do this?

Answer 1

You should be able to export the modules as text to the git folder, and then commit as follows.

In the VBA editor Add modules for each macro (insert / menu module), copy each macro into a module and save it as a text file with + E control. Save git in your folder and use the usual git procedures to make any changes.

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

Answer 2

I had this problem and solved it by creating a VBA module to export other VBA modules. Instructions for use for this and the raw code can be found at the following address:

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

C # Alternative

Since I wrote this module, I have discovered VSTO plugins in C #, and I and his teammate have created a much more advanced import / export method using C #. The main reason why C # is better is that it does not face this problem: how can a module import itself? To do this, you need to erase your own code, but it cannot erase the code, since it still works! This is not just a theoretical point: I saw this question from my own experience.

Unfortunately, I cannot make C # code available, as it is confused with other project-specific code, but I hope to deal with it someday.

Answer 3

You can create a git pre-commit binding 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 more information, see https://www.xltrail.com/blog/auto-export-vba-commit-hook .