Solved

Creating a Common VBA Library

Posted on 2004-04-04
6
560 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 300 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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