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.

我正在为Excel UDF的VBA开发人员开发一个VBA模块(称为“Greg”)。Greg的目的是增强终端用户的体验，他们在Excel中使用这些UDF。

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.

开发人员只需将一个代码片段复制到他们自己的模块中(称为“Dev”)。如果加载了Greg，则代码片段将增强Dev的行为。否则，它不会影响行为，但会提示Excel用户导入Greg.bas。

Approach

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

这是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:

...和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:

虽然这在Windows上偶尔奏效，但在Windows上经常失败，在Mac上总是失败。问题的关键似乎是开发环境中的Enhance_IsSupport()：

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().

我假设即使没有Greg模块，Greg.IsLoaded()行也会编译……事实的确如此！只有在Greg模块没有名为IsLoaded()的成员的情况下，它才会失败。

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

不幸的是，VBA似乎不能可靠地用IsLoaded()函数刷新它对Greg模块的“感知”。

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.

当我一起导入Greg.bas和Dev.bas时，一切都按预期工作：Enhance_IsSupported（）返回True，如果Greg被删除，它会更新为False。

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.

但是，当我第一次导入Dev.bas并运行DevEnhanced()，然后才导入Greg.bas时，尽管存在Greg，但Enhance_IsSupport()显然返回FALSE。

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!

后一种工作流程是绝对必要的：Excel用户必须首先运行DevEnhanced()并查看提示，才能首先了解Greg.bas！

Failed Solutions

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

不幸的是，没有任何实验是有用的。在Greg本身中，我尝试使用一个常量...

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()...

在Dev的代码片段中，我隐式尝试了Application.Evalate()...

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.

但是，虽然这对Enhance_IsSupport()本身有效，但相同的错误只是在其他地方突然出现-就像在Greg.*的其他实例中打鼹鼠一样-并且只有手动编辑才会“刷新”这些过程。我还希望避免对任何.Evalate()方法进行不稳定的调用，即使在工作表范围内也是如此。

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!

这个项目代表了我时间和精力的巨大投入，而且它是可操作的，所以非常感谢您在克服这最后一个障碍方面的帮助！

优秀答案推荐

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.

根据我的实验，如果自上次编译以来没有任何更改，则VBE不会重新编译模块中的代码。正如您注意到的，使用手动方法(菜单栏>文件>导入文件>*在对话框窗口中选择文件*)导入VBA模块不会触发项目中其他模块的重新编译。

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.

编辑引用可选模块(Greg)的行将强制重新编译该特定函数/子函数。然后，函数Enhance_IsSupport()应该如预期那样返回True。

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.

由于您的用户无论如何都将使用VBE手动导入模块(Greg)，因此要求他们删除Dev模块顶部的一行似乎并不过分。

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.

将Dev模块的内容导出到.bas文件并重新导入是另一种强制重新编译的做法，这也可以通过VBE接口手动完成。

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.

我最初的方法是使用VBA过程导出并重新导入开发人员模块，使其更加自动化。但是，这需要用户启用“信任访问VBA项目对象模型”(vbom访问)。

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*.

事实证明，即使常规的导入文件功能不会导致项目范围的重新编译，插入文件功能也会。当在Project Explorer中选择一个模块时，可以使用该特性(确保通过双击该模块窗口是可见的)。要使用它，请转到菜单栏>插入>文件...>*在对话框窗口中选择文件*。

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.

此功能可用于将.bas文件中的内容加载到模块中，即使该模块中已包含一些过程。出于某种原因，这迫使重新编译整个项目。

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.

知道您可以告诉您的用户插入一个新模块后，选择它并使用Insert File特性来插入Greg的内容。这样，您就可以确保Dev将重新编译。

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.

否则，您始终可以让他们使用导入文件方法添加模块，并添加TriggerRecompilation()SUB，以使用与Insert文件方法等效的VBA重新编译项目。

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.

基本上，在模块对象上调用.InsertFile似乎与从图形用户界面插入文件相同。它还会导致重新编译项目，为了利用这一点，我们可以创建一个虚构的.bas文件；导入它；然后删除它。

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.

使用VBA可能有一种更简单的方法来强制重新编译，但这是我所能找到的唯一不涉及启用vbom访问的一致方法。

I've got to say, I don't understand how someone could include a reference to a module in their project and forget to include the module itself, so this whole problem doesn't make any sense to me. But here is a possible solution. The only problem that I can think of in using this is that it requires a reference to VBA Extensibility.

我不得不说，我不明白为什么有人会在他们的项目中包括对模块的引用，却忘记了包括模块本身，所以整个问题对我来说没有任何意义。但这里有一个可能的解决方案。我在使用它时能想到的唯一问题是，它需要引用VBA可扩展性。

Function ThisProjectIncludesModuleGreg() As Boolean
    'This routine require a reference to "Microsoft Visual Basic for Applications Extensibility".
    
    Dim n As Long
    
    With ThisWorkbook.VBProject.VBComponents
        For n = 1 To .Count
            With .Item(n)
                If .Type = 1 And .name = "Greg" Then
                    ThisProjectIncludesModuleGreg = True
                    Exit Function
                End If
            End With
        Next
    End With
End Function

You already mentioned a constant, why not:

你已经提到了一个常数，为什么不：

in module Greg:
Public Const GREG_IS_LOADED = True

在模块Greg中：公共常量Greg_is_Load=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

I expected the #If-lines to be recompiled after a module was added. Obviously that does not work correctly.

我原以为添加模块后会重新编译#if-line。显然，这并不能正常工作。

What Office tool are you using?

I've tested the code below in Excel.

您使用的是什么Office工具？我已经在Excel中测试了下面的代码。

Public Function IsGregLoaded() As Boolean
    Dim o As Object
    IsGregLoaded = False
    For Each o In ActiveWorkbook.VBProject.VBComponents
        'Debug.Print o.Type, o.Name
        If o.Name = "Greg" And o.Type = 1 Then
           IsGregLoaded = True
           Exit Function
        End If
    Next
End Function

In msaccess it would be:

在MSAccess中，它将是：

    For Each o In Application.CurrentProject.AllModules
        If o.Name = "Greg" And o.Type = 5 Then

But in msaccess, both a code module and a class module have type 5

但在MSAccess中，代码模块和类模块都具有类型5

EDITED:

编辑：

Public Function IsGregLoaded()
    IsGregLoaded = False
    On Error Resume Next
    Application.Run "Greg.IsLoaded()"
    If Err.Number = 0 Then
       MsgBox "'Greg' module is present."
       IsGregLoaded = True
    Else
       MsgBox "'Greg' module is NOT present."
    End If
End Function