How to reuse Core VBA functions (UDFs) across proj

问题:

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

I do not want to show this function in the cell insert function, since it returns an collection object, but I want to reuse it across multiple VBA projects inside VBA code.

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

Research

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.

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.

Questions

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?

回答1:

PLEASE SCROLL DOWN TO UPDATE BECAUSE I HAVE DISCOVERED THISWORKBOOK DOES WHAT OP WANTS

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.

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.

Here are the steps...

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

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

3. Add a standard module called 'modEarlyBoundClassFactory' and add the following code

    Option Explicit

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

4. In the ThisWorkbook module I added the following code

    Option Explicit

    Public Function CreateMyLibraryLateBoundEntryPoint(ByVal sLicenceKey As String) As Object

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

    End Function

5. Save the workbook
6. Create a calling workbook, I called mine FunctionLibraryCallers.xlsm and in a new standard module I added the following code

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

7. To run the top sub you'll need to go Tools->References and references FunctionLibrary.xlsm.
8. To run the bottom sub requires no Tools->Reference though you'll have to comment out the top sub to avoid compile errors.

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.

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 hides functions from Insert Function dialog

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!

ThisWorkbook hides functions and subs from Application.Run

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.

Yes, this works for xlam as well as xlsm.

Thanks to OP, I've learnt something today.

回答2:

Q1&2 - Another solution is you can create a COM Add-in:

COM Add-in functions cannot be directly called from cell formulas in worksheets.

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

Q3 - Yes add-ins are still reasonable; creating a COM Add-in is a significant amount of additional effort.