Creating a Common VBA Library

What is the best way to setup/maintain a common VBA library that can be used by all Office apps?

I have a number of VBA procedures and functions that can be used by all of the Office apps.  Is there a better way to share this lib other than having a separate module that's loaded into the VBA IDE for each app?

I would like to have one file that I maintain that can be referenced by all the Office apps.
LVL 10
apolloisAsked:
Who is Participating?
 
inthedarkConnect With a Mentor Commented:
The simple way is to paste you VBA code into a VB Dll.  You can then use the DLL from any office product by using the CreateObject to make your functions avaiable.
0
 
byundtCommented:
apollois,
I posted a link to this question in the Visual Basic programming technical area http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20943430.html
byundt--Office TA Page Editor
0
 
Jeroen RosinkSoftware testing consultantCommented:
Hello Apollois,

I never saw a solution where all macro's are called in one way. for all applications. (all companies have their own templates for excel , word and powerpoint)

what you might do is make use of import of modules.

in excel you can places lines of code into a personal.xls file (perhaps create one yourself and locate them in the XLSTART folder.
example of importing module in xls.
http://www.exceltip.com/show_tip/Modules,_Class_Modules_in_VBA/Insert_a_new_module_from_a_file_using_VBA_in_Microsoft_Excel/507.html

the same thing you can do in word by creating a DOT file.

regards,

Jeroen
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BrianGEFF719Commented:
Three words: Active X Object.

Design an Active X OCX or DLL.


-Brian
0
 
apolloisAuthor Commented:
Thanks byundt for posting the link in the VB section.

Turns out it is very simple once you know how. :-)

inthedark is the closest to my final solution -- but it is even simpler that he/she stated.

Once you create the DLL, and add it as a ref in any Office App, all Public Subs and Functions are available directly.  You do NOT need to use CreateObject.

Here is the MS KB Article I found that tells you how to do this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrCreatingDLLThatActsAsCodeLibrary.asp

BTW Jeroen, I don't doubt that most people/companies copy/paste code from one Office App VBA to another.  But my whole point is to specifically aviod doing this.  With my VBA Lib DLL solution, I have ONLY one place to maintain the source code.  My VBA Lib does NOT contain any Office App specific code -- it is general purpose VBA utility routines that can/are used by all Office Apps.

Thanks for all your suggestions.
0
 
Jeroen RosinkSoftware testing consultantCommented:
Thanks apollois for the link.
Didn't knew about this.
Jeroen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.