Using a Function macro form another workbook

Question

I have project with multiple workbooks. To simplify macro management, I created a workbook with a single empty worksheet where I store all my macros. When this workbook is open, I can execute any of the Sub macros while working in another active workbook. However, I cannot call a Function macro unless that macro is actually in the active workbook. This applies whether I am calling the Function macro from a cell in the worksheet or from another macro. All the macros are declared as, or default to, Public. Module names are never the same as macro names.

Why are Function macros treated differently than Sub macros? (They also do not show up in the Alt-F8 drop-down list.) Is it really a requirement that a Function macro physically reside in the workbook where it used? If not, how do I make them available to any other open workbook?

Thank you

Answer 1

Hi,

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-

1. 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.
2. 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.

Best Regards.