excel - Is there a place to store VBA code that's accessible to all Microsoft Office products..?

Question

Is there a way of storing VBA source code that's common and accessible to all Microsoft Office products..? I have a variety of functions which I use in both Access and Excel, and sometimes even Word. I modify and add to the the functions and modules frequently. The copying, pasting, and maintaining of code between projects can get tedious, and I inevitably overlook something.

I know I could do something like write a class/ocx/addin in VB6 or DotNet, compile it, and reference it in my VBA projects, but I was hoping for something more simple.

If there was a way to store VBA code in independent files, and open them with the VBA IDE in a standalone mode of some sort, that would be outstanding, but I know the IDE doesn't work that way...as far as I know.

Answer 1

I know I could do something like write a class/ocx/addin in VB6 or DotNet, compile it, and reference it in my VBA projects, but I was hoping for something more simple.

Unfortunately that's your best bet.

As you know VBA code is hosted, wrapped-up in a host document: a VBA add-in is still tied to its host document (.xlam, etc.), and as such can't be shared with another host.

In VBA-land what's meant to be shared between VBA projects, is type libraries - things you compile separately from the VBA project, and reference from as many projects as you like.

If you have a VB6 IDE, you can compile a 32-bit DLL that VBA projects can reference. The problem is that it won't work with 64-bit hosts - the solution is a .NET type library, written in the .NET language of your choice, made COM-visible. Note that COM-compatibility does restrict what you can expose in your API: for example you can't expose generics, and method overloads will look weird.

If a type library isn't an avenue you wish to explore, then your choices are rather limited, and IMO sub-optimal.

IMO the only thing that can "work" is a bunch of exported code files in some common folder, and the VBA projects that need to use that code need to literally import these code files. The risk here being, that if you make any changes, other VBA projects using an unmodified version of that code will not "see" these changes, IOW by doing that you're multiplying the number of times you need to fix a bug by the number of projects using that code.

Or you could have some code that uses the VBIDE extensibility type library to ensure the set of imported modules always match exactly with the exported files in that common location.