Problem with user created Functions

[John Barrington]

How can I create a function and locate it in one central place for every
open workbook to access?

The function appears to work fine if there's a copy of it in EVERY
workbook's module, but will not work if there's only ONE accessible copy in
all the open workbooks that I'm using, such as the workbook PERSONAL.XLS.

I read an earlier replied posting that stated a function should be placed in
and saved as a XLA file, and then installed as an Add-in before being able
to access the function within all open workbooks. I tried this and it didn't
appear to work unless I didn't do something correctly.

I created a XLA file called MyFunctions.XLA , saved it, and then started
transferring a few of my newly created functions into designated module. I
then resaved the XLA file and tried to use my functions, but the other open
workbooks couldn't seem to see them for some reason. All I saw within the
cells was an error #NAME?

[Niek Otten]

You may have forgotten to mention it, but one step is missing in your
recipe:

Tools>Add-ins, browse to the right directory and doubleclick MyFunctions

Regards,

Niek Otten

"John Barrington" <jbarrington_REMOVE_@_REMOVE_home.com> wrote in message
news:nKMj7.331902$qc.39...@news1.rdc1.va.home.com...

[Norman Harker]

Hi John & Niek!

If you put your Addins in:

C:\Program Files\Microsoft Office\Office\Library

You don't need to browse to find the Addin.

It also makes life easier on removal as all Addins you install are in one
place. I believe that there are removal problems if the Addin is not located
in the Library file where pre-2000 versions are concerned.

hth
"Niek Otten" <nico...@xs4all.nl> wrote in message
news:exv9vpiMBHA.1368@tkmsftngp02...

[John Barrington]

Under Add-ins, I previously had MyFunctions.XLA showing within the
selections, so I'd already checked it off earlier for my use.
As a precaution, I did go ahead and re-selected from the browse section as
you said just in case it did work. My worksheets still do not seem to be
seeing the functions within the XLA.

Here's an one example of one of the functions within MyFunctions.XLA.
Perhaps it's something within the code itself.

Option Explicit
Function Percent(HighNum As Double, LowNum As Double) As Double

Application.Volatile True
Dim PercentNum As Double

If HighNum >= LowNum Then
PercentNum = ((HighNum / LowNum) - 1) * 100
Else
PercentNum = ((LowNum / HighNum) - 1) * (-100)
End If

Percent = PercentNum

End Function

I also tried the term Public in the beginning term: Public Function
Percent(HighNum ...).

"Niek Otten" <nico...@xs4all.nl> wrote in message
news:exv9vpiMBHA.1368@tkmsftngp02...

[Norman Harker]

Hi John!

Just shooting in the dark here.

You copied the functions from an existing file. Did you remove them from
that file or comment them out? Comment them out or remove them so that Excel
is not confused as to which version to use.

Having done that, close and re-open Excel. You may well find that on
re-opening you will either have the problem disappear or get a more
informative error message.

Something somewhere, tells me that you do get just this problem when copying
functions into an Addin from an existing file. Excel is still looking for
the function in its original location.

But I am shooting in the dark and it's a case of wanting to get my hands on
the keyboard.

hth

"John Barrington" <jbarrington_REMOVE_@_REMOVE_home.com> wrote in message

news:cINj7.331944$qc.39...@news1.rdc1.va.home.com...

[John Barrington]

The problem's fixed now.

I had already done most what you had said, but your one comment about
"confused" got me to thinking. I didn't remove the original Percent()
functions from each of the cells from my first testing of the function from
within the original workbook. I began to think that you may have something
about it trying to reference the old Percent() function.

Once I remove all the old function references in the workbook and re-typed
the Percent() into the same cells again, they worked fine. Thanks for your
help with my problem.

I'd also like to take a moment to thank Nick for his help too.

"Norman Harker" <nha...@ozemail.com.au> wrote in message
news:qUNj7.1029$V83....@ozemail.com.au...

[Norman Harker]

Hi John!

Just to get it clear for later use.

If you cut and paste a function from personal.xls to another location such
as an Addin, Excel continues to look to the old location for all existing
uses of that function?

This looks like a bit of 'efficient' programming that prevents Excel from
having to go walkabout looking for a UDF after it has found it once because
once found, Excel links it to that location.

Interesting? I thought I'd hit that problem before when I moved a test
function to a permanent home.

Sees Ya!

"John Barrington" <jbarrington_REMOVE_@_REMOVE_home.com> wrote in message

news:iiPj7.332363$qc.39...@news1.rdc1.va.home.com...

[John Barrington]

> Just to get it clear for later use.
>
> If you cut and paste a function from personal.xls to another >location
such as an Addin, Excel continues to look to the old >location for all
existing uses of that function?

It does appear that Excel looks at only the old module location where the
function was originally created. Until you delete all function references in
the worksheet to the old module location, it seems that it gets bit stupid
when it can't find the code and refuses to look at any other add-ins to see
if it could be there. In the wider scope of things, I guess that could be a
good thing.
Take care.