How to reuse Core VBA (UDF) functions on projects, but not show them in the cell insert function

Question

I have an Addin with “core” functions and signatures that I want to reference and use in different Addins or VBA projects. Due to the principles of code reuse and a single update.

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

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 insert function of the cell , since it returns a collection object, but I want to reuse it for several VBA projects inside VBA code.

Image of the current problem, the function of the object shows the cell insertion formula:

Study

I found SO1 , SO2 solutions for one project method using Option Private Statement .
However, this does not solve the problem due to limitations of other applications or projects .

When a module contains an Option Private Module, public parts, such as variables, objects, and custom types declared at the module level, are still available in the project containing the module, but they are not available to other applications or projects .

Then I found a question about the MRExcel formum - Hiding only VBA functions . Tom Schreiner suggests that I can use Custom Classes and implement functions inside Classes . Thus, they will not be available through the cell insert function, but are still available for my other projects.

Questions

• How to reuse Core VBA (UDF) functions for projects, but not show them in the cell insert function?
• Is Custom Classes the only solution?
• (opinion-based) Is my original philosophy of using core methods in multiple projects via excel addins (.xlam) reasonable?

Answer 1

PLEASE VIEW TO UPDATE BECAUSE I OPEN IN THIS STATEMENT WHAT I DO DO

In conclusion, you will have to put your function in the class in your appendix, but there is an additional step for creating cross-workbook scripts; you cannot use the New keyword for the outer class. Therefore, you need to write a function of the factory class that can be called from the outside.

The next problem is the link, you can use the links to the tools and link to the project to get an early link to its useful Intellisense, but you potentially create a rod for your back due to the loading sequence, addin will be loaded by any client call that has the link. An alternative is the Late Bound equivalent, which removes the link, but puts the burden on downloading the add-on on the developer.

Here are the steps ...

• Create a project, I named my FunctionLibrary.xlsm, I renamed the Project Property from 'VBAProject' to FunctionLibrary.

• Add a class to your project, I called MyLibrary, I set 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 book
6. Create a calling book, I called my FunctionLibraryCallers.xlsm and in the 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) 

Sub

code>

7. To start the top unit, you will need to go to Tools-> Links and Links FunctionLibrary.xlsm.
8. To run the bottom section, no tools are needed-> Link, although you will need to comment on the top to avoid compilation errors.

UPDATE: Folding feedback with a comment. The Hell DLL is when you move the code to the library, and then you have to worry about loading it, loading the correct version and the correct dependencies.

ThisWorkbook

OP asks about ThisWorkbook, this idea came from another SO> question about compilation errors. if you define the variable as a workbook, the compiler will not use the standard workbook interface. One of them has the right to name additional methods not found in the standard interface. I suggested that this was because ThisWorkbook could be used as an extensibility mechanism.

ThisWorkbook hides functions from the Insert Function dialog box.

Interestingly, ThisWorkbook hides a function from the Insert Function dialog box, so this is an easier way to achieve OP requirements!

ThisWorkbook hides features and subtitles from Application.Run

Actually, since ThisWorkbook is one instance of the class, then all the functions and sub-settings defined by the developer are not added to the global namespace inside it, so Application.Run cannot be called on them. To execute them, you need to get a link to the Excel.Workbook object for the library tutorial and call methods through this instance.

Yes, this works for both xlam and xlsm.

Thanks to OP, today I learned something.

Answer 2

Q1 & 2 - Another solution - 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-ons are still reasonable; creating a COM add-in is a significant additional effort.