We all re-use code in one form or another. Perhaps it begins by copying and pasting routines into a text file.

Perhaps you’ve used people’s online repositories of code that they place online, or you share your code in forum posts and search there for code help.

Maybe you find yourself browsing between many projects and looking for a snippet of something you’ve done in the past.

In any case, there is always a need to be able to retrieve and lookup code that has been used previously to achieve some outcome in your current application.

One strategy I’ve tried to use is making code relatively generic. Finding atomic sized pieces of code you need over and over again which just make life a little easier. Perhaps it’s a function to wrap text in single quotes for use in building SQL query strings, or perhaps it’s certain functions like Max and Min that are just missing in VBA. These types of functions can go in an Access database and you can create a reference to that database in the VBE editor. The functions you’ve created can be used globally in another database.

There are some caveats to using libraries. If you are sharing class modules there are some hidden options you can turn on by exporting the module to text, then adding headers to the text, then reimporting. In order to get a class module to be seen by another database, this is something you need to do. But if you are just sharing public atomic modules that contain small helper functions, this can be a good solution.

The problem with libraries that you use across multiple applications is that they may be difficult to maintain. If you fix bugs or improve functions in your library, you may want to update that library for all of your older applications. Without testing though, this could prove problematic if you’ve changed the function name or function arguments, or it no longer behaves the same way and that behavior was expected in the old system. Ultimately, placing a library into it’s own Access file can help if you place the code into a versioning system and make sure you can easily refer to the version of the library in each application.

Using version control can allow you to make sure you are able to more easily update the library across applications, since you could place a separate working copy with each application and update it and import it as needed.

Maintaining code can be challenging, but as you work to systematize the maintenance and updating, there are many benefits!