Source Control and custom VBA Code Exporter

Question

Context

Me and my team which I'm part of, for most of the time code in Excel VBA. Writing code in it self is pretty enjoyable, but combining our work in a shared repository is pretty painful. The ease our pain of combining/merging binary files, we have decided to help ourselves and create the Source Code Manager module.

Key features:

• User, should be able to export code modules from the Workbook into any location
• User, should be able to import code modules from any location back to Workbook.

Today, I would like from all of you to investigate the former.

Note: if you want to export your code, might have to select Trust access to the VBA project object model in the Developer Macro Settings (File - Options - Trust Center - Trust Center Settings). Otherwise, code will not be exported!

Note2: Everything, which is related to code management, I will try purposefully keep in one module to make it as shareable as possible. Sometime, I will intentionally break a OO principles like creating a new class and moving to it a set of closely related functionalities.

If anything needs an explanation, let me know. This might be a good indication that a piece which I wrote is in a 'not easy to follow' manner, which I'm actively trying to avoid!

---

Dependencies

To start working with this code you need to add following references to your VB Project

• Microsoft Scripting Runtime
• Microsoft Visual Basic for Application Extensibility 5.3
• Microsoft ActiveX Data Object 6.1 Library

This project requires also additional modules which can be found in External modules

• ArrayH
• Exception
• Tools

---

Client code

In it's simplest form, you have to call one method SourceControlH.ExportProjectComponents which requires two parameters, source from which components will be exported (of type Workbook) and location, where components will be stored (of type String).

Public Sub Start()
    SourceControlH.ExportProjectComponents ThisWorkbook, ThisWorkbook.Path & "\src"
End Sub

The following code presents how do I use SourceControlH module with the Workbook_AfterSave event which lets me to export the entire VbProject when it is saved.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    Dim ExportFolder As String
    ExportFolder = ThisWorkbook.Path & "\src"

    If Fso.FolderExists(ExportFolder) = False Then
        Fso.CreateFolder ExportFolder
    End If

    SourceControlH.ExportProjectComponents ThisWorkbook, ExportFolder

End Sub

---

Under the hood

This section will present the actual code which does all the magic.

SourceControlH.bas

Option Explicit
'@Folder("Helper")

Private Const ModuleName As String = "SourceControlH"

' Path to the folder where components will be saved.
Private pExportFolderPath As String

' Indicates if empty components should be exported or not.
Private pExportEmptyComponents As Boolean


Public Property Get ExportEmptyComponents() As Boolean
    ExportEmptyComponents = pExportEmptyComponents
End Property


Public Property Let ExportEmptyComponents(ByVal Value As Boolean)
    pExportEmptyComponents = Value
End Property


' Exports and saves project's components, from Source workbook
' to the location which is specified in Path argument.
' If Source.VBProject is protected, throw an InvalidOperationException.
' If target path does not exists or if path does not points to a folder,
' throw an DirectoryNotFoundException.
Public Sub ExportProjectComponents(ByVal Source As Workbook, ByVal Path As String)

    Const MethodName = "ExportProjectComponents"

    If Source.VBProject.Protection = vbext_pp_locked Then
        Exception.InvalidOperationException "Source.VBProject.Protection", _
                                            "The VBA project, in this workbook is protected " & _
                                            "therefor, it is not possible to export the components. " & _
                                            "Unlock your VBA project and try again. " & ModuleName & "." & MethodName
    End If

    If Tools.Fso.FolderExists(Path) = False Then
        Exception.DirectoryNotFoundException "Path", ModuleName & "." & MethodName
    End If

    pExportFolderPath = NormalizePath(Path)

    Dim Cmp As VBComponent
    For Each Cmp In GetExportableComponents(Source.VBProject.VBComponents)
        ExportComponent Cmp
    Next Cmp

End Sub


Private Function GetExportableComponents(ByVal Source As VBIDE.VBComponents) As Collection '<VbComponents>

    Dim Output As New Collection
    Dim Cmp As VBIDE.VBComponent
    For Each Cmp In Source
        If IsExportable(Cmp) Then
            Output.Add Cmp
        End If
    Next Cmp

    Set GetExportableComponents = Output
    Set Cmp = Nothing
    Set Output = Nothing

End Function


Private Function IsExportable(ByVal Component As VBIDE.VBComponent) As Boolean

    ' Check if component is on the list of exportable components.
    If ArrayH.Exists(Component.Type, ExportableComponentsTypes) = False Then
        IsExportable = False
        Exit Function
    End If

    If IsComponentEmpty(Component) = False Then
        IsExportable = True
        Exit Function
    End If

    If pExportEmptyComponents = True Then
        IsExportable = True
        Exit Function
    End If

    IsExportable = False

End Function


Private Property Get ExportableComponentsTypes() As Variant
    ExportableComponentsTypes = Array(vbext_ct_ClassModule, vbext_ct_MSForm, vbext_ct_StdModule, vbext_ct_Document)
End Property


' Indicates if component is empty by checking number of code lines.
' Files, which contains just Option Explicit will be counted as empty.
Private Function IsComponentEmpty(ByVal Source As VBIDE.VBComponent) As Boolean

    If Source.CodeModule.CountOfLines < 2 Then
        IsComponentEmpty = True

    ElseIf Source.CodeModule.CountOfLines = 2 Then
        Dim Ln1 As String: Ln1 = Source.CodeModule.Lines(1, 1)
        Dim Ln2 As String: Ln2 = Source.CodeModule.Lines(2, 1)

        IsComponentEmpty = (VBA.LCase$(Ln1) = "option explicit" And Ln2 = vbNullString)

    Else
        IsComponentEmpty = False
    End If

End Function


Private Sub ExportComponent(ByVal Component As VBIDE.VBComponent)

    ' Full name means - name of the component with an extension.
    Dim FullName As String: FullName = GetComponentFullName(Component)
    Dim ExportPath As String: ExportPath = pExportFolderPath & FullName
    Component.Export ExportPath

End Sub


' To avoid problems with saving components, add backslash
' at the end of folder path.
Private Function NormalizePath(ByVal Path As String) As String
    NormalizePath = Path & IIf(Path Like "*\", vbNullString, "\")
End Function


Private Property Get ComponentTypeToExtension() As Dictionary

    Dim Output As New Dictionary
    With Output
        .Add vbext_ct_ClassModule, "cls"
        .Add vbext_ct_MSForm, "frm"
        .Add vbext_ct_StdModule, "bas"
        .Add vbext_ct_Document, "doccls"
        .Add vbext_ct_ActiveXDesigner, "ocx"
    End With

    Set ComponentTypeToExtension = Output

End Property


Private Function GetComponentFullName(ByVal Component As VBIDE.VBComponent) As String
    GetComponentFullName = Component.Name & "." & ComponentTypeToExtension.Item(Component.Type)
End Function

---

External modules

Modules, with code, which are used by SourceControlH module. These has to be include in your project as well.

Exception.cls

Option Explicit
'@Exposed
'@Folder("Lapis")
'@PredeclaredId


' The exception that is thrown when a null reference (Nothing in Visual Basic)
' is passed to a method that does not accept it as a valid argument.
Public Sub ArgumentNullException(ByVal ParamName As String, ByVal Message As String)

    Err.Raise 513, , "Value cannot be null." & vbNewLine & vbNewLine & _
                    "Additional information: " & Message & vbNewLine & vbNewLine & _
                    "Parameter: " & ParamName

End Sub


' The exception that is thrown when one of the arguments provided to a method is not valid.
Public Sub ArgumentException(ByVal ParamName As String, ByVal Message As String)

    Err.Raise 518, , "An exception of type ArgumentException was thrown." & vbNewLine & vbNewLine & _
                    "Additional information: " & Message & vbNewLine & vbNewLine & _
                    "Parameter: " & ParamName

End Sub


' The exception that is thrown when a method call is invalid for the
' object's current state.
Public Sub InvalidOperationException(ByVal ParamName As String, ByVal Message As String)

    Err.Raise 515, , "An exception of type InvalidOperationException was thrown." & vbNewLine & vbNewLine & _
                    "Additional information: " & Message & vbNewLine & vbNewLine & _
                    "Parameter: " & ParamName

End Sub


' Occurs when an exception is not caught.
Public Sub UnhandledException(ByVal Message As String)

    Err.Raise 517, , "An exception of type UnhandledException was thrown." & vbNewLine & vbNewLine & _
                    "Additional information: " & Message & vbNewLine & vbNewLine

End Sub


' The exception that is thrown when a file or directory cannot be found.
Public Sub DirectoryNotFoundException(ByVal ParamName As String, ByVal Message As String)

    Err.Raise 520, , "An exception of type DirectoryNotFoundException was thrown." & vbNewLine & vbNewLine & _
                    "Additional information: " & Message & vbNewLine & vbNewLine & _
                    "Parameter: " & ParamName

End Sub

Tools.bas

Option Explicit
'@Folder("Lapis")

Private Const ModuleName As String = "Tools"
'@Ignore EncapsulatePublicField
Public Fso As New FileSystemObject


' Returns number of text lines based on the specified Stream.
' Throws an ArgumentNullException when Stream is set to nothing.
' Throws an ArgumentException when Stream is closed.
Public Function LinesCount(ByVal Stream As ADODB.Stream) As Long

    Const MethodName = "LinesCount"

    If Stream Is Nothing Then
        Exception.ArgumentNullException "Stream", ModuleName & "." & MethodName
    End If

    If Tools.IsStreamClosed(Stream) Then
        Exception.ArgumentException "Stream", "Stream is closed. " & ModuleName & "." & MethodName
    End If

    Dim Ln As Long
    Stream.Position = 0
    Do While Stream.EOS <> True
        Stream.SkipLine
        Ln = Ln + 1
    Loop

    LinesCount = Ln

End Function


' Determines if TextStream is closed.
' There is no property of TextStream object (like object.IsClosed) to know directly if Stream is closed
' or not. To know TextStream's state, method will attempt to read cursor position. If it fails
' (throws an error), that will mean Stream is not readable (closed).
' Throws an ArgumentNullException when Stream is set to nothing.
Public Function IsStreamClosed(ByVal Stream As ADODB.Stream) As Boolean

    Const MethodName = "IsStreamClosed"

    If Stream Is Nothing Then
        Exception.ArgumentNullException "Stream", ModuleName & "." & MethodName
    End If

    On Error Resume Next
    Dim i As Long: i = Stream.Position

    If Err.Number = 91 Then
        IsStreamClosed = True
        On Error GoTo 0

    ElseIf Err.Number = 0 Then
        IsStreamClosed = False

    Else
        ' Other, unexpected error occured. This error has to be moved upward.
        Exception.UnhandledException ModuleName & "." & MethodName
    End If

End Function

ArrayH.bas

Option Explicit
'@Folder("Helper")


' Item parameter has to be a simple type.
' Arr has to have only one dimension.
Public Function Exists(ByVal Item As Variant, ByRef Arr As Variant) As Boolean
    Exists = (UBound(Filter(Arr, Item)) > -1)
End Function

---

Conclusion

What do you thing about this code?

I'm looking forward to hear your thoughts about this piece. Within a week, I will also submit an import functionality for a review.

Answer 1

The overall first impression is a very good one. Procedures are small, focused, generally well-named, everything is pretty much in its place - well done!

What follows is a series of observations, and suggestions / how I'd go about "fixing" them.

---

Controlling object lifetime

In my view, it's important to be able to reliably know whether any object pointer is valid at any given point in the code that needs to dereference that pointer: for any object we create and consume, we want to be able to control when it's created, and when it's destroyed.

So while this is procedural code and we're not going to fuss much about coupling, we can still flag the auto-instantiated object:

'@Ignore EncapsulatePublicField
Public Fso As New FileSystemObject

While convenient, a global-scope auto-instantiated FSO object isn't something I'd recommend. Kudos for early-binding (side note: consider qualifying the library, e.g. As Scripting.FileSystemObject), but like anything accessing external resources (e.g. database connection, file handle, etc.), IMO its scope and lifetime should be as limited as possible. With a global-scope As New declaration, you give VBA the entire control over that object's actual lifetime.

Alternatively, a With block could hold the object reference in a tight, well-defined local scope, and we wouldn't even need to declare a variable for it:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    Dim ExportFolder As String
    ExportFolder = ThisWorkbook.Path & "\src"

    With New Scripting.FileSystemObject
        If Not .FolderExists(ExportFolder) Then
            .CreateFolder ExportFolder
        End If
    End With

    SourceControlH.ExportProjectComponents ThisWorkbook, ExportFolder

End Sub

Note that the {bool-expression} = False condition is redundant - comparing a Boolean expression to a Boolean literal is always redundant: Not {bool-expression} is more idiomatic, more concise, and more expressive.

---

Portability

VBA code that doesn't need to be tied to a particular specific VBA host application's object model library, should avoid such dependencies.

Public Sub ExportProjectComponents(ByVal Source As Workbook, ByVal Path As String)

The Source parameter should be a VBProject object, not a Workbook; by taking in an Excel.Workbook dependency, the module becomes needlessly coupled with the Excel object model: if you needed to reuse this code in the future for, say, a Word VBA project, you'd need to make changes.

---

Consistency

Qualifying members is nice! Consistently qualifying members is better :)

If Tools.Fso.FolderExists(Path) = False Then

Why is this Fso qualified with the module name, but not the one in ThisWorkbook? Without Rubberduck to help, a reader would need to navigate to the definition to make sure it's referring to the same object.

But, then again, I'd New up the FSO on the spot, and let VBA claim that pointer as soon as it's no longer needed:

With New Scripting.FileSystemObject
    If Not .FolderExists(Path) Then
        Exception.DirectoryNotFoundException "Path", ModuleName & "." & MethodName
    End If
End With

---

Other notes

I like your centralized approach to error-raising very much! I find the term "exception" a bit misleading though (if it's an exception, where's my stack trace?), and the procedure names read like properties. I'd propose something like this:

    Errors.OnDirectoryNotFound "Path", ModuleName & "." & MethodName

It removes the doubled-up "Exception" wording from the instruction, and the On prefix is reminiscent of the .NET convention to name event-raising methods with that prefix.

The Exception module being a class feels a bit wrong, even more so given the @PredeclaredId Rubberduck annotation, which presumably was synchronized and indicates the class has a VB_PredeclaredId = True attribute value: the class is never instantiated, only its default instance is ever invoked. The .NET equivalent is a static class with static methods, and the idiomatic VBA equivalent is a standard procedural module.

Of course Public Sub procedures in a standard module would be visibly exposed as macros in Excel, and using a class module prevents that... but so does Option Private Module!

Side note, there's a spelling error in this message:

    Exception.InvalidOperationException "Source.VBProject.Protection", _
                                        "The VBA project, in this workbook is protected " & _
                                        "therefor, it is not possible to export the components. " & _
                                        "Unlock your VBA project and try again. " & ModuleName & "." & MethodName

The comma after The VBA project is superfluous, there should be a dot after is protected, and so therefor should be Therefore, capital-T.

That said, VBA project protection can easily be programmatically thwarted, so with a little tweaking I think you could make this macro a bad boy that can just unlock a locked project to export it - but yeah, prompting the user with "oops, it's locked, try again" is probably the more politically-correct way to go about handling project protection.

I'm not finding any uses for the LinesCount function, and it validating whether the stream is open strikes me as weird: raising this error would clearly only ever happen because of a bug, and should be a Debug.Assert check, if present at all.

If ArrayH.Exists(Component.Type, ExportableComponentsTypes) = False Then

That H again? I'm starting to think it just stands for Helper, which is a code smell in itself. Once more, this condition would read better as If Not ArrayH.Exists(...) Then, but I'd like to point out that these helper methods feel very much like what would be extension methods in .NET-land, and ArrayExt.Exists - or better, a fully spelled-out ArrayExtensions.Exists would raise fewer eyebrows. Kudos for avoiding the trap of just dumping all "helper" procedures and functions into some Helpers bag-of-whatever module.

' Path to the folder where components will be saved.
Private pExportFolderPath As String

' Indicates if empty components should be exported or not.
Private pExportEmptyComponents As Boolean

This very much Systems Hungarian p prefix is distracting: there's no Hungarian Notation anywhere in the code and it reads like a charm - yes, naming is hard. Yes, naming is even harder in a case-insensitive language like VBA (or VB.NET).

You could make a simple, private data structure to hold the configuration state, and with that you wouldn't need any prefixing scheme:

Private Type ConfigState
    ExportFolderPath As String
    WillExportEmptyComponents As Boolean
End Type

Private Configuration As ConfigState

Note that because Export is a noun in the String variable, but a verb in the Boolean one, a distinction is necessary IMO. Adding a Will prefix to the Boolean name clarifies everything I find. And now you can have properties named exactly after the ConfigState members, without any prefixing scheme - note the Rubberduck annotation opportunity for a @Description annotation, too:

'@Description("Indicates if empty components should be exported or not.")
Public Property Get WillExportEmptyComponents() As Boolean
    WillExportEmptyComponents = Configuration.WillExportEmptyComponents
End Property

Public Property Let WillExportEmptyComponents(ByVal Value As Boolean)
    Configuration.WillExportEmptyComponents = Value
End Property

Speaking of Rubberduck opportunities, the @Folder organization can be enhanced - using @Folder annotations on the project's wiki describes how the annotation can be used to create subfolders:

'@Folder("Parent.Child.SubChild")

We have SourceControlH and ArrayH modules under '@Folder("Helper"), some Tools module (FWIW "Tools" has the exact same smell as "Helper" does) under '@Folder("Lapis"); the Exception module is under '@Folder("Lapis") as well, which means the tree structure looks like this:

- [Helper]
   - ArrayH
   - SourceControlH
- [Lapis]
   - Tools
   - Exception

Not sure what Lapis means, but the contents of the Tools module has this "whatever couldn't neatly fit anywhere else" bag-of-whatever feeling to it. What I wonder though, is why there's no clear dedicated SourceControl folder.

I'm not going to claim a more OOP approach would even be warranted here (procedural is perfectly fine), but the basis for sticking to procedural feels wrong: it's not a self-contained module, it has dependencies and must be packaged as a "bunch of modules that need to be imported together" anyway.

Having a Helpers.SourceControl folder would give you the dedicated space to cleanly split responsibilities while keeping the components neatly regrouped (in Rubberduck's Code Explorer toolwindow, that is).

' Full name means - name of the component with an extension.
Dim FullName As String: FullName = GetComponentFullName(Component)

I've seen Microsoft claim using the : instruction separator like this is "good practice" and "helps transition to VB.NET syntax". I'm not buying it at all. It looks awful and crowded. That comment is also very informative: it reads "this GetComponentFullName procedure needs a better name". In the Excel object model, FullName includes not only the file extension, but also the full path: your version of "full" isn't quite as "full" as it should be. In fact, FullName is actually nothing more than a fileName:

Dim fileName As String
fileName = GetComponentFileName(Component)

Kudos here:

       .Add vbext_ct_Document, "doccls"

This file extension is compatible with Rubberduck's own handling of document modules. By default, the VBIDE API exports document modules with a .cls file extension, which makes them import as class modules: to import them back into a Worksheet module, or into ThisWorkbook, you need some special handling, and that different file extension works great.

Source-controlling VBA code is hard, because the code in a document module (e.g. Worksheet) can very well include references to objects that exist in the host document, like ListObject tables and whatnot - and these can't really be under source control (not without having the whole host document under source control too!). Worksheet layout can't be restored from source code, unless the worksheet layout is itself actually coded: this means a VBA project restored from source control can't really ever fully restore a project without the original host document anyway. So, kudos for tackling this thorny issue.

Note that the last few pre-release builds of Rubberduck include bulk import/export functionality that does everything your code does, out of the box, without requiring programmatic access to the VBIDE Extensibility library, and without needing to share and manage versions for a SourceControlH module across devs and projects:

Answer 2

This is my response to the answer given by @Mathieu Guindon.

---

Scope and life time of Scripting.FileSystemObject

(...) but like anything accessing external resources (e.g. database connection, file handle, etc.), IMO its scope and lifetime should be as limited as possible.

Can you please also explain why is that? What pitfalls I might fall into while using external resources like Scripting.FileSystemObject? One of the reasons might be when I decide to start testing my methods which are using this global scope pointer. Are there any others?

---

Unused LinesCount

That's correct, LinesCount is not used in this context. I was relaying on Rubberduck to tell me if any method which are not used in this example... turns out it doesn't complain about this method. ProcedureNotUsed inspection should be triggered. I do not have this inspection turned off and filters are not enabled in Code Inspections window.

---

I think, this is the area where I'm expressing my subjective feelings about particular topics rather that hard data.

---

The letter H

That H again? I'm starting to think it just stands for Helper, which is a code smell in itself.

That's correct, H stands from Helper. I'm using this abbreviation to try mitigate a situation where invocation of a method would be predominantly a module name. I think, what I'm aiming at, is to minimize the noise around my helper/extension calls. IMO, it does not take too long to figure out, what H stands for, even when you this piece of code for the first time.

Public Sub Start()

    Const TestValue As String = "bbaabb"

    ' Case 1: Methods, which are helping dealing with string data type
    ' are collected in the StringExtensions module.
    If StringExtensions.Contains(TestValue, "a") _
       And StringExtensions.StartsWith(TestValue, "b") _
       And StringExtensions.EndsWith(TestValue, "b") Then

        ' ...
    End if

    ' Case 2: The same methods are now in the StringExt module.
    If StringExt.Contains(TestValue, "a") _
       And StringExt.StartsWith(TestValue, "b") _
       And StringExt.EndsWith(TestValue, "b") Then

        ' ...
    End if

    ' Case 3: ... and now, they are in the StringH module.
    If StringH.Contains(TestValue, "a") _
       And StringH.StartsWith(TestValue, "b") _
       And StringH.EndsWith(TestValue, "b") Then

        ' ...
    End if
End Sub

---

Hungarian notation

Yes, I'm actively against using Hungarian notation... But, this one is exception. I used this p (private) prefix here because:

• The same reasons why you would use _ in C#
• To avoid hassle of creating an additional private data structure
• To speed up a process of creating new variables
  • Right now, I have small code snippet in VS Code which lets me create module/class variable, property Get and Let in a few keyboard strokes. I don't see now the way how I could do it with custom data type.

{
    "New property":{
    "prefix": "prop",
    "body": [
        "Private p${1:name} As ${2:type}",
        "",
        "Public Property Get ${1:name} () As ${2:type}",
        "    ${1:name} = p${1:name}",
        "End Property",
        "",
        "",
        "Public Property Let ${1:name} (ByVal Value As ${2:type})",
        "    p${1:name} = Value",
        "End Property",
    ],
    "description": "Creates a new property."
    }
}

• Rubberduck also does not provide a accessors builder for the members of data structures and maybe there are good reasons for it.

---

Case for the ":"

The only place where I would use the : is where declaration of a variable AND value assignment is short. Lets say no longer than 65 characters (mine has 67, I know 😊).

Things which I haven't touched on in this answer, I completely agree with.