# 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
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. • Did you know Rubberduck provides this bulk import/export functionality out of the box? (no need to trust programmatic access to the VBIDE API library!) – Mathieu Guindon Dec 7 '19 at 16:08 • One more thing - the VBIDE API will export module/member attributes for document modules, but since they can't be imported back in, they shouldn't be exported at all - FYI Rubberduck's bulk-export functionality doesn't have this problem, but exporting individual modules does – Mathieu Guindon Dec 11 '19 at 19:45 • That's correct. When I import back modules into VB Project I use VBProject.VBComponents.Import(FilePath). This this lets me avoid problems with attributes. – FlameHorizon Dec 12 '19 at 10:27 ## 2 Answers 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: 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.

• Rubberduck also does not provide a accessors builder for the members of data structures and maybe there are good reasons for it. - reason being, it just hasn't been implemented yet.. but it's definitely planned - VSC is cheating! ;-) ...the global FSO is probably perfectly fine, pragmatically speaking. However it's unnecessary, and in any other language would be considered sloppy coding, and in .NET it would be leaking unmanaged resources. – Mathieu Guindon Dec 9 '19 at 13:46
• Now with that said, as a moderator I have to point out that this discussion forum-style of answer-to-an-answer isn't how Stack Exchange Q&A works; answers should respond to the OP, not to other answers! – Mathieu Guindon Dec 9 '19 at 13:50
• Got it. Thank you for your time! – FlameHorizon Dec 9 '19 at 14:40