If I am reading this right, you are asking how to use these macros on an open spreadsheet or MS Application (e.g. Access)?
Place the PERSONAL.XLSB in a common place on your hard drive or even the network.
e.g. X:\MyExcelMacros
Open a new Excel workbook.
In the Code window, go to Tools Reference - navigate to X:\MyExcelMacro and choose the Personal.xlsb
Now, the current workbook will reference all public formulas in the MyExcelMacro workbook.
In a past life, this was very useful to create custom business formulas.
For example: A laboratory could electronically report Mercury in Drinking Water samples.
The samples might come back as some number of Parts per billion.
Or they might have BDL (below detectable limits). By taking the Certified Lab ID, the Detectable Limit for that lab testing drinking water samples could be looked up. Lets say it is 0.001 PPB
Now, for a Regulatory process the EPA wants an Average for the last eight samples and the average must be below some value.
All reports were generated using VBA in Excel from MS Access linked to SQL Server.
One regulator agent wanted to replace BDL with zero while another wanted to replace BDL with the worst case (the labs's detection limit).
After consulting the manager and legal staff, it was decided that 50% of the Labs detection should be used. So a custom AverageMercury formula was created and placed on the Excel locate on the network. All regulatory agent's computer generated spreadsheets were linked to that central excel formula workbook.
Then, as the regulations changed, there was one central location to make a single change.
So, there can be a huge advantage to using the References to manage custom business formulas and rules.
I was responsible for well over 1 Million Public Drinking Water Samples per year - per western state.
Whats in your water?
