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

[Bob Phillips]

Use

=Personal.xls!Soundex(A1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DrShowMe" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> DrShowMe
> ------------------------------------------------------------------------
> DrShowMe's Profile:
http://www.excelforum.com/member.php...o&userid=28854
> View this thread: http://www.excelforum.com/showthread...hreadid=486033
>

[Gord Dibben]

If you save Personal.xls as an Add-in(*.xla) you will not be forced to add the
workbook reference to your formula.


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 20:54:20 -0000, "Bob Phillips"
<[email protected]> wrote:

>Use
>
>=Personal.xls!Soundex(A1)
>
>--
>
>HTH
>
>RP
>(remove nothere from the email address if mailing direct)
>
>
>"DrShowMe" <[email protected]> wrote in
>message news:[email protected]...
>>
>> 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
>>
>>
>> --
>> DrShowMe

[DrShowMe]

Gord,

Many thanks. I knew about adding in the sheet reference (as suggested by others) and should have said so in my original post. Your suggestion is exactly what I needed. I have created a number of these UDFs which should be useful to other genealogists in an organization of which I am a volunteer and was looking for a way to make them easily available to others as well as to myself. Your advice is much appreciated.

If you save Personal.xls as an Add-in(*.xla) you will not be forced to add the
workbook reference to your formula.


Gord Dibben Excel MVP

On Thu, 17 Nov 2005 20:54:20 -0000, "Bob Phillips"
<[email protected]> wrote:

>Use
>
>=Personal.xls!Soundex(A1)
>
>--
>
>HTH
>
>RP
>(remove nothere from the email address if mailing direct)
>
>
>"DrShowMe" <[email protected]> wrote in
>message news:[email protected]...
>>
>> 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
>>
>>
>> --
>> DrShowMe