Keeping ALL macros in Personal.xlsb ?

I've used VBA for years but mainly in a sort of Copy/Paste/Adapt mode. As a novice one of my main difficulties I still have is deciding where to place a code module so that it's accessible when I decide to run it.

I'm considering simplifying matters by placing ALL of them in PERSONAL.XLSB? Is that OK?

Unless it's workbook specific, there is no reason you can't do that.

Thanks Rory, appreciate the fast reply. That will make things a lot easier.

Even if I have some macros that will only ever be required in specific workbooks, presumably they don't have to be stored there?

No, they don't have to be, it just makes more sense.

I would suggest building a workbook with all your code like PERSONAL.XLSB but make it xlam and make it a addin that way you dont have 2 instances of excel open all the time, anyway it works better for me
it gets loaded every time you open excel
unless you have worksheets in your PERSONAL.XLSB then you should keep it the way it is an addin its sheets are not visible in excel

@DIHayman

collecting code shouldn't be to hard to make, but looking for duplicates will be hard i think

Thanks for the follow-up Rory, but I've just thought of a potential problem. Presumably all simple cell references like B12, which were OK for a macro in the same workbook it was intended to be run, would have to be expanded to explicitly show the workbook and worksheet?

If so, is there an intuitive edit that will always work, such as changing it to something like WorkbookName.Worksheet.B12?

Maybe I'll post separately about this more general question.

@Joske920

That sounds an interesting alternative. Do you mean I just create an empty workbook, called say Mymacros.xlam, paste all my code into its VBA window, and place that file in \XLSSTART?

In a normal module, any unqualified range reference - e.g. just Range("A1") - will refer to A1 on the active sheet of the active workbook. That is the same no matter which workbook the code is in, so I'm not sure I really see it as an issue. I cannot see any benefit to moving code that should apply to a specific sheet in a specific workbook into another workbook.

@DIHayman: I hope there is indeed some existing macro or tool. Otherwise I'm considering using my Macro Express Pro to do the task. (Unfortunately it uses proprietary script that cannot be shared with non-users.)

@Joske920: I would expect that would be reasonably easy to do visually after a sort.

When you save a xlam addin it will save to a specific location in %APPDATA% folders not in \XLSSTART

I made a normal excel workbook with .xlsm ands put all the code in there, i didnt use the sheets in the workbook only modules because a xlam file is not visible in excel only in the VBA editor
I saved that .xlsm file to xlam so i always have a workbook to edit if necessary

just try something and you will see what the benefits are compared to a normal workbook

Worth noting that macros in an add-in do not appear in the macros list, so you need to build a menu system for them.

If you trust the xlam file in FILE -> OPTIONS -> ADD-INS -> Manage-GO -> select your addin

all code will be in the VBA editor

You could export all your modules from your sheets - maybe using something like this from Ron de Bruin

https://www.rondebruin.nl/win/s9/win002.htm

Then you can try this tool for managing a vba library.
Import them all into this and see if it helps manage where there are duplicates and delete them as needed.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=1054

Originally Posted by Joske920

If you trust the xlam file in FILE -> OPTIONS -> ADD-INS -> Manage-GO -> select your addin

all code will be in the VBA editor

Most people want macros available through the UI for ease of use I would think.

@Joske920, @scottiex: Thanks both. I'll explore those suggestions at a later date but meanwhile I've changed my approach, at least for my current project.

One workbook, WalkIndex.xlsm, will always be open. So I'm going to keep all macros that operate on that and on associated workbooks in that main workbook. I'll use Personal.xlsb for macros that are used more widely.

Ive made myself a ribbon so i have easy access to all macros thats my UI