VBA Tips? : Store common code in universal library (1 Viewer)

[ironfelix717]

Hi,

Definitely covered before, but curious what the pros are doing...

What is the best solution for accessing a custom VBA code base for multiple projects? A single library that is shared across multiple projects on a single local machine. Making changes to a few main projects that share the same copied library is getting old lol...

The criteria for such a solution would be:

1. Security: Cannot be modified maliciously or otherwise be any less "secure" than VBA already is.
2. Ease of editing: Need to make a change to the code, I want to do it fast.
3. Ability to have some type of view of the structure of the library to make development easy.
4. Ideally not Access specific, but supports Excel, Word, etc.

I think I once read about storing code in an .accdb and accessing it somehow.


Thanks a lot---

 

[The_Doc_Man]

There are products that do this, usually called "code managers" for the more limited products, or "configuration managers" for the ones that handle more than just code. (E.g. drawings, documentation, ...) You might search the web for such products.

The way the U.S. Navy did that was with a dedicated mainframe server with the management product to track whole-project configurations which included code, engineering drawings, contract info, testing info, and other stuff. In essence it locked the folders to become read-only except for folks who had a group identifier for the "Shared Code Manager" group. (That was a domain group name we created for the purpose.) The database for the folder included some hash codes for each file (to verify that the code hadn't been altered) and some text describing the purpose of each file. plus of course the fully qualified file specification. The app also allowed you to check out a module, modify it, and submit it as a replacement for the original. The shared code managers would decide if it was a worthy change and if so, would make the actual replacement.

For small businesses, some of the "big boy" stuff might be more than is desired, so whatever you look at online, decide the magnitude of project that it would cover and remember that, sometimes, small and simple are adequate.

 

[CJ_London]

to include in an application, you simply need a .accdb or .accde and add it into your library via the vba library . Not sure if an excel or word file will read either (probably not a .accde) - or an access file will read a word or excel file, you'll need to try it.

Each user needs their own copy. So if you make a change, you still need to distribute it (appreciate not in this case)

 

[CJ_London]

Further to above - excel vba does not recognise the NZ function (don't know about word) - so if it tries to use a library function which uses nz, it will fail. There may be other oddities.

Other thing to be aware of, if an access library file references currentdb, that refers to the current db of the calling app. To reference a db in the library file, use codedb.

I used to use library files (purely for Access) but found the management of the library became too onerous. Changes would have to be tested in each application and any change to parameters would required modifying all applications. In your case, excel does not have datatypes as such in the spreadsheet, all cells are variants so may require functions to be rewritten to some extent.

Since you don't want to be access specific, I suspect you will need to late bind all applications

 

[Pat Hartman]

Access supports code libraries. Either .mda or .accda depending on what version of Access you're using.

I keep useful code in an accdb and I extract the code I want to use in a particular application and import it directly. I don't like using a single shared code library because I have to update all instances of it and that can sometimes be difficult. It also makes testing difficult. If I want to modify the shared library, I have to modify the app that will use the code to reference my test code library until everything is tested. Then I have to deploy the updated code library and change the app I modified for testing to go back to using the shared code library and deploy that. Too many moving parts.

Somehow, once code is working I don't generally have to change it again so constantly updating shared code has never been a problem. Keeping a single code library is the problem. If you want to keep one per app, that is probably easier to manage.

I also have "mini-apps" which are sets of forms, etc that all work together and can be used in diffeent applications. Again, I import all the objects and don't worry about having to modify them later. If I come up with something better, I will modify the app that needs the "better" and then update my code library so the next time I deploy that code, it contains the latest feature.