Macro housing

[cmpgeek]

where should i place / save macros so that they can be used on any excel sheet that happens to be open? or is that even possible? i know when i open some files there are certain macros listed there that i never see anywhere else - although i could use them elsewhere...

do i have to copy them into each spreadsheet i want to use them on, or is there a specific place that i cansave them so they are always available?

thanks again for all your help - i could not do this without yall!

[NateO]

Hello again,

For a standard procedure, house the code in a normal module. To create one, hit alt-f11 to enter the VBE, and click Insert->Module.

You'll want to remove sheet references as well. In some cases you can do without the sheet reference all together, and so code like:

range("a1").value = 1

Will automatically work with a1 on the active sheet.

But with some code, like the autofiltermode property, sheet references are necessary. In this event, use:

Activesheet

Instead of a specific sheet reference (e.g., Sheets(1) ). Hope this helps.

[Ken Puls]

Hi there,

Are meaning you want them available in all workbooks?

Some alternatives:
-You could save them in the Personal.xls file. This file is created when you record your first macro and choose to save it in the Personal Macro workbook. It's available to you no matter what workbook is open, but is limited to your system.

-You could create an add-in. Like the personal macro workbook, you can store lots of code there, but it is only opened if you select to install it using the Tools|Add-ins command. The bonus here is that you can email it off to a friend (or home) and they can install it to get any benefits from it... of course, you'd want to build menus and stuff to make it more accessible, which is actually not as hard as it may sound at first! (I personally use John Walkenbach's menu maker (http://j-walk.com/ss/excel/tips/tip53.htm):thumb )

And then, of course, there's the VBAX Knowledgebase! With the added bonus that if you store your code there, you can get it from anywhere, AND people see how brilliant you are! (That was the reason you made the KB for us, wasn't it Dreamboat? :rofl :p)

Seriously, though... I'm sure that there's other alternatives as well.

Personally, I use a mixture. I have a very small amount of code in my Personal.xls file at work, but I also created an addin which holds a huge amount of my code. That file gets emailed home (automatically) when I update it, so that I'm always using the most current version at each location.

This is good for me, but I'm also really careful about making sure that if a macro is required for a specific file, that the macro is ALWAYS stored within that file. This eliminates the issues of a potential user not having your add-in or personal macro workbook.:yes

So the purpose of the code is important!

HTH,

[johnske]

Hi,

There is a further alternative that I tend to favour (as it's very quick and simple) - Export the module with the code you may want to use in another book (you can keep the exported module on the desktop, in a special folder you make for these, ,,, whatever).

Whenever you want to use this code in another book, simply click Import, select the module you want from wherever you've stored it, and it's then available for use in this other book...

HTH

EDIT: PS you can also open these exported files with notepad to read or edit/change them (userforms can also be exported and imported in the exact same way)

[austenr]

Good tip Johnske

[cmpgeek]

thanks for all your suggestions guys!

i can't offer you money - but how about my undying respect? ;)