Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Collecting Excel modules

Posted on 2014-12-04
16
107 Views
Last Modified: 2014-12-05
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.
0
Comment
Question by:Frank Freese
  • 5
  • 4
  • 3
  • +3
16 Comments
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 84 total points
ID: 40481593
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
 
LVL 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 166 total points
ID: 40481630
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
 

Author Comment

by:Frank Freese
ID: 40481837
Wayne,
Does the Personal workbook follow you every time you open a new workbook?
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 166 total points
ID: 40481883
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 40482534
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40482632
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40482670
I'd use an add-in instead.
0
 
LVL 11

Assisted Solution

by:jkpieterse
jkpieterse earned 166 total points
ID: 40482748
@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
 

Author Comment

by:Frank Freese
ID: 40482750
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 84 total points
ID: 40482790
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
 
LVL 11

Assisted Solution

by:jkpieterse
jkpieterse earned 166 total points
ID: 40482828
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40482905
Indeed, not really had much input with Add-Ins.
0
 

Author Comment

by:Frank Freese
ID: 40482952
Rob - thanks. Got Book1 again.
I'll award points here and ask another question. Thanks to all
0
 

Author Closing Comment

by:Frank Freese
ID: 40482957
thanks to all!
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40483113
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
 

Author Comment

by:Frank Freese
ID: 40483153
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA modules import 4 57
Excel if statement 3 15
Frequency distribution of horizontal data? 5 29
Excel 201 rounding creating a problem with my macro 11 34
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question