如何在项目之间重用核心VBA功能(UDF)，但不在单元格插入功能中显示它们 [英] How to reuse Core VBA functions (UDFs) across projects, but not show them in cell insert function

问题描述

我有一个带有核心"功能和子程序的插件，我想在不同的插件或VBA项目中引用和使用这些插件.由于代码的重用和单一更新的原则.

I have an Addin with "core" functions and subs that I want to reference and use in different Addins or VBA projects. Because of the code reuse and single update principles.

例如，一个函数，该函数根据条件过滤集合成员并返回子集合.代码本身在这里不是问题.

For example, a function, that filters collection members based on criteria and returns a sub-collection. The code itself is not an issue here.

Public Function listNamesContaining(ByVal NamesInput As Names, ByVal ContainsCriteria As String) As Collection
    Dim NameMember As Name

    Set listNamesContaining = New Collection
    For Each NameMember In NamesInput
        If InStr(1, NameMember.Name, ContainsCriteria, vbTextCompare) Then
            listNamesContaining.Add NameMember
        End If
    Next
End Function

我不想在单元格插入函数 中显示此函数，因为它返回了一个收集对象，但是我想在多个VBA中重用它VBA代码中的项目.

当前问题的图片，单元格插入公式中显示了一个目标函数:

A picture of current problem, an object function is showing in cell insert formula:

我找到了解决方案 SO2 用于单个项目方法，使用

I have found a solutions SO1,SO2 for a single project methods, using Option Private Statement.
This however does not solve the problem, because of the other applications or projects limitation.

当模块包含Option Private Module时，公共部分(例如，在模块级别声明的变量，对象和用户定义的类型)在包含该模块的项目中仍然可用，但是 不适用于其他应用程序或项目 .

When a module contains Option Private Module, the public parts, for example, variables, objects, and user-defined types declared at module level, are still available within the project containing the module, but they are not available to other applications or projects.

接下来，我在

Next, I have found a question on MRExcel formum - Hiding VBA functions only. Tom Schreiner suggests, that I can use Custom Classes and implement the functions inside the Classes. That way, they will not be availible through cell insert function, but still availible to my other projects.

1. 如何在项目之间重用Core VBA函数(UDF)，但不在单元格插入函数中显示它们?
2. 自定义类解决方案仅是一种吗?
3. (基于意见的)我最初关于通过excel插件(.xlam)在多个项目中共享核心方法的理念是否合理?

1. How to reuse Core VBA functions (UDFs) across projects, but not show them in cell insert function?
2. Is the Custom Classes solution only one?
3. (Opinion-based) Is my original philosophy about sharing the core methods accross multiple project through excel addins (.xlam) a reasonable one?

推荐答案

请向下滚动以进行更新，因为我发现此手册有什么需要

总而言之，您需要将函数放置在外接程序的类中，但是要使跨工作簿脚本有效，还有一个额外的步骤，您不能在外部类上使用New关键字.因此，您需要编写一个可以在外部调用的类工厂函数.

In summary you will need to place your function in a class in your addin but there is an extra step to get cross workbook scripting operative, you cannot use the New keyword on a external class. So you need to write a class factory function which can be called externally.

下一个问题是耦合，您可以使用工具参考"并引用该项目，以使用其有用的Intellisense BUT进行早期绑定，由于加载顺序，您可能会为背部创建一个棒，该插件将通过任何调用来加载有参考的客户.另一种选择是Late Bound等效项，它消除了引用，但将加载外接程序的负担加到了开发人员上.

Next problem is the coupling, you can use Tools References and make reference to the Project to get early binding with its useful Intellisense BUT you potentially create a rod for your back because of loading sequences, the addin will be loaded by any calling client that has a reference. An alternative is the a Late Bound equivalent that eliminates the reference but places the burden of loading the addin on the developer.

这是步骤...

1. 创建一个项目，我叫我FunctionLibrary.xlsm，我将其重命名为 从"VBAProject"到FunctionLibrary的项目属性.

1. Create a project, I called mine FunctionLibrary.xlsm, I renamed the Project property from 'VBAProject' to FunctionLibrary.

向您的项目添加一个类，我将其命名为MyLibrary，然后将 Instancing至2 - PublicNotCreateable.我添加了(简单) 以下代码

Add a class to your project, I called my MyLibrary, I set the Instancing to 2 - PublicNotCreateable. I added the (simple) following code

    Option Explicit

    Public Function Add(x, y)
        Add = x + y
    End Function

1. 添加一个名为'modEarlyBoundClassFactory'的标准模块，并添加以下代码

    Option Explicit

    Public Function CreateMyLibraryEarlyBoundEntryPoint(ByVal sLicenceKey As String) As MyLibrary
        'If sLicenceKey = "Yourlicencekey" Then
            Set CreateMyLibraryEarlyBoundEntryPoint = New MyLibrary
        'End If
    End Function

1. 在ThisWorkbook模块中，添加了以下代码

    Option Explicit

    Public Function CreateMyLibraryLateBoundEntryPoint(ByVal sLicenceKey As String) As Object

        'If sLicenceKey = "Yourlicencekey" Then
            Set CreateMyLibraryLateBoundEntryPoint = New MyLibrary
        'End If

    End Function

1. 保存工作簿
2. 创建一个调用工作簿，我将其命名为FunctionLibraryCallers.xlsm，并在新的标准模块中添加了以下代码

Option Explicit

Sub EarlyBoundTest() '* requires Tools->References to addin (this can create load sequence issues and the addin equivalent of dll hell)! Dim o As FunctionLibrary.mylibrary Set o = FunctionLibrary.CreateMyLibraryEarlyBoundEntryPoint("open sesame") Debug.Print o.Add(4, 5)

Sub EarlyBoundTest() '* requires Tools->References to addin (this can create load sequence issues and the addin equivalent of dll hell)! Dim o As FunctionLibrary.mylibrary Set o = FunctionLibrary.CreateMyLibraryEarlyBoundEntryPoint("open sesame") Debug.Print o.Add(4, 5)

End Sub

Sub LateBoundTest()

Sub LateBoundTest()

'* you need to write code to ensure the function library is loaded!!!
On Error Resume Next
Dim wbFL As Excel.Workbook
Set wbFL = Application.Workbooks.Item("FunctionLibrary.xlsm")
On Error GoTo 0
Debug.Assert Not wbFL Is Nothing
'* End of 'you need to write code to ensure the function library is loaded!!!'

Dim o As Object 'FunctionLibrary.mylibrary
Set o = wbFL.CreateMyLibraryLateBoundEntryPoint("open sesame")  '* this works because the method is defined in ThisWorkbook module of library
Debug.Print o.Add(4, 5)

结束子

1. 要运行顶部子菜单，您需要转到工具"->引用"，并引用FunctionLibrary.xlsm.
2. 要运行底部的子程序，不需要工具->参考，尽管您必须注释掉顶部的子机，以避免编译错误.

更新:折叠评论者的反馈. DLL Hell是将代码移至库中，然后又要担心加载它，加载正确的版本和正确的依赖项的情况.

UPDATE: Folding in commenter's feedback. DLL Hell is when you have moved code to a library and then you have to worry about loading it, loading the right version and the right dependencies.

此工作簿

OP询问有关ThisWorkbook的问题，这个想法源自另一个

OP asks about ThisWorkbook, this idea arose out of a different SO question about compile errors. if one defines a variable as Workbook, the compiler will not enforce the standard Workbook interface. One is free to call extra methods not found in the standard interface I guessed that this was because ThisWorkbook can be used as an extensibility mechanism.

此工作簿从插入功能"对话框中隐藏功能

有趣的是，ThisWorkbook在插入函数"对话框中隐藏了一个函数，因此这是实现OP要求的更简单方法！

What is interesting is that ThisWorkbook hides a function from the Insert Function dialog so it is a simpler way of achieving OP's requirement!

此工作簿从Application中隐藏功能和子项.运行

实际上，由于Thisworkbook是类的单个实例，因此开发人员在其中定义的所有函数和子对象都不会添加到全局名称空间中，因此无法在它们上调用Application.Run.要执行它们，必须获取对库工作簿的Excel.Workbook对象的引用，并通过该实例调用方法.

Actually, because Thisworkbook is a single instance of a class then all the functions and subs a developer defines within it are not added to the global namespace so one cannot call Application.Run on them. To execute them one must acquire a reference to the Excel.Workbook object for the library workbook and call methods through that instance.

是的，这既适用于xlam也适用于xlsm.

Yes, this works for xlam as well as xlsm.

多亏了OP，我今天学到了一些东西.

Thanks to OP, I've learnt something today.

这篇关于如何在项目之间重用核心VBA功能(UDF)，但不在单元格插入功能中显示它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！