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 to 2 - PublicNotCreateable. I added the (simple)
following code
Option Explicit
Public Function Add(x, y)
Add = x + y
End Function
- 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
- 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
- Save the workbook
- 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
- To run the top sub you'll need to go Tools->References and references FunctionLibrary.xlsm.
- 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.
