Within VBA for excel, I have multiple workbooks that rely on the same code. It’s a pain to update them all. How can I import modules from a network folder at runtime, so that I can save changes once, and have all the files auto update?
Here is what I came up with. If you have an idea to improve my implementation, please comment. If you have your own solution you think is better, please add your own answer! Thanks!
In the "ThisWorkook" module:
Option Explicit
'Requires reference to "Microsoft Visual Basic for Applications Extensibility"
Const RepPath As String = "X:\MyNetworkLocation\"
Const Deprecator As String = "_DEP"
Private Sub Workbook_Open()
'VBA has trouble managing the removal and adding of code modules
'within a single procedure, so we'll handle this in two steps.
LoadCode
RemoveDeprecated
'Using Application.Ontime to get around potential compile errors caused by
'subs/functions being referenced in modules that don't get loaded until runtime
Application.OnTime Now(), "DoSomething"
Application.OnTime Now(), "DoSomethingElse"
End Sub
Private Sub LoadCode()
'This sub loads code from a central repository directory
Dim vbP As VBIDE.vbProject
Dim vbC As VBIDE.VBComponent
Dim FileName As String
Dim CodeName As String
Set vbP = Application.ThisWorkbook.vbProject
FileName = Dir(RepPath)
Do While Len(FileName) > 0
'get the module name without the file extension
CodeName = Left(FileName, InStrRev(FileName, ".") - 1)
Select Case CodeName
'Using Select Case to ignore certain modules...
Case "ThisWorkbook"
'do nothing
Case "CodeLoader"
'do nothing
Case Else
'test if module exists in VB Project
On Error Resume Next
Set vbC = vbP.VBComponents(CodeName)
On Error GoTo 0
'if the module already exists, we need to remove it
'VBA struggles with doing this within a single procedure,
'so for now, we'll just rename it
If Not vbC Is Nothing Then vbC.Name = vbC.Name & Deprecator
'load the new code module
vbP.VBComponents.Import RepPath & FileName
End Select
'reset variables
Set vbC = Nothing
CodeName = ""
'next file
FileName = Dir
Loop
End Sub
Private Sub RemoveDeprecated()
'This sub removes the deprecated code modules that we previously renamed
Dim vbP As VBIDE.vbProject
Dim vbC As VBIDE.VBComponent
Set vbP = Application.ThisWorkbook.vbProject
For Each vbC In vbP.VBComponents
If InStr(1, vbC.Name, Deprecator) > 0 Then vbP.VBComponents.Remove vbC
Next
End Sub