[AccessD] Modules and libraries in Office

Colby, John JColby at dispec.com
Mon Nov 8 11:06:07 CST 2004


This is an area that provides a stark glimpse into the disparate roots of
the various Office Applications.  There is plenty of code that can / should
run in any of the applications.  Aside from the fact that I use a table to
pull initialization values for the framework, the framework itself (or at
least the foundation) has nothing whatsoever that is platform specific.  It
sets itself up, it provides hooks for services such as zip / unzip, ftp,
error logging etc.  All of that is just VBA.  No references to forms or
reports.  All that stuff should work just fine from Word, Excel, Outlook
etc.  There are developers who create entire applications around Outlook for
example.  They live, breath and die inside Outlook.  Yet the framework could
provide a valid foundation for an Outlook application just as easily as for
an Access application.

Of course a DLL can be created, but that is nowhere near as simple as what
can be done in Access (for example) where the app and the mda are placed in
the same dir, the app references the mda and starts using it.  No visual
basic compiler, no registration of a DLL, no registry keys, no nada.  Just
use it.

I played around with Excel this morning for a few minutes.  I was able to
create a class.  I could not for the life of me figure out how to change the
NAME of the class although it is probably possible somewhere.  I could place
an ocx combo (for example) on a spreadsheet.  I could set the combo to bind
to a cell, take its values from a range.  The combo can fire events handled
in the sheet's module.  I could dim a variable for a class which sinks the
combo events etc. etc.  All the pieces are there to write pretty powerful
aps from right inside Excel.  Very similar, yet distinctly different from
doing it in Access.  

So now I have a framework that is just VBA, but it is in an Access MDA.  In
order to use it in Excel I have to either somehow move it all out to an
excel set of classes / modules, stored in a workbook or something, or go to
an entirely external environment, use a compiler to create a dll, then
install and reference that dll.  

John W. Colby
The DIS Database Guy


-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Monday, November 08, 2004 11:28 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Modules and libraries in Office


But remember, Access doesn't use the same kind of forms that VB and the
other Office apps use.  If you want to share code, create a dll and
reference it in the various projects.

Charlotte Foust


-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com] 
Sent: Sunday, November 07, 2004 5:37 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Modules and libraries in Office


Holy cow, is that a mish mash or what?  The point of my questions was to
try to find a common ground where code could be stored and run from all
of the various office applications.  Not to be it would seem.  It does
seem bizarre that if I had some function that should be able to run in
any of the apps I have to save the same thing in 5 different places so
that Access, Excel, Word, Powerpoint and Outlook could use it.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Sunday, November 07, 2004 2:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Modules and libraries in Office


In Excel you can store functions and modules as Public in .xla  files. 
Old Excel pre 97 didn't store the code in xla it
looked like an mde file. Matter of fact you can call these .xla files 
from Access. I have called functions from the Excel Statistical and 
Financial Analysis Packs.

In Outlook 2000  and any code that you create in the Outlook VBA 
environment is stored in a file named VBAProject.otm.
Even though you can copy the VBAProject.otm file, that's not a good way 
to distribute Outlook macros company-wide. The recommended method is to 
create an Outlook or Word  COM add-in. Talk to Shamil he has been 
upgrading com addins to dotnet. http://www.outlookcode.com/d/vb.htm
http://www.outlookcode.com/d/comaddins.htm

Word uses templates either user or workgroup (to be shared) in a 
specific folder
A template  contain macros or other customizations such as toolbars, it 
works better from some locations than others.
Again these com addin's can be used in place of macro's.
see
http://word.mvps.org/FAQs/index.htm

Just for future reference this method is also partially available under 
word and excel

Under the Tools/References menu in the VBE, you need to check the
Microsoft Visual Basic for Applications Extensibility 5.3 object
library. Microsoft Visual Basic for Applications Extensibility 5.3
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB


The following code exports the contents of the Code Module to a file
called test.bas. This can be run from Word or Excel

Application.VBE.ActiveVBProject.VBComponents("Module1").Export("test.bas
")
I think you can also import code this way but never tried it or got it 
to work or something.

John W. Colby wrote:

>In Access we use MDA/Es to store libraries of reusable code.  Is there
>an equivalent for Word / Excel / Outlook etc (the rest of Office)?  Do 
>these other platforms have references like we do in Access?  If so, can

>you reference an MDA/E to use code in it?
>
>John W. Colby
>www.ColbyConsulting.com
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list