Easy way to share vba modules

[macroapa]

Hi, I have a load of VBA functions in access that I want to be able to
easily have available in any database.

Now I know I could export the module and then import it into the new
data base, but is there an easier way (maybe a dll or something????)

Cheers

 

[Karl Hoaglund]

Hi! Yes, there is a way. Create a new database, then create one or
more new Modules in the new database. Put into the module(s) all your
common functions etc. You're done! When you want to use the
functions from another database--which I'll call the client database--
you go into the code editor for a module in the client database and
select Tools - References. Select a the file type of "Access Database
(.mdb)" and select your library database. Voila. . . all your
functions are visible in the client code. (Be sure your functions are
defined as Public, the default setting).

Hope this helps.

Karl

 

[Tony Toews]

Hi, I have a load of VBA functions in access that I want to be able to
easily have available in any database.

Now I know I could export the module and then import it into the new
data base, but is there an easier way (maybe a dll or something????)

See my Add-in Tips, Hints and Gotchas page at
http://www.granite.ab.ca/access/addins.htm

Tony

 

[Tony Toews]

Hi! Yes, there is a way. Create a new database, then create one or
more new Modules in the new database. Put into the module(s) all your
common functions etc. You're done! When you want to use the
functions from another database--which I'll call the client database--
you go into the code editor for a module in the client database and
select Tools - References. Select a the file type of "Access Database
(.mdb)" and select your library database. Voila. . . all your
functions are visible in the client code. (Be sure your functions are
defined as Public, the default setting).

Yup, but there are issues.

1) You can't create an MDE which references an MDB. The reference
must be changed to use an MDE.

2) If you make changes to the referenced MDE you must also
redistribute the "main" FE MDE as well.

3) If while programming and you are referncing an MDB you can make
changes to the refenced MDB code they will be immediately lost when
you exit the referenced MDB. They will not be saved. This makes
debugging problems a bit trickier. Yes, I found this out the hard
way. <smile>

There are likely a few more things I've forgotten but they'll be on my
web page in my other posting.

Tony

 

[Karl Hoaglund]

Excellent points, Tony. It sounds like you'd have to create a MDE
version of your library database every time you change it, and
distribute the updated library MDE with your application.

And yes, I too have run into the problem you mentioned in your point
3): Access lets you merrily make changes to the library code while
you're debugging, but then as soon as you close your file, it drops
all your changes without even telling you it is going to do it! Very
surprising and annoying.

Thanks for your comments.

 

[Tony Toews]

Excellent points, Tony. It sounds like you'd have to create a MDE
version of your library database every time you change it, and
distribute the updated library MDE with your application.

Correct.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[David W. Fenton]

Yup, but there are issues.

1) You can't create an MDE which references an MDB. The reference
must be changed to use an MDE.

2) If you make changes to the referenced MDE you must also
redistribute the "main" FE MDE as well.

3) If while programming and you are referncing an MDB you can make
changes to the refenced MDB code they will be immediately lost
when you exit the referenced MDB. They will not be saved. This
makes debugging problems a bit trickier. Yes, I found this out
the hard way. <smile>

There are likely a few more things I've forgotten but they'll be
on my web page in my other posting.

For what it's worth, you can avoid the reference and instead use
Application.Run for a lot of things, though it's not as convenient,
and won't have compile-time checking.