vba Personal workbook - I dont get it

[DeanP]

I am a bit confused about the personal workbook and what it's supposed to do? So I saved a few macros in the personal workbook, but if I want them to work in other workbooks I have to copy those macros into that workbook? What was the point of saving them in the personal workbook?

I have a workbook into which I have copied macros from my personal workbook. However, this other workbook will be used
every month, and saved with a new name (eg. Jan Sales - Feb Sales). My problem is that when the file is 'rolled forward'
any macro that is executed also open the Jan file. I was under the impression that having those macros in the personal
would prevent this?
Most of the resources that I have seen online describes how to create the personal workbook as opposed to how to use it.
Any clarification would be appreciated.

[Paul_Hossler]

Just a couple of points


You can use Personal subs and functions in another workbook's macros or the functions in in another workbook's worksheet, …

BUT the Personal subs and functions have to written general purpose enough

a. Proper use of ThisWorkbook and ActiveWorkbook
b. No hardcoding that will prevent use by another workbook
c. Probably a lot more

I keep my Personal.xlsm in my XLSTART folder (I prefer xlsm instead of xlsb)


1. You can directly call a Personal macro sub from Alt-F8 if it doesn't require parameters

Capture.JPG


2. You can make it a Reference so that you do not need the "Personal.xlm!". A sub like this will take paramerters

Capture2.JPG

Option Explicit

Sub DefinedAsReference()
    FilterOnSelection
End Sub


Sub NotDefinedAsReference()
    Application.Run "personal.xlsm!FilterOnSelection"
End Sub

3. You can call a Personal function on the worksheet

Capture3.JPG


Edit -- 4. A lot of people will keep their 'toolkit' macros in Personal.xlsm so that they can call / use them on any open visible workbook


I'm sure others will have additional ideas

[DeanP]

This is really helpful. Thank you for the excellent explanation!