Excel 2013 vba: Is there a possibility to crate a library with functions and classes that can be used in any other workboob

Over time I created a number of "reusable" macros which I'd like to make automatically accessible for other workbooks I'm using. The only possibilities I've come across is storing those macros in e.g. a Personal.xls file in the XLSTART Directory of Excel or to store them as an Add-in in a xlam file. The downside of all of this (if I understand) is, that functions can only be called via RUN with no possibility to pass Information back and forth to the calling macro. And if I understand it correctly, an instantiating of a class defined in this library is not possible at all.
So the only Advantage - if at all - would then be to have such a library open when any workbook is open in order to copy / past code to the individual workbook?
I wonder whether there are (other) possibilities to use functions and classes defined in a library with passing Information back and forth?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCost AccountantCommented:
I think what you need is the xlam as you mention.

You install this into excel and the code should be readily available.
This includes class modules and functions.

The use of an addin can change the ribon to allow for a custom tab at the top of the excel along side those such as Data etc. Functions are available with all the same help and tips as with a normal native function.

What you do will depend on what you need to do with the code.
As any functions you have will not be available to those without the addin.

An addin is easy to create and test.
But once you get into changing the ribon it can get more complicated.
So giving it a try may be best.

Roy CoxGroup Finance ManagerCommented:
There's more details for creating an addin here


You can even deploy them across a Network if you need to

KKressAuthor Commented:
@Steve, Roy: Thanks both for your comments and links. Unfortunatelly it Looks like I didn't correctly Phrase what I was looking for. So let me descrive the Scenario:
* In the addin I'd like to store a couple of General purpose "Utility" functions. Example could be: a TOKEN function, that Returns the n-th token of a string based on token seperators like chr(13). The function would accept a TextString, a SeperatorTextString, and a TokenNumberInteger and Returns the TokenString.
* In a workbook WB1 I'd like now to Analyse a string by calling this Token function and pass the three Parameters specifically for the purpose and use within this workbook.
* In another workbook WB2 I have e.g. a Webpage which I want to Analyse., passing Webpage Content, HTMLtag, and tokennum as Parameters to the Token function.
So the intent is not to code everything in in the addin, but to provide a set of General purpose Tools for use in individual workbook macros.
English is not my mother language, but I hope I could be more precise in what I was looking for.
Any ideas?
Thanks in advance.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.


you say
no possibility to pass Information back and forth
you could use this syntax

Res = Application.Run("'my XLAM file.xlam'!myFunction", Arg1, Arg2)

you can also use class modules from add-ins

pls see


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
FYI, it is in fact possible to have classes in one project that you can declare and create in another. You can either export the class module, change its Instancing in the exported text file and then re-import it, or simply use a line of code like:

activeworkbook.VBProject.VBComponents("Class1").Properties("Instancing") = 5

Open in new window

KKressAuthor Commented:
@Rgonzo1971: That was exactly what I was looking for. Many thanks for your answer!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.