Collecting Excel modules

Folks,
I have many modules that I have created. Most of them are workbook or worksheet specific. However, I am now seeing that I also have another group that I would "collect" as a special group (library) such that every time I open a new workbook those modules  will be available, even if I do not need all for a workbook.
I promise you I've read some where that this can be done or an Expert commented on this as an option. Possible? If so, how is this accomplished.
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Open a blank workbook, add a module and copy the macros to that module. Then store the workbook with your templates and use the template when you open a new workbook.
0
Wayne Taylor (webtubbs)Commented:
You would be referring to the PERSONAL workbook. This is a blank workbook created in 'C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART' and is opened whenever Excel is started.

The easiest way to create this workbook is to record a macro, but under "Store macro in", select Personal Macro Workbook. Then you can go to the VB Editor and copy all of your macros over to this file.

Record New Macro
0
Frank FreeseAuthor Commented:
Wayne,
Does the Personal workbook follow you every time you open a new workbook?
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Wayne Taylor (webtubbs)Commented:
The PERSONAL.XLSB will open (in the background) whenever you start Excel on that computer - whether it be via opening a file, or starting Excel itself.

If it's required on other computers, the PERSONAL.XLSB file can be copied to the corresponding folder.
0
jkpieterseCommented:
I think it is a bad idea to copy ones presonal.xlsb to another computer UNLESS this is your own computer. You would be overwriting the other user's recorded macro's.
0
Rob HensonFinance AnalystCommented:
Would the following work?

Save the Personal.xlsb file in a network location and then create a shortcut to the Personal xlsb in the User specific folder of the particular computer(s).

The shortcut will then trigger the opening of the network file which can be kept up to date.

Thanks
Rob H
0
Rory ArchibaldCommented:
I'd use an add-in instead.
0
jkpieterseCommented:
@Rob: That will not work if the user has his own personal.xlsb, Excel cannot open two files with the same name, regardless of their location.
I'm with Rory: an add-in is most flexible
0
Frank FreeseAuthor Commented:
Folks,
I've messed up somewhere. After following Wayne's suggestion in  ID: 40481630 opening a new workbook now longer gives me Book1 but instead Personal. I don't know how to restore back to Book1. Help here would be appreciated before going forward.
0
Rob HensonFinance AnalystCommented:
jkpietersen - I am thinking if there are multiple users each would have their own User folders with their own Personal books. If they have separate logins on the one computer you wouldn't end up with Excel trying to open multiple copies of a file with same name. Each user would be in their own session.

fh_freese - the Personal Workbook should be set to hidden on the View Ribbon tab. Hide workbook and then close Excel, you will get a message warning about saving changes to Personal workbook, say Yes to saving and then re-open Excel. You should then get a blank workbook open as normal.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jkpieterseCommented:
Yes but if you would want to share some code with multiple users, having copies of that code in various personal.xlsb files becomes a maintenance nightmare. Better to have one add-in with the code in a central location set to read-only so you can simply replace it when needed.
0
Rob HensonFinance AnalystCommented:
Indeed, not really had much input with Add-Ins.
0
Frank FreeseAuthor Commented:
Rob - thanks. Got Book1 again.
I'll award points here and ask another question. Thanks to all
0
Frank FreeseAuthor Commented:
thanks to all!
0
Rob HensonFinance AnalystCommented:
fh_freese - Thanks for the points. I know you have spread the points evenly across all people who contributed but strictly speaking the kudos for the Accepted solution ought to go to Wayne Taylor as his solution pointed you down the route of the Personal workbook.

Thanks
Rob
0
Frank FreeseAuthor Commented:
Indeed, however, you did resolve the new problem I introduced. I take the position that if you add value you deserve some points.
Thanks Rob. I really appreciate EE
Frank
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.