Optional Dependency in VBA Module

Question

Background

I am developing a VBA module (call it "Greg") for VBA developers of Excel UDFs. The purpose of Greg is to enhance the experience for the end user, who uses those UDFs in Excel.

The developer must simply copy a snippet into their own module (call it "Dev"). If Greg is loaded, the snippet enhances Dev's behavior. Otherwise, it has no impact on behavior, but it does prompt the Excel user to import Greg.bas.

Approach

Here is a simplified sketch of Greg.bas...

Attribute VB_Name = "Greg"

' Keep these functions invisible to Excel users.
Option Private Module


' Enhancement function.
Public Function Enhancer(Optional x)
   ' ...
End Function


' Assert the 'Greg' module is loaded.
Public Function IsLoaded() As Boolean
    IsLoaded = True
End Function

...and of Dev.bas:

Attribute VB_Name = "Dev"

' Some UDF by the dev.
Public Function DevRegular()
   ' ...
End Function


' #############
' ## Snippet ##
' #############

' Use the enhancer, if available via the 'Greg' module.
Public Function DevEnhanced(Optional x)
    If Enhance_IsSupported() Then
        DevEnhanced = Greg.Enhancer(x)
    Else
        DevEnhanced = DevRegular()
        
        ' Prompt the user to import the enhancer.
        MsgBox "Import the 'Greg' module to enhance your experience."
    End If
End Function


' Check if enhancement is supported via the 'Greg' module.
Private Function Enhance_IsSupported() As Boolean
    On Error GoTo Fail
    Enhance_IsSupported = Greg.IsLoaded()
    Exit Function
Fail:
    Enhance_IsSupported = False
End Function

Problem

While this has worked sporadically on Windows, it has often failed there and always on Mac. The crux seems to be Enhance_IsSupported() within Dev:

Private Function Enhance_IsSupported() As Boolean
    On Error GoTo Fail
    Enhance_IsSupported = Greg.IsLoaded()
    Exit Function
Fail:
    Enhance_IsSupported = False
End Function

I assumed that the line Greg.IsLoaded() would compile, even if there were no Greg module present...and it does! It only fails when a Greg module exists without a member called IsLoaded().

Unfortunately, it seems that VBA does not reliably refresh its "awareness" of a Greg module with an IsLoaded() function.

When I import Greg.bas and Dev.bas together, everything works as intended: Enhance_IsSupported() returns True, and it updates to False if Greg is removed.

But when I first import Dev.bas and run DevEnhanced(), and only afterwards import Greg.bas, then Enhance_IsSupported() apparently returns False despite the presence of Greg.

Note

This latter workflow is absolutely essential: an Excel user must first run DevEnhanced() and see the prompt, in order to know about Greg.bas in the first place!

Failed Solutions

Unfortunately, no experimentation has availed. In Greg itself, I have tried using a constant...

Public Const IS_LOADED As Boolean = True

...and also a procedure:

Public Function IsLoaded() As Boolean
   IsLoaded = True
End Function

In the snippet for Dev, I have implicitly tried Application.Evaluate()...

Private Function Enhance_IsSupported() As Boolean
    On Error GoTo Fail
    Enhance_IsSupported = [Greg.IsLoaded()]
    '                     ^^^^^^^^^^^^^^^^^
    '                     Application.Evaluate("Greg.IsLoaded()")
    Exit Function
Fail:
    Enhance_IsSupported = False
End Function

...but while this works for Enhance_IsSupported() itself, the same error simply crops up elsewhere — like whack-a-mole at other instances of Greg.* — and only a manual edit will "refresh" those procedures. I would also prefer to avoid unstable calls to any .Evaluate() method, even in the Worksheet scope.

Questions

• What is the simplest way to "refresh" the Dev module, such that its procedures now recognize the calls to Greg.*?
• Is this possible without resetting cached or Static variables in Dev?
• Can this be done with an IS_LOADED constant rather than an IsLoaded() procedure?

Bonus

• Can this refreshment be done by some Sub like Dev.Refresh(), called automatically at the start of Enhance_IsSupported()?
• Can this refreshment be done by some Sub like Greg.Refresh(), as run manually by the user in the VBA editor?

---

This project represents a great investment of my time and energy, and it is otherwise operational, so your help is greatly appreciated in conquering this final obstacle!

Answer 1

The issue here is that the reference to the other module is stale. To update it you need the module to recompile.

Based on my experimentations, the VBE won't recompile the code in the module if there were no changes since the last compilation. And as you noticed, importing a VBA module with the manual method (Menu Bar > File > Import File > *Select file in dialog window*), won't trigger a recompilation of the other modules in the project.

Editing the line with the reference to the optional module (Greg) will force a recompilation of that specific function/sub. The function Enhance_IsSupported() should then return True as expected.

Adding or removing a member of the module

Another way of doing it is to add/remove a sub or module level variable/constant which causes the whole module to recompile. Knowing that, you could have a private variable at the top that could be quickly deleted manually to achieve this. Eg.:

Private DELETE_ME_TO_COMPLETE_GREG_INSTALLATION As Boolean

Since your user will be using the VBE to import the module (Greg) manually anyway, it doesn't seem too much to ask them to delete a row at the top of the Dev module.

Exporting and re-importing the module with stale references

Exporting the content of the Dev module to a .bas file and re-importing it is another was to force the recompilation and that can also be done manually with the VBE interface.

My initial approach to make this more automated was to just export and re-import the Dev module with a VBA procedure. However this requires the user to have "Trust access to the VBA project object model" (VBOM access) enabled.

I saw in one of your comments that you cannot make the users enable that, so this won't solve your issue, but I'm just going to include the code here in case this is useful to someone.

Sub ResetModule()
    
    With ThisWorkbook.VBProject.VBComponents
    
        'Optional: Delete all module copies (Dev1, Dev2, etc.)
        On Error Resume Next
            Dim i As Long
            For i = 1 To 100
                .Remove .Item("Dev" & i)
                If Err.Number <> 0 Then
                    Exit For
                End If
            Next
        On Error GoTo 0
        
        Dim ModuleTempLocation As String
        ModuleTempLocation = ThisWorkbook.Path & Application.PathSeparator & "Dev.bas"
        
        'Export and remove module
        Dim md As Object 'CodeModule
        Set md = .Item("Dev")
        md.Export ModuleTempLocation
        'Change name to avoid potential naming conflicts
        md.Name = md.Name & "_OLD"
        .Remove md
    
        'Re-import module
        .Import ModuleTempLocation
        Kill ModuleTempLocation
    
    End With

End Sub

Using the Insert File feature

It turns out that even if the regular Import file feature doesn't cause project-wide recompilation, the Insert File feature does. This feature can be used while a module is selected in the Project Explorer (make sure the module window is visible by double-clicking it). To use it, go Menu Bar > Insert > File... > *Select file in dialog window*.

This feature can be used to load content from a .bas file to a module even if it already has some procedures in it. And for some reason, this forces a recompilation of the whole project.

Knowing that you could tell your users to insert a new module, select it and use the Insert File feature to insert the content of Greg. This way you'll be sure that Dev will recompile.

Otherwise, you can always let them add the module with the Import File method and add a TriggerRecompilation() sub to recompile the project using the VBA equivalent of the Insert File method.

Basically, calling .InsertFile on a Module object seems to be the same as inserting a file from the GUI. It also causes a recompilation of the project and to make use of this, we can create a fictitious .bas file; import it; and delete it.

For example:

Sub TriggerRecompilation()

    'Create fictitious .bas file
    Dim FilePath As String
    FilePath = ThisWorkbook.Path & Application.PathSeparator & "ForceRecompilation.bas"
    Open FilePath For Output As #1
    Print #1, "Attribute VB_Name = ""ForceRecompilation"""
    Close #1
    
    'Import and delete module
    Dim ThisModule As Module
    Set ThisModule = ThisWorkbook.Modules.Add
    ThisModule.InsertFile FilePath
    Application.DisplayAlerts = False
    ThisModule.Delete
    Application.DisplayAlerts = True
    Set ThisModule = Nothing
    Kill FilePath

End Sub

There might be a simpler method with VBA to force a recompilation, but that's the only consistent approach I could find that doesn't involve having VBOM access enabled.