Code Libraries

Maggie asks what I use for a code library. Sadly, nothing. When I need a piece of code that I’ve used before, I go find the project where I used it and copy it out of there. The success rate on that method is about 70% – success meaning that I find what I’m looking for in a reasonable amount of time. Ten percent of the time I never find it, and basically start from scratch.

The final 20% constitutes finding it in an unreasonable amount of time. When that happens, I copy it in a blank workbook and save the workbook with a really long, descriptive name so I can find it the next time.

I have the developer addition of MS Office, which I believe ships with a code library, but I don’t know where it is, so maybe I didn’t install it.

Also, since I started this blog, I’ve been adding code to this category, VBA Code Library. I reuse a lot more code than what’s here, however, because I’ve constrained myself to putting ready-to-use code here. A lot of the code that I reuse isn’t quite general or self-contained enough that I feel comfortable publishing it. Ultimately I’d like to have this category be my only source for reusable code. I just need to figure out how to present that code that’s not in a copy-and-run state.

Well, it must be Friday because I’m giving you nothing and instead asking you for something. What do you use as a code library, if anything? How self-contained is the code in your library? Is there a benefit to having a code library on the web?

Posted in Uncategorized

11 thoughts on “Code Libraries

  1. Could be that you could put a good deal of reusable stuff especially vba (but could be Sheets too) into an .xla file which is loaded whenever you fire up Excel. That way you could avoid having to copy paste old stuff into new projects. Any improvements to, or bug fixes made in routines in the xla will be automatically implemented for any project which uses them.

  2. I have the developer version codelib, containing lots of good things. It’s also a great application. So I use that 2% of the time.

    And I have an email folder “code”, containig tons of amazing newsgroup solutions forwarded to myself. I never open that, 0% usage, and I wonder what gems are really in there after all these years, has to be brilliant. Guess I’ll transfer it to the codelib one day.

    The rest is copy-paste from existing projects, and doing that I’m totally confused about the refinements and versions of the code. Sad, isn’t it ?

  3. I too pull code from existing projects.

    If something is generic enough and I’ll likely use it again, I’ll put it on my website.

    Sometimes it’s scary though. I’ll open an existing project for some code, copy it out and realise there’s a bug. Wonder how many other places I copied that code to…

  4. I use a combination of Dick’s, Harald’s, Hugh’s, and Rob’s techniques. My personal.xls serves as a collection of things which I actually use for myself. I have a few folders on my hard drive that contain ready-to-use or nearly ready-to-use routines. When I find that I’ve copied the same bit two or three times, I pop it into one of these folders.

    Harald, my collection of emails and old newsgroup posts is over 100 MB. I’d say my usage of this is 2%. It’s facilitated by an incredibly anal directory tree structure. Often I go in looking for something that seemed so brilliant five years ago when I was just embarking on this VBA stuff, and realize that what I’d saved wasn’t very good. In my “spare time” I plan to reorganize this archive, right after I clean the basement.

    I plan to give Gary Beene’s utility a test, though, because my definition of “reasonable time” for a search has shrunk now that I’m working for myself.

    – Jon

  5. Hi,
    I use this one, it puts snippits & files in a database:

    Unfortunately most of the documentation is in Chinese but I haven’t had any real need to look anything up since it is pretty intuitive. I have many databases going for a bunch of different areas.


  6. 1. I keep a bunch of code in Personal.xls. This file does get big and slows down Excel’s loading, so I occasionally use Rob Bovey’s VBA Code Cleaner to reduce the file size.

    2. I was disappointed that I couldn’t search in VBA modules using the regular desktop search, so I exported a bunch of my more useful modules; now that they’re just regular ASCII text, it’s easier to find stuff by doing a desktop search.

    3. Google search, esp. the newsgroups.

    4. This blog, and

  7. i cant seem to find code to go with what i need to do. i have lets say, 30 products on a page, one product consists of a picture and some prices. what i need is a code that can be added to a button that i click to add the picture and the price to the checkout page in the next avalable place(not overlaping). i don’t know were this will end up but i am hoping this pirson or computer (hopfully not) can help me

  8. A really late adder to this thread – but I finally had enough with not being able to find code in modules, so while waiting for one of the desktop search programs to get smart enough to “see” modules I developed a workbook that (optionally) recursively searches directories for .xls files and creates a list on a worksheet.

    A second set of subs generates an plain text copy of any sheet code, ThisWorkbook code and code in any modules. That’s written to a file, whose name is based on the original workbook’s name, plus a timestamp taken from the file.

    With all that being written to a “XLS Code Bank” directory, I’ll let Copernic do the indexing. There are some issues with password-protected files and the code could be polished a bit, but I’m at a wait-and-see point to learn if it’s worth the additional effort

    Code cheerfully available to anyone who wants to use (and especially improve on) it….

  9. I use a separate workbook called scripts.xlsm. On sheet1 I have about 30 shapes marked with the names of processes I use frequently. I click on the shape I want and it loads the code into memory. Then I paste it in my new project.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.