Optional Dependency in VBA Module

by

Background

I am developing a VBA module (call it “Greg“) for VBA developers of UDFs, which enhances their existing module. They simply copy a snippet into their own module (call it Dev): if Greg is loaded, the snippet enhances behavior; and otherwise it makes no difference.

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!

You already mentioned a constant, why not:

in module Greg:
Public Const GREG_IS_LOADED = True

and in module dev:

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

Leave a Comment