Function macros cannot be directly accessed from other workbooks or worksheets unless they are physically stored within the workbook where they are being called.
If you want to make a Function macro available to other workbooks, you can-
- Add the Function Macro to a Personal Macro Workbook: You can create a Personal Macro Workbook that serves as a central location to store your commonly used macros. The Personal Macro Workbook is a hidden workbook that opens automatically whenever Excel starts. You can save your Function macros in this workbook, and they will be available for use in any workbook. To implement this, take the help of- Create and save all your macros in a single workbook.
- Use an Add-In: By creating an Add-In, you can distribute and share your Function macros with other users or workbooks. To create an Add-In, you can refer to the relevant documentation based on your Excel version.
Remember to install and enable the Personal Macro Workbook or Add-In on the computers where you want to use the Function macros. This will ensure that the macros are accessible to all open workbooks.