Registering Function Macros

[DrShowMe]

I have a workbook that contains regular (Sub) macros and function macros. Some of the function macros are used by the regular macros. I moved ALL the macros into Personal.xls. After doing this, the regular macros are available to all new and existing Workbooks. However, the function macros are NOT. For example, I have a function macro that creates a code (Soundex) that is used by genealogists. If I insert "=soundex(A1)" into cell B1, B1 should contain the code for the contents of cell A1. This works fine as long as I copy the function into a VB module in the workbook in which I want to use it. However, I want to be able to call this function (and many others) from any workbook without copying all of them from Personal.xls into each workbook.

Can someone give me a clue about how to do this.

TIA

 

[Greg Truby]

Welcome to the board!

Well, even if you make the FUNCTION public, you are going to need to prefix it with the workbook name, in this case =PERSONAL.XLS!foofoofoo() The way to avoid that is to set a reference to your personal workbook's project via the TOOLS | REFERENCES... menu option in the VBE. The problem you would have by utilizing external UDF's in cell formulas is that they may fail should anyone but you open the workbook. So normally, you do want to copy the function's code over to the workbook anyway. What you can do is to drop you most commonly used udf's into a separate module in PERSONAL.XLS that way you can just copy that module over whenever you want to reference those functions in cell formulae.

HTH

 

[DrShowMe]

Greg,

Thanks for the reply and the welcome. I posted my query elsewhere also and got an answer that is perfect for my needs. I am posting this information here in the hope that it might assist others. The solution (which perfectly supports my needs) is to save workbook with the macros (in my case Personal.xls) as an add-in [File --> Save as ... --> (select Microsoft Excel Add-in under Format)]. I have a number of these UDFs that should be useful to genealogists and want to make them available (simply) to those in the organization in which I volunteer - many of whom are NOT particularly facile with Excel.

When the workbook is saved as an add-in it acquires the extension .xla. It can then be 'added in' by [Tools --> Add Ins ... --> pick Select, navigate to the saved file]. Then those functions are available to all workbooks.

Thanks again.

Welcome to the board!

Well, even if you make the FUNCTION public, you are going to need to prefix it with the workbook name, in this case =PERSONAL.XLS!foofoofoo() The way to avoid that is to set a reference to your personal workbook's project via the TOOLS | REFERENCES... menu option in the VBE. The problem you would have by utilizing external UDF's in cell formulas is that they may fail should anyone but you open the workbook. So normally, you do want to copy the function's code over to the workbook anyway. What you can do is to drop you most commonly used udf's into a separate module in PERSONAL.XLS that way you can just copy that module over whenever you want to reference those functions in cell formulae.

HTH

 

[Greg Truby]

Yes-sirree, you can always toss the functions over to an Add-In and do 'er that-a-way. If you look at the top of the boards and follow VP's Recommended Add-Ins and Links Sticky you'll see several baskets of specialized functions that are distributed as Add-Ins. Very common way of distributing custom functions. There's an upside and a downside to this. Upside is the user only has to install your add-in once and he'll always have your functions at his disposal. The downside is as my post above, the code is not included in your workbook itself. So any time you share it, you'd need to share the add-in also. One solution to this is to put code in your workbook's open method that checks the users AddIns collection for the needed add-in and if it does not find it, pop a message to the user or open a browser session pointed to a URL where you have conveniently stashed a downloadable copy of your add-in.