Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Creating a Common VBA Library

Posted on 2004-04-04
6
Medium Priority
?
570 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:apollois
6 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 10754869
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
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 10755623
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
 
LVL 17

Accepted Solution

by:
inthedark earned 1200 total points
ID: 10755647
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 10756681
Three words: Active X Object.

Design an Active X OCX or DLL.


-Brian
0
 
LVL 10

Author Comment

by:apollois
ID: 10760982
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
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 10762742
Thanks apollois for the link.
Didn't knew about this.
Jeroen
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question