Modularise Project and Deduplicate Code By Loading External Modules On Startup

Question

Starting point I kind-of inherited a VBA project at work that consists of a bunch of Word document templates that provide users with the tools they need to create documents according to company guidelines and to help them give all documents the same appearance. We use different templates for the documents that we create, and each of these uses a different subset of tools from the collection. I was faced with the problem of having to maintain multiple copies of the same code in as many different templates.

Current Solution The solution I came up with was to group associated code fragments into separate modules where each serves a well-defined purpose, to export these modules to external files and to build a preloading process that is triggered from AutoNew() and AutoOpen(). One advantage of this is that I can change the code in these modules without having to wait for write access to the templates that need them.

Failed Ideas What I tried but failed at is to move the code to another template and to link that to the document template via the VBE in an attempt at faking some kind of inheritance. This approach consistently led to crashes of the macro system as well as (occasionally) Word itself. What's more, the error codes that Word decided to spew at me were entirely unhelpful, which is the main reason why I threw all of this in the bin - I didn't feel equipped to even start debugging it.

What's probably not going to work What I would like to avoid, though, is to write a Word AddIn or using VSTO because we do not have any meaningful way of deploying new versions of the code to the users' workstations, and I'd hate the prospect of having to tamper with their machines every time I fix something. For my personal convenience, I'd pretty much prefer if everything could just sit in our group's template directory on our central network share.

Well, here's the monstrosity I created and that I'd like your opinion on:

' Constants are public because they are defined in a different module and I 
' just copied them here for the purpose of this post.
' path to the data storage directory
Public Const DATADIR_PATH As String = "K:\msoffice\data\"
' path to the code modules
Public Const MODULE_PATH As String = DATADIR_PATH & "modules\"
' list of additional modules that this template requires
Public Const IMPORT_MODULES As String = "table_tools.bas,ParseHeader.bas,drawing_tools.bas"

Sub AutoOpen()
  ' Only preload code when the opened document is NO template because
  ' that would lead to the modules being saved with the template, which
  ' breaks the concept.
  If (ActiveDocument.Type <> wdTypeTemplate) Then
    PreloadAdditionalModules
  End If
End Sub

Sub AutoNew()
  PreloadAdditionalModules
End Sub

' Load additional code modules into the project
Private Sub PreloadAdditionalModules()
  Dim Module As Variant
  Dim ModulesToLoad() As String
  ' Split() is the only way to ensure that the array exclusively consists
  ' of strings.
  ModulesToLoad = Split(IMPORT_MODULES, ",")

  ' Load all listed modules into the project that do not yet exist.
  For Each Module In ModulesToLoad
    If (Dir(MODULE_PATH & Module) = "") Then
      MsgBox "Critical error: Unable to load module " & Module & ". "& _
             "Network share unreachable?", vbExclamation
      ActiveDocument.Close False
      Exit Sub
    ElseIf (ModuleExists(Module) = False) Then
      ThisDocument.VBProject.VBComponents.import FileName:=MODULE_PATH & Module
    End If
  Next Module
  ' Loading a module into the project constitutes a change of the template file,
  ' therefore prompting Word to ask the user if it should save the changes to disk.
  ' This would break the concept by permanently embedding the "dynamically" 
  ' loaded modules into the template. Therefore, we mark the template as "already saved."
  ThisDocument.Saved = True
End Sub

Private Function ModuleExists(ByVal WantedModule As String) As Boolean
  Dim count As Integer

  ModuleExists = False
  ' Module filenames are passed including their filename extensions. As the module
  ' names are identical to their file names, the extension hinders comparison with
  ' existion module names. It is therefore cut off beforehand.
  WantedModule = Split(WantedModule, ".")(0)
  With ThisDocument.VBProject.VBComponents
    For count = 1 To .count
      If (.Item(count).name = WantedModule) Then
        ModuleExists = True
        Exit For
      End If
    Next count
  End With
End Function

P.S.: Curiously, Rubberduck-VBA 1.4.3.2343 throws a "mismatched input" error on the expression Split(WantedModule, ".")(0).

However, because this also requires every workstation to allow all access to the VBA object model, I feel like this approach poses an inherent security risk, and I would very much like to avoid it but can't.

Thank you very much for taking the time to read all this, and for any input you may have.

Answer 1

I have two tiny things right now. The variable count is a system reserved name for something else, so I would avoid naming the variable that way. countComponents might be good.

ElseIf (ModuleExists(Module) = False) Then

This logic can be simplified by editing the ModuleExists function to return the opposite.

ElseIf NoModuleExists(Module) Then

A simple adjustment here

Private Function NoModuleExists(ByVal WantedModule As String) As Boolean
  Dim countModules As Integer

  NoModuleExists = True

  WantedModule = Split(WantedModule, ".")(0)
  With ThisDocument.VBProject.VBComponents
    For countModules = 1 To .count
      If (.Item(count).Name = WantedModule) Then
        ModuleExists = False
        Exit For
      End If
    Next countModules
  End With
End Function

That would just increase the intuitive logic. Alternatively you could just use

ElseIf Not ModuleExists(Module) Then

But that is also using a False basis when a True is always more intuitive with if.

---

Speaking of checking for the module, you could simplify all that nesting by using the module objects as the loop.

Private Function NoModuleExists(ByVal WantedModule As String) As Boolean
  Dim targetModule As Object
  NoModuleExists = True

  For Each targetModule In Application.VBE.ActiveVBProject.VBComponents
    If targetModule.Name = WantedModule Then
        NoModuleExists = False
        Exit Function
    End If
  Next
End Function

Oh, one more thing on that integer - integers are obsolete. According to msdn VBA silently converts all integers to long.

Answer 2

Thanks for using Rubberduck! As @Vogel612 mentioned, the parser error you're getting is a known issue in the v1.4.3 release. In short:

foo = bar(x)(y)

Accessing a subscript immediately after a function call isn't supported. An easy work-around is to introduce a local variable to hold the collection/array result:

Dim result
result = bar(x)
foo = result(y)

Note that your entire code parses perfectly fine as-is with the current 2.0 build, which we're going to pre-release in a couple of weeks days.

---

The sad part is that, since Rubberduck couldn't parse your project, you couldn't benefit from the code inspections.. Not that there's a ton of results, but here's what 2.0 says:

• Member AutoOpen is implicitly public
• Member AutoNew is implicitly public

Module members are public by default, which can be counter-intuitive. Consider specifying explicit access modifiers to avoid ambiguity.

• Parameter WantedModule is passed ByVal and assigned a value

Parameter is passed by value, but is assigned a new value/reference. Consider making a local copy instead if the caller isn't supposed to know the new value. If the caller should see the new value, the parameter should be passed ByRef instead, and you have a bug.

There are other results, but they're probably not relevant to your actual project (I just pasted your code into a new module, in Excel VBA) - Watch out for Option Explicit not being specified though.

---

Your indentation is well done and consistent, but you'll have to configure Rubberduck 2.0 indenter settings to make 2-space indent happen - default being 4 spaces.

---

Public Const DATADIR_PATH As String = "K:\msoffice\data\"

If K: is a network drive, know that it could be mapped (or re-mapped) to any other letter on other machines - it's probably a better idea to use the UNC path, like:

Public Const DATADIR_PATH As String = "\\servername\folder\msoffice\data\"

That way the path is accessible if you're on the network, regardless of whether your network drives are connected or not.

---

As the module names are identical to their file names

It's usually the case. However a module's actual name isn't determined by its filename, but by the value of a specific dedicated attribute in the file header (the VBE hides class headers and Attribute meta-instructions):

Attribute VB_Name = "table_tools"

In the unlikely event where someone would modify that attribute in, say, Notepad, your code would fail to identify an existing module, re-import a set of existing functions and procedures, and then the code wouldn't compile anymore because of duplicate definitions.

I'm not suggesting to stop assuming that the file name matches the module name, merely saying that the fact that the module name matches the file name is more of a convenient coincidence - and it's good to know where VBA actually gets the module name from, too =)

Answer 3

I don't have much to add, other than I noticed a complete lack of error handling. I'm going to guess that this template won't work correctly if the modules aren't loaded correctly. You should take some time to identify possible failure points and handle possible runtime errors by notifying the user that there was an issue loading required files, logging relevant info off someplace, and then shutting down the template.