Solved

Creating a Common VBA Library

Posted on 2004-04-04
6
559 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Office 365 Training for IT Pros

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.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

710 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