VBA and source control? Do you know what is VBA? Are you sure we can source control the code, that resides in Excel?
Oh, you mean that lengthy way of extracting the modules one by one? But why? Don’t you have anything better to do with the time you are getting paid?
And actually, why should we do it? Yeah, source control is important, but for big projects, and ours is just an internal app, on which 3 departments count on to take their numbers…
The lines above are combined real life examples, that I have heard when I have mentioned “VBA” and “source control” in one sentence. The main reason for those is that mainly, 99.9% of the VBA developers are not developers, but “financial-controllers”, “analysts”, “secretaries”, “accountants”, “Joe-the-intern”, “logistics consultants”, “tax consultants” and etc. And just that 0.1% of VBA developers that are actually developers are the one that use source control. How I got the data? Took a look at github for VBA code and did not find a lot. Then took a guess 🙂
Anyway, source control is a must. It does not matter whether you are a VBA developer or a senior Python dude. You should use source control. No excuses.
Having said the above, below there is a video, in which I present some automatic code, extracting the VBA code from a project. By running the GitSave() the VBA code is created in 2 nice folders and could be used for source control. The code resides here – https://github.com/Vitosh/VBA_personal/blob/master/VBE/GitSave.vb and looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
Sub GitSave() DeleteAndMake ExportModules PrintAllCode PrintAllContainers End Sub Sub DeleteAndMake() Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") Dim parentFolder As String: parentFolder = ThisWorkbook.Path & "\VBA" Dim childA As String: childA = parentFolder & "\VBA-Code_Together" Dim childB As String: childB = parentFolder & "\VBA-Code_By_Modules" On Error Resume Next fso.DeleteFolder parentFolder On Error GoTo 0 MkDir parentFolder MkDir childA MkDir childB End Sub Sub PrintAllCode() Dim item As Variant Dim textToPrint As String Dim lineToPrint As String For Each item In ThisWorkbook.VBProject.VBComponents lineToPrint = item.codeModule.Lines(1, item.codeModule.CountOfLines) Debug.Print lineToPrint textToPrint = textToPrint & vbCrLf & lineToPrint Next item Dim pathToExport As String: pathToExport = ThisWorkbook.Path & "\VBA\VBA-Code_Together\" If Dir(pathToExport) <> "" Then Kill pathToExport & "*.*" SaveTextToFile textToPrint, pathToExport & "all_code.vb" End Sub Sub PrintAllContainers() Dim item As Variant Dim textToPrint As String Dim lineToPrint As String For Each item In ThisWorkbook.VBProject.VBComponents lineToPrint = item.Name Debug.Print lineToPrint textToPrint = textToPrint & vbCrLf & lineToPrint Next item Dim pathToExport As String: pathToExport = ThisWorkbook.Path & "\VBA\VBA-Code_Together\" SaveTextToFile textToPrint, pathToExport & "all_modules.vb" End Sub Sub ExportModules() Dim pathToExport As String: pathToExport = ThisWorkbook.Path & "\VBA\VBA-Code_By_Modules\" If Dir(pathToExport) <> "" Then Kill pathToExport & "*.*" End If Dim wkb As Workbook: Set wkb = Excel.Workbooks(ThisWorkbook.Name) Dim unitsCount As Long Dim filePath As String Dim component As VBIDE.VBComponent Dim tryExport As Boolean For Each component In wkb.VBProject.VBComponents tryExport = True filePath = component.Name Select Case component.Type Case vbext_ct_ClassModule filePath = filePath & ".cls" Case vbext_ct_MSForm filePath = filePath & ".frm" Case vbext_ct_StdModule filePath = filePath & ".bas" Case vbext_ct_Document tryExport = False End Select If tryExport Then Debug.Print unitsCount & " exporting " & filePath component.Export pathToExport & "\" & filePath End If Next Debug.Print "Exported at " & pathToExport End Sub Sub SaveTextToFile(dataToPrint As String, pathToExport As String) Dim fileSystem As Object Dim textObject As Object Dim fileName As String Dim newFile As String Dim shellPath As String If Dir(ThisWorkbook.Path & newFile, vbDirectory) = vbNullString Then MkDir ThisWorkbook.Path & newFile Set fileSystem = CreateObject("Scripting.FileSystemObject") Set textObject = fileSystem.CreateTextFile(pathToExport, True) textObject.WriteLine dataToPrint textObject.Close On Error GoTo 0 Exit Sub CreateLogFile_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateLogFile of Sub mod_TDD_Export" End Sub |
To make sure that the above code compiles, you need to add the Microsoft Visual Basic for Extensibility 5.3. reference. This is how to add it (sorry for the German screenshot):
- In the VBEditor, from the Tools menu, choose References
- Scroll down to find the Microsoft Visual Basic for Extensibility 5.3.
- Add it and press “OK”
Thank you!