|Excel and Office
Sharing Excel macros with other users
Q: I've been working on a complicated macro and it is save in my personal.xls workbook. I would like to take it home this weekend to work on it. If I copy my personal.xls workbook and load it on my PC at home, will it move the macro(s) with it? If not, how do I get the macro on another PC?
Which I guess brings up the question, if I want to share the macro with someone after it's done, how do I give it to them?
A: This an interesting issue, and you'll hear many, many opinions on how you really ought to do it, but little common sense.
The only question you need to ask is, "Do I want non-expert users to be able to use my code?"
If the answer is no, then do what I do: go into the VBE (Alt+F11), cut and paste the code into an email or a Word doc, mail it to yourself or other expert users and you/they can paste it into another personal workbook or some other file whenever you need to. Copying a whole personal workbook is dangerous, because it will overwrite the existing one, and you might have stuff in there you'd want to keep.
If you want non-experts to use the code, then convert it into an add-in. There's help on how to do this around the web, but there are some gotchas you'll have to understand about making the macros available to the user, and this will usually involve writing some extra code, maybe adding a custom menu or toolbar from code. If you're confident doing that, fine. If you'd like some code to get you started, feel free to download my free Excel add-in. The code isn't protected, so you can see how I've created the toolbar and how the code references are kept current no matter where the file runs from.