Organizing and Storing Code/Formulas

Hello everyone,

In my time on here, I've accumulated a bunch of sample workbooks, code snipets, etc from answering various threads. I'd like to create some sort of library so that I can look up examples of previous code/formulas that I've written when I need to do something similar. The issue is, that it's difficult for me to understand what my code was supposed to do unless I see the data that it was supposed to work on. This means one excel file (sometimes multiple) per thread. I haven't figured out a good way to organize a bunch of excel notebooks, or how to combine my code into fewer files and still understand what it's doing easily.

What do you guys do? I haven't been here that long. There must be people who have written tons more excel code than I. You must have some way to keep track of things. What's your system?

I'm not much of a programmer but I do have samples of code written by others.

On the other hand, I'm decent at formulas and I do have a massive library of formulas.

I have several folders that categorize the sample files by formula types. Such as:

Across Sheets
Address
Aggregate
Average
Consecutives
Count
Dates
Event Macros
Extract Data
etc
etc
etc

Not sure of the exact number of files but it's got to be around 700-800.

Some are duplicates as they can fall into more than one category. For example, getting a conditional average on filtered data. There would be the same file in the folders:

Average
Filtered Data

Most of the files have many formulas related to the subject file. So most of the files have many sheets. In files with many sheets I use a Table of Contents (or Index) sheet with hyperlinks to the individual sheets.

One of the biggest problems with this is coming up with short file names that succinctly describe what type of formulas are in the file.

I also have a file that lists all the file names by folder.

I'm in the process of trying to better organize all of this. It's a daunting task!

It gets added to on an almost daily basis.

Those are some really good ideas. I never thought of having a hyperlinked TOC page. And making categorized folders would help a lot.

Originally Posted by ragulduy

I'd suggest having a bookmarks folder in your browser with categories of subject and links to posts that contain 'particularly' useful code.

That's another good idea.

I keep the links in the files themselves.

In general, there's just too much to remember. The purpose of the sample files is so that we don't have to start from scratch when doing some task. Just open the sample file and the "template" is there and just needs to be tweaked for the immediate application. It's a real time saver. I'm sure most "power users" do this to some extent.

Text files are so easy to search, I keep two text files open in EditPad all the time:

Excel Tidbits
Groups of formulas for specific types of tasks and common tricks, organized by similarity of function and commented for searches:

Please Login or Register  to view this content.

Notice some of the comments include links to the thread where they were developed or used?

Excel Macros and Functions
This is a list of the most useful custom macros and UDFs I've written, again the code is listed in full with comments along with links to threads where it's been used successfully.


With these two text files, I can quick search on almost any topic and find what I'm looking for in seconds. Big time saver.

That's a great idea to include links. BTW, all but the first of those tricks are new to me, and pretty cool.

I'd be happy to send you the text files, they're HUGE...

Originally Posted by Tony Valko

Not sure of the exact number of files but it's got to be around 700-800.

Wow!

I just counted my sample files.

1133

Those are just the Excel files.

I also have notes stored in 261 text files.

I'd love to see the text files. I learned new stuff just from that snippet. Is there any advtange to using a text file over a module? I like the color-coding comments and easier formatting of a module.

The text files are just notes about a variety of things.

An example of one.

Sometimes we need to explain how Excel evaluates dates. So I have this info stored in a text file named: Date Serial Number Explanation.

In Excel dates are really just numbers formatted to look like dates. These numbers are commonly referred to as the date serial number. The date serial number is the count of days starting from a base date. In Windows Excel the default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Jan 1 2014 = date serial number 41640

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

Whenever I need to explain that to someone in a forum instead of retyping it all the time I just open the file, copy the text and paste it into a reply.

Originally Posted by k64

I'd love to see the text files. I learned new stuff just from that snippet. Is there any advantage to using a text file over a module? I like the color-coding comments and easier formatting of a module.

I think that was in response to my offer. Here's my main non-vba text file that those excerpts above came from, it's not very organized since I mostly "search" it to find what I need:

Thank you! Great info. I'll have to create my own eventually.

I've got modules by type - I have
An: autofilter mod,array mod,control mod,cs mod,date mod, dev mod...shape mod...udt mod. For 30 so far

Do you have any sample data associated with them or just the code? Do you keep them all in a workbook, in several, or as .bas files?

I name the programs after the OP's and I name the Workbooks after the OP - I don't save the trivial ones but I save every one that I learned from even if just a little

Here is a link to a file by benishiryo He has put a huge amount of work into this!!!

http://www.excelforum.com/tips-and-t...e-learned.html

That's a really cool file!
Here is a file that I made for my brother to help him learn excel. It's not so much neat tricks as a bunch of excel problems with a little bit of explanation. I just finished it, so it's a little rough. I'm afraid it won't have much to teach the experts around here but it's a nice sampling of excel problems of different types. Feel free to use it as you like.

This may be of use to many: http://www.rondebruin.nl/efb/efb.htm