Custom function in an add-in problem!

[Alan Ibbotson]

The add-in defines a custom function that returns a catalog price. The
formula in the cell is "=CAT05("1234", Price). When the user that created
the file opens the file, all is well. When another user opens the file we
have problems. Even though all of our users have the same add-in, the file
worksheet will try to reference the creators add-in, i.e. in the non
creators display the cell say "='C:\Documents and Settings\User\Application
Data\Microsoft\AddIns\CustomTools.xla'!CAT05("1234", Price)", where "User"
is the file creator. So the cell returns the "#NAME?" error.

I have been using find and replace to delete the path of the originator and
just display "=CAT05("1234", Price)"[ That gets it to work correctly]. So my
question is what do I need to do, either when the file is created or when it
is reopened, so the path is not inserted into the formula?

 

[Harlan Grove]

Alan Ibbotson wrote...

The add-in defines a custom function that returns a catalog price. The
formula in the cell is "=CAT05("1234", Price). When the user that created
the file opens the file, all is well. When another user opens the file we
have problems. Even though all of our users have the same add-in, the file
worksheet will try to reference the creators add-in, i.e. in the non
creators display the cell say "='C:\Documents and Settings\User\Application
Data\Microsoft\AddIns\CustomTools.xla'!CAT05("1234", Price)", where "User"
is the file creator. So the cell returns the "#NAME?" error.

I have been using find and replace to delete the path of the originator and
just display "=CAT05("1234", Price)"[ That gets it to work correctly]. So my
question is what do I need to do, either when the file is created or when it
is reopened, so the path is not inserted into the formula?

If this add-in is meant to be used by multiple users, then put it in a
shared locations that would always have the same pathname, e.g., put it
into

C:\Documents and Settings\All Users\Documents

or some reasonable *common* subdirectory.

 

[Alan Ibbotson]

I have tried this in the past but when installing the add-in you are
prompted to copy the add-in to the local drive in the AddIns folder. Even if
I could figure this out that would not be a complete solution. I have
several users that work in other countries and don't have access to our
server.

Alan Ibbotson wrote...

The add-in defines a custom function that returns a catalog price. The
formula in the cell is "=CAT05("1234", Price). When the user that created
the file opens the file, all is well. When another user opens the file we
have problems. Even though all of our users have the same add-in, the file
worksheet will try to reference the creators add-in, i.e. in the non
creators display the cell say "='C:\Documents and
Settings\User\Application
Data\Microsoft\AddIns\CustomTools.xla'!CAT05("1234", Price)", where "User"
is the file creator. So the cell returns the "#NAME?" error.

I have been using find and replace to delete the path of the originator
and
just display "=CAT05("1234", Price)"[ That gets it to work correctly]. So
my
question is what do I need to do, either when the file is created or when
it
is reopened, so the path is not inserted into the formula?

If this add-in is meant to be used by multiple users, then put it in a
shared locations that would always have the same pathname, e.g., put it
into

C:\Documents and Settings\All Users\Documents

or some reasonable *common* subdirectory.

 

[Harlan Grove]

Alan Ibbotson wrote...

I have tried this in the past but when installing the add-in you are
prompted to copy the add-in to the local drive in the AddIns folder. Even if
I could figure this out that would not be a complete solution. I have
several users that work in other countries and don't have access to our
server.

The drive/directory path above is *LOCAL* to *EVERY* PC running Windows
2000 or later (unless your IT department has gotten creative with
directory naming). That is, *EVERY* PC running Windows 2000 or later
will have this directory as well as

C:\Documents and Settings\<UserNameHere>

on <UserNameHere>'s PC.

If this doesn't work for you, use a common directory in the root of the
C: drive, e.g., C:\OurWonderfulAdd-Ins. Create that directory as part
of the installation process and put the add-in XLA there.