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
- How to reuse Core VBA functions (UDFs) across projects, but not show them in cell insert function?
- Is the Custom Classes solution only one?
- (Opinion-based) Is my original philosophy about sharing the core methods accross multiple project through excel addins (.xlam) a reasonable one?
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...
Create a project, I called mine FunctionLibrary.xlsm, I renamed the Project property from 'VBAProject' to FunctionLibrary.
Add a class to your project, I called my MyLibrary, I set the
Instancing
to2 - PublicNotCreateable
. I added the (simple) following codeUPDATE: 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 callApplication.Run
on them. To execute them one must acquire a reference to theExcel.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.
Q1&2 - Another solution is you can create a COM Add-in:
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.