Best way to store my VBA Code snippets

[JasonEnsor]

I have been learning VBA for about a year now, just teaching myself bit by bit, however i seem to be writting the same kind of code over and over....does anyone have any good suggestions as to the best way to store these snippets of code. it's normally things like opening files or removing columns ect....i guess i want some kind of storage system so that i can just refere to them easily.

Any ideas would be appreciated

 

[xlbo]

Use a Personal.xls workbook

It opens hidden whenever you open excel and modules within it are available in the VBE

Easiest way to get yourself one of these little beauties is to record a macro (anything will do - just enter a number into a cell or something) but after clicking on the record button, in th elittle form that pops up asking for a name etc, use the drop down box to change the store location of the macro to a personal.xls workbook

finish recording and you will now have a personal.xls

Get rid of th erecorded macro, put your useful bits n bobs in there - when you exit excel it will prompt if you want to save changes to personal.xls which you do

et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[melmitts]

I use the registered version but here is a link to the free version...

http://fishcodelib.com/CodeLibFree.htm

Later...
Mel

 

[electricpete]

I use a word file in outline view. Easily collapsible and expandable for organization. Easily word-searchable. Just another option.

I notice that vba code seems to slow down opening and closing... if it's not just my imagination. I would think that carrying everything around in personal.xls would slow things down.

 

[SkipVought]

Hi,

I have hundreds of functions and subs in my personal workbook. VERY handy!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

I recommend MZ-Tools. It provides a ton of functionality including code library and it's free.

Duane
Hook'D on Access
MS Access MVP

 

[TreefernT]

Hello All,

I also asked this question in another forum (MrExcel

http://www.mrexcel.com/forum/showthread.php?t=395104)

and received some useful advice.

I ended up creating separate Word documents for Excel, Word etc. and keep all my code there. This format enables me to paste the code and any pictures, screen dumps, comments, etc. that helps explain what the code does. For parts of the code that can be changes I can apply formatting and colour so they stand out, all of which I could not do in a dedicated code storage program.

Regards,
Treefern

 

[xlbo]

The benefit of storing in your Personal.xls file is that it is always available to you - either to run manually or to attach to buttons / toolbars etc. For example - one thing I have done is attach a paste special - values snippet to ctrl+z (as I'm not fussed about using hotkeys for undoing stuff) so I can very quickly do a copy/paste special values (which I do a LOT)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[fumei]

Yes, I am not so sure of the efficiency of storing Excel procedure code in a Word document.

I like MZ Tools, use it, and highly recommend it.


unknown

 

[WaterSprite]

I copy everything to NotePad, save it as a text file in a folder named, VBCodeSnippets. Works well, also serves as sort of a backup for me, even if I lost the spreadsheet, just build a new one, recopy the code from the text and I am back in business.

 

[fumei]

Oh as a backup, having a text file of your code is a GOOD idea!


unknown

 

[firefytr]

In addition to the fine suggestions (all of which I've used at one point or another), another might be OneNote. Actually works very well as a repository. If you have it of course.

I know XL-Dennis created a VBA library, I believe it was an add-in. I couldn't find a link for it, not sure if it's still stored online or not. But it was very user friendly. MZ-Tools, in addition to storing information, has some really handy-dandy tools as well, and is probably the best bang for your buck.

One thing I found to be a pain with Personal.xls (or .xlsb) is multiple versions. I have a work computer and a laptop, and a separate Personal.xlsb in both of them. So storing procedures there doesn't make sense to me, as they're not available wherever I want them. I store them in text files and keep them on my website. I also use Firefox with Xmarks add-on utility, which lets me set bookmarks to all of my computers and keep them all the same, and then I just bookmark my code, now it's always wherever I go (internet connection-dependent of course, which some people may not have...).

And yes - BACKUP!

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[SkipVought]

Actually I have several different Modules that I have stored in my PERSONAL workbook: One for database functions, one for Attachmate screen scraper functions, one for Buyer functions, Outlook/mail procedures etc. These modules, in addition to being stored in my PERSONAL, are also stored on a network, for others to import.

Of course a .bas file is just a text file.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NashProjects]

hi

I started with a blank excel workbook and created a separate module for each section of code type for example

I created a module called B_SQLConn and i store all the code ive ever used to connect to sql, access or oracle databases.. etc etc

Stuff like A_Codepool is where all my general code is kept like if i wanted to remove gridlines from an entire workbook and stuff like that.

I also have modules called D_DataMine where all my code belonging to datamining and web scraping is kept...

I agree with all the other guys here that sometimes 3rd party programs are brilliant with your code storage.. however a simple excel file works for me and has done so for over 11 years...

I tend to keep it in my usb and carry it with me...

p.s a word of warning about the personal.xls book.. if you are using this on a networked machine and your profile changes you will lose this file so from personal experience I would not go down this route.

The text file option from WaterSprite is an excellent option!

Regards

Kind regards

Lee Nash

http://www.Nashprojects.com

 

[VBAjedi]

To chime in:

I use a hybrid approach that I think is the best of both worlds. I keep my master snippets list in an outlined/indexed Word doc (which I'm actually moving to a Google doc so it's available from anywhere). In addition to allowing you to insert screenshots, etc, it's often nice to be able to keep incomplete code fragments without the VBA editor evaluating them for syntax. As long as I remember to apply a heading style to the title of a new snippet I'm adding, it's automatically added to the index, which is also nice.

Then, for complete functions and subs, I copy them to my Personal.xls file for quick access while I'm coding. You just have to remember to transfer any changes you make back to your master text document. Treating the text document as your master snippets library eases the pain of losing your personal.xls file or being on a different machine with a different version of the file.

VBAjedi

 

[Gavona]

Treating the text document as your master snippets library eases the pain of losing your personal.xls file or being on a different machine with a different version of the file

I too kept losing my Personal.xls, or having different versions on different machines. That is why my Personal.xls contains only the code to open myMacros.xls. The latter file is stored on a network drive with offline files making it available when I am not on the network and a copy on my home network. (I also give others access to myMacros.)

Since I did this I have far fewer issues with version control.

Gavin