Calling Private Macros / UDFs From Another Workbook (Add-In)

3
votes

This is a follow-up question for my previous question: How Can I Prevent The Suggestion of Custom VBA Functions When Writing Formulas in Excel?

I have implemented the suggestions made in the answer given in order to make my macros and UDFs private, in a way which allows them to be called within other modules in the same workbook but prevents them from being suggested when writing formulas.

I'm trying to produce an Add-In which contains reusable macros and UDFs, and in other VBA projects I am adding this Add-In as a reference (tools > references) so that I can call the functions directly rather than using Application.Run()

enter image description here

By doing this, projects using these reusable functions will be easier to write as the required / optional parameters can be seen at the point of writing the line, and will generally keep the code looking tidier.

By implementing the solution to my first question to make these functions private, the functions are accessible from within the same workbook, however are not accessible to other workbooks.

Does anyone know a solution to achieve this?

1
vbaexcelexcel-2010

1 Answers

2
votes

If it needs to be accessible beyond the project it's defined in, then it must be Public.

Make macros and UDFs public in a standard code module.

Implement the "utility" code in class modules, and make these classes PublicNotCreatable. Now export these classes and open the .cls file in Notepad.

Locate the VB_PredeclaredId attribute and switch it to True, save and reimport it back in - given class Utilities, a VBA project that references that addin can call the code without creating an instance of the class (it can't New it up anyway), but you can access its public members as you would a default instance of any UserForm class, by qualifying the method with the class name:

foo = Utilities.DoSomething(42)

This is because the PredeclaredId makes a global-scope instance of the class named after the class itself: now you can use that class as if it were a standard module (or a Shared class in VB.NET), and its members won't be available as macros or UDF's.