+ Reply to Thread
Results 1 to 20 of 20

Organizing and Storing Code/Formulas

  1. #1
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    668

    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?
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Organizing and Storing Code/Formulas

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    668

    Re: Organizing and Storing Code/Formulas

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

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Organizing and Storing Code/Formulas

    I don't keep a library of code I've written, I have a bunch of workbooks I use regularly that are full of code and contain quite a lot of examples that I can bring up, otherwise if I can't remember something I just google it. I generally write code from scratch to answer posts here though as there are normally a bunch of changes I need to make to my example so that it is use-able by the OP.

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

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Organizing and Storing Code/Formulas

    Quote Originally Posted by ragulduy View Post
    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.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Organizing and Storing Code/Formulas

    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.
    Last edited by JBeaucaire; 12-26-2019 at 05:30 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  7. #7
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    668

    Re: Organizing and Storing Code/Formulas

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

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Organizing and Storing Code/Formulas

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


  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Organizing and Storing Code/Formulas

    Quote Originally Posted by Tony Valko View Post
    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.
    Last edited by Tony Valko; 06-18-2014 at 08:35 AM. Reason: I don't know how to spell "I" !

  10. #10
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    668

    Re: Organizing and Storing Code/Formulas

    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.

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Organizing and Storing Code/Formulas

    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.

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Organizing and Storing Code/Formulas

    Quote Originally Posted by k64 View Post
    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:
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    10-21-2012
    Location
    miami, florida usa
    MS-Off Ver
    Excel 2007
    Posts
    260

    Re: Organizing and Storing Code/Formulas

    Hi Mr JBeaucaire,

    your text It's seem very good and use full with bunch of formulas,
    for sure i'm going to learn (might not all .) but i'll try..


    Thank you for posting such great help file !!



    Best Regards

    David

  14. #14
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    668

    Re: Organizing and Storing Code/Formulas

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

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Organizing and Storing Code/Formulas

    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
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  16. #16
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    668

    Re: Organizing and Storing Code/Formulas

    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?

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Organizing and Storing Code/Formulas

    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

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,185

    Re: Organizing and Storing Code/Formulas

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  19. #19
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    668

    Re: Organizing and Storing Code/Formulas

    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.
    Attached Files Attached Files

  20. #20
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Organizing and Storing Code/Formulas

    This may be of use to many: http://www.rondebruin.nl/efb/efb.htm
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help with organizing data for charts (using names and formulas)
    By bazofio in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 04-19-2014, 02:18 PM
  2. organizing data with formulas
    By TheVolkinator in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2013, 12:22 PM
  3. [SOLVED] Formulas needed to replace macro that was organizing data
    By sephus730 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-12-2013, 01:15 PM
  4. Re-organizing a table with formulas
    By kancun in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2012, 03:00 PM
  5. [SOLVED] Storing Formulas to Use
    By NEWB in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-02-2005, 01:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1