从另一个工作簿调用私有宏/ UDF（加载项） [英] Calling Private Macros / UDFs From Another Workbook (Add-In)
问题描述
这是我上一个问题的后续问题：
通过这样做，使用这些可重用功能的项目将更容易编写，因为可以在写一条线，通常会保持代码看起来整洁。
通过实现我的第一个问题的解决方案，使这些功能成为私有的，这些功能可以从同一个没有其他工作簿可以使用工作簿。
有没有人知道实现这一点的解决方案？
如果需要在项目定义之外进行访问，那么它必须是 Public 。
Make宏和UDF在标准代码模块中公开。
在类模块中实现实用程序代码，并使这些类 PublicNotCreatable 。现在导出这些类并在记事本中打开.cls文件。
找到 VB_PredeclaredId 属性并将其切换到 True ，保存并重新导入 - 给定类实用程序，引用该插件的VBA项目可以调用代码，而不需要创建一个类的实例（它不能新的它，无论如何），但您可以访问其公共成员，因为您将默认实例的任何 UserForm 类，通过使用类名称限定方法：
foo = Utilities.DoSomething（42）
这是因为 PredeclaredId 创建一个以类本身命名的类的全局范围实例：现在可以使用该类，就像它是一个标准模块（或 Shared VB.NET中的类），其成员将不会以宏或UDF的形式提供。
This is a follow-up question for my previous question: How Can I Prevent The Suggestion of Custom VBA Functions When Writing Formulas in Excel?
I have implemented the suggestions made in the answer given in order to make my macros and UDFs private, in a way which allows them to be called within other modules in the same workbook but prevents them from being suggested when writing formulas.
I'm trying to produce an Add-In which contains reusable macros and UDFs, and in other VBA projects I am adding this Add-In as a reference (tools > references) so that I can call the functions directly rather than using Application.Run()
By doing this, projects using these reusable functions will be easier to write as the required / optional parameters can be seen at the point of writing the line, and will generally keep the code looking tidier.
By implementing the solution to my first question to make these functions private, the functions are accessible from within the same workbook, however are not accessible to other workbooks.
Does anyone know a solution to achieve this?
If it needs to be accessible beyond the project it's defined in, then it must be Public.
Make macros and UDFs public in a standard code module.
Implement the "utility" code in class modules, and make these classes PublicNotCreatable. Now export these classes and open the .cls file in Notepad.
Locate the VB_PredeclaredId attribute and switch it to True, save and reimport it back in - given class Utilities, a VBA project that references that addin can call the code without creating an instance of the class (it can't New it up anyway), but you can access its public members as you would a default instance of any UserForm class, by qualifying the method with the class name:
foo = Utilities.DoSomething(42)
This is because the PredeclaredId makes a global-scope instance of the class named after the class itself: now you can use that class as if it were a standard module (or a Shared class in VB.NET), and its members won't be available as macros or UDF's.
这篇关于从另一个工作簿调用私有宏/ UDF（加载项）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
