Как повторно использовать основные функции VBA (UDF) в разных проектах, но не показывать их в функции вставки ячейки

Question

У меня есть надстройка с "основными" функциями и подпрограммами, на которые я хочу ссылаться и использовать в разных надстройках или проектах VBA. Из-за повторного использования кода и единичных принципов обновления.

Например, функция, которая фильтрует элементы коллекции на основе критериев и возвращает вложенную коллекцию. Сам код здесь не проблема.

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.

Изображение текущей проблемы, функция объекта отображается в формуле вставки ячейки:

Исследование

Я нашел решения SO1, SO2 для одного метода проекта, используя Option Private Statement.
Это, однако, не решает проблему из-за ограничений других приложений или проектов.

Когда модуль содержит Option Private Module, открытые части, например переменные, объекты и определяемые пользователем типы, объявленные на уровне модуля, все еще доступны в проекте, содержащем модуль, но они не доступны для других приложений или проектов.

Далее я нашел вопрос по формуле MRExcel - скрытие только функций VBA. Том Шрайнер предполагает, что я могу использовать пользовательские классы и реализовывать функции внутри классов. Таким образом, они не будут доступны через функцию вставки ячейки, но будут доступны для других моих проектов.

Вопросы

1. Как повторно использовать основные функции VBA (UDF) в разных проектах, но не показывать их в функции вставки ячейки?
2. Является ли решение для пользовательских классов только одним?
3. (Основано на мнениях) Является ли моя оригинальная философия о разделении основных методов между несколькими проектами через Excel надстройки (.xlam) разумной?

Answer 1

ПОЖАЛУЙСТА, ПРОЧИТАЙТЕ, ЧТОБЫ ОБНОВИТЬ, ПОТОМУ ЧТО Я УЗНАЛ, ЧТО ЭТО РАБОТАЕТ, ЧТО ОПТОМ ХОЧЕТ

Таким образом, вам нужно будет поместить свою функцию в класс в вашем дополнении, но есть дополнительный шаг для того, чтобы начать работать с сценариями кросс-книги, вы не можете использовать New Ключевое слово на внешний класс. Поэтому вам нужно написать функцию фабрики классов, которая может вызываться извне.

Следующая проблема - связывание, вы можете использовать Tools References и сделать ссылку на Project, чтобы получить раннее связывание с его полезным Intellisense, НО вы потенциально можете создать стержень для вашей спины из-за загрузки последовательностей, надстройка будет загружена любым вызывающим клиентом, который имеет ссылка. Альтернативой является эквивалент с поздним ограничением, который исключает ссылку, но возлагает бремя загрузки надстройки на разработчика.

Вот шаги...

1. Создайте проект, который я назвал моим FunctionLibrary.xlsm, я переименовал свойство Project из 'VBAProject' в FunctionLibrary.

2. Добавить класс в ваш проект, я назвал MyLibrary, я установил Instancing в 2 - PublicNotCreateable, Я добавил (простой) следующий код

    Option Explicit

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

3. Добавьте стандартный модуль с именем 'modEarlyBoundClassFactory' и добавьте следующий код

    Option Explicit

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

4. В модуле ThisWorkbook я добавил следующий код

    Option Explicit

    Public Function CreateMyLibraryLateBoundEntryPoint(ByVal sLicenceKey As String) As Object

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

    End Function

5. Сохранить книгу
6. Создайте рабочую книгу, я назвал свой 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)

End Sub

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)
End Sub
 Чтобы запустить верхнюю подпрограмму, вам нужно перейти в Инструменты-> Ссылки и ссылки на FunctionLibrary.xlsm. 
 Для запуска нижней подпрограммы не требуется Tools->Reference, хотя вам придется закомментировать верхнюю подпрограмму, чтобы избежать ошибок компиляции. 
ОБНОВЛЕНИЕ: сворачивание в комментариях комментатора.  DLL Hell - это когда вы перемещаете код в библиотеку, а затем вам нужно беспокоиться о его загрузке, загрузке правильной версии и правильных зависимостей.
ThisWorkbook
OP спрашивает об этой рабочей книге, эта идея возникла из другого SO вопроса об ошибках компиляции. если кто-то определяет переменную как Workbook, компилятор не будет применять стандартный интерфейс Workbook. Можно свободно вызывать дополнительные методы, которых нет в стандартном интерфейсе. Я догадался, что это ThisWorkbook может быть использован в качестве механизма расширения.
ThisWorkbook скрывает функции из диалогового окна "Вставка функции".
Что интересно, так это ThisWorkbook скрывает функцию из диалогового окна "Вставить функцию", так что это более простой способ выполнения требования OP!
ThisWorkbook скрывает функции и подпрограммы от Application.Run
На самом деле, потому что Thisworkbook это единственный экземпляр класса, тогда все функции и подпрограммы, определяемые в нем разработчиком, не добавляются в глобальное пространство имен, поэтому нельзя вызывать Application.Run на них. Для их выполнения необходимо получить ссылку на Excel.Workbook объект для книги библиотеки и вызов методов через этот экземпляр.
Да, это работает как для xlam, так и для xlsm.
Благодаря ОП я сегодня кое-что узнал.
                                        
                                    
                                

Answer 2

Q1&2 - Другое решение - вы можете создать COM-надстройку:

Функции надстроек COM нельзя напрямую вызывать из формул ячеек на листах.

https://support.microsoft.com/en-au/help/291392/excel-com-add-ins-and-automation-add-ins

Q3 - Да надстройки все еще разумны; создание COM-надстройки требует значительных усилий.