Limit 'Public' scope to project only with VBA Add-in in Excel (XLAM)

Question

Is there any way to limit the public scope of non-interface routines inside my XLAM VBA project to the add-in only?

Right now, I am trying to write an add-in which should have several interfaces (public). Within my project, I want to use several modules to separate and organize my functionality. If I now want to do a cross-module routine call, I need to declare the called routine 'Public'. At the same time, this declaration causes my add-in to expose these routines as well.

If it is not possible to discriminate like that, would there be a way to make them hidden?

Answer 1

I'm not sure I follow. If you mean you don't want Functions to be visible in the Excel function wizard or usable in cell formulas head the module

Option Private Module

However all routines are available externally with say Application.Run, even if Private or in a Private module.

Peter Thornton

Answer 2

Peter:

Not only available like that in my worksheet, but also not directly visible/available from another VBA project that makes use of the XLAM functionality.

Let's say I have a math library XLAM with interface routines for all kinds of functions defined in one VBA code module. For example a distribution function based on an input array with an optional normalization argument.

After the distribution is done and the normalization argument is set to true, I would call an internal function, located in another module of the XLAM. As I need to reach this function, I assume it should be publicly available? If this is the case, then my XLAM not only exposes my interface functions, but also my internal 'public' functions.

I don't mind if eventually you could still reach it with things like Application.Run, but I don't want them to show up with code completion.

On the side: would they conflict with functions with the same name in other XLAM projects?

---

Sometimes we tend to think too much out of the box and forget that we still have a lot to digest inside of it...

Answer 3

After the distribution is done and the normalization argument is set to true, I would call an internal function, located in another module of the XLAM. As I need to reach this function, I assume it should be publicly available?

It depends what you mean by publically available and I'm still not quite sure what you mean by terms like "interface functions". I'll try and summarise (possibly repeating what I've already said)

Functions (UDFs): these are visible in the Excel UI in the function wizard Shift-F3 and in cell formulas but only if not in a module headed "Option Private Module" (without quotes) and if Function is not prefixed with Private. Functions that are not intended for use as UDFs should always be placed in a module headed "Option Private Module", thereafter they can be left as public so they can be called between modules

Sub routines (aka macros): In addins these are never visible in the Excel UI via Alt-F8. However in normal workbooks (incl say Personal) they can be hidden from the Alt-F8 menu by placing in a Private module (the Subs themselves can be left as Public so they can be called between modules).

All procedures in normal modules (Subs & Funcs etc) that are not prefixed with Private, even if in a Private module, can be called from other projects either with Application.Run or by setting a reference to the callee project, and "seen" in various other ways.

I don't mind if eventually you could still reach it with things like Application.Run, but I don't want them to show up with code completion.

If by "code completion" you mean intellisense, that will only be available if a reference has set to the other project, and if the procedure is fully public. Normally only the extrermely inquisitive will attempt to do that unless explicitly required to do so.

On the side: would they conflict with functions with the same name in other XLAM projects?

For sure it's best not to use similar names for functrions intended as UDFs in different addins. Otherwise normally there shouldn't be any conflicts unless a reference has been set to one or more projects that include similar non private procedure names, including in the original project.

FWIW you might want to lock the project with a password before distributing.

Peter Thornton

Answer 4

Actually "Option Private Module" seems to be the thing I was looking for. Thank you very much, especially for the extended answer.

Between modules I should be able to call the functions, but from outside the project I don't want them to be visible. That's why I also asked for the name conflicts. (note that this should only hold for supporting functions in my case, not directly for UDFs)

I marked your second reply as answer.

BTW: passwords are no issue, this is an internal thing only, but I try to organize my code a little such that I can reuse it easily as well.

---

Sometimes we tend to think too much out of the box and forget that we still have a lot to digest inside of it...