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 220.127.116.113 throws a "mismatched input" error on the expression
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.