sharing an add-in function for multiple users

I needed to access a MS Access database to extract a unit cost that matches
a part number that was in Excel. I created a function, saved it as an XLA,
and used it as an Add-in. I figured that I could copy the XLA file to the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file, the
function would work. As it turns out, it looks like when another user opens
the file, it is trying to access my profile for the function. So, I figured
that I would put the XLA file on a shared network folder. Browsed to the
addin (did not copy to local HD), and then resaved the spreadsheet. I
opened the file on a different machine (that has access to the network
folder), but it still wasn't working. Is there a way to get this to work?
Or do I have to save the function in the actual file?

Thanks,
Brian

Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your AddIn) in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" <[email protected]> wrote in message
news:[email protected]...
> I needed to access a MS Access database to extract a unit cost that
matches
> a part number that was in Excel. I created a function, saved it as an
XLA,
> and used it as an Add-in. I figured that I could copy the XLA file to the
> users profile (c:\documents and settings\user\application
> data\microsoft\addins), and then when another user opened the file, the
> function would work. As it turns out, it looks like when another user
opens
> the file, it is trying to access my profile for the function. So, I
figured
> that I would put the XLA file on a shared network folder. Browsed to the
> addin (did not copy to local HD), and then resaved the spreadsheet. I
> opened the file on a different machine (that has access to the network
> folder), but it still wasn't working. Is there a way to get this to work?
> Or do I have to save the function in the actual file?
>
> Thanks,
> Brian
>
>

Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" <[email protected]> wrote in message
news:[email protected]...
> Hi Brian,
>
> If you want to use a custom function in another workbook you need to
> reference the workbook containing that function (in this case your AddIn)
in
> the workbook that will be using the function. Shame ain't it!
>
> Post back if you don't know how to do this.
> HTH,
> Gareth
>
> "Brian K. Sheperd" <[email protected]> wrote in message
> news:[email protected]...
> > I needed to access a MS Access database to extract a unit cost that
> matches
> > a part number that was in Excel. I created a function, saved it as an
> XLA,
> > and used it as an Add-in. I figured that I could copy the XLA file to
the
> > users profile (c:\documents and settings\user\application
> > data\microsoft\addins), and then when another user opened the file, the
> > function would work. As it turns out, it looks like when another user
> opens
> > the file, it is trying to access my profile for the function. So, I
> figured
> > that I would put the XLA file on a shared network folder. Browsed to
the
> > addin (did not copy to local HD), and then resaved the spreadsheet. I
> > opened the file on a different machine (that has access to the network
> > folder), but it still wasn't working. Is there a way to get this to
work?
> > Or do I have to save the function in the actual file?
> >
> > Thanks,
> > Brian
> >
> >
>
>

Hi Brian,

Open your AddIn and the workbook that needs to reference it.
Open the VBE (SHIFT F11)
Select Tools \ References from the Menu Bar.
Your AddIn should be listed there (that's why we opened it before -
otherwise you might have to browse for it)
Tick the checkbox for your AddIn and click OK.
Save your workbook (for good measure).
You should now be able to access the function.

Note that if you didn't give your AddIn's VBA project a specific name it
will just be called VBAProject in the References list. You may like to
change it: Select Tools \ VBAProject Properties

Note further that, whilst Excel is smart at working out which project is
referenced even if you mess around with paths and things you can get into
bit of trouble should people have different mapped drives. Even referencing
by browsing through network neighourhood to make sure you get the UNC path
can be problematic if one person saves the referencing workbook on a mapped
drive - it somehow changes the references... but on the wholeit should be
fine.

HTH,
Gareth

----- Original Message -----
From: "Brian K. Sheperd" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Tuesday, March 15, 2005 6:00 PM
Subject: Re: sharing an add-in function for multiple users


Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" <[email protected]> wrote in message
news:[email protected]...
> Hi Brian,
>
> If you want to use a custom function in another workbook you need to
> reference the workbook containing that function (in this case your AddIn)
in
> the workbook that will be using the function. Shame ain't it!
>
> Post back if you don't know how to do this.
> HTH,
> Gareth
>
> "Brian K. Sheperd" <[email protected]> wrote in message
> news:[email protected]...
> > I needed to access a MS Access database to extract a unit cost that
> matches
> > a part number that was in Excel. I created a function, saved it as an
> XLA,
> > and used it as an Add-in. I figured that I could copy the XLA file to
the
> > users profile (c:\documents and settings\user\application
> > data\microsoft\addins), and then when another user opened the file, the
> > function would work. As it turns out, it looks like when another user
> opens
> > the file, it is trying to access my profile for the function. So, I
> figured
> > that I would put the XLA file on a shared network folder. Browsed to
the
> > addin (did not copy to local HD), and then resaved the spreadsheet. I
> > opened the file on a different machine (that has access to the network
> > folder), but it still wasn't working. Is there a way to get this to
work?
> > Or do I have to save the function in the actual file?
> >
> > Thanks,
> > Brian
> >
> >
>
>


"Brian K. Sheperd" <[email protected]> wrote in message
news:[email protected]...
> Gareth,
> Yes... Please explain how to accomplish this.
> Thanks,
> Brian
>
>
> "Gareth Roberts" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Brian,
> >
> > If you want to use a custom function in another workbook you need to
> > reference the workbook containing that function (in this case your
AddIn)
> in
> > the workbook that will be using the function. Shame ain't it!
> >
> > Post back if you don't know how to do this.
> > HTH,
> > Gareth
> >
> > "Brian K. Sheperd" <[email protected]> wrote in message
> > news:[email protected]...
> > > I needed to access a MS Access database to extract a unit cost that
> > matches
> > > a part number that was in Excel. I created a function, saved it as an
> > XLA,
> > > and used it as an Add-in. I figured that I could copy the XLA file to
> the
> > > users profile (c:\documents and settings\user\application
> > > data\microsoft\addins), and then when another user opened the file,
the
> > > function would work. As it turns out, it looks like when another user
> > opens
> > > the file, it is trying to access my profile for the function. So, I
> > figured
> > > that I would put the XLA file on a shared network folder. Browsed to
> the
> > > addin (did not copy to local HD), and then resaved the spreadsheet. I
> > > opened the file on a different machine (that has access to the network
> > > folder), but it still wasn't working. Is there a way to get this to
> work?
> > > Or do I have to save the function in the actual file?
> > >
> > > Thanks,
> > > Brian
> > >
> > >
> >
> >
>
>

Gareth,

Thank you very much for the info.

Just as a side thought. The file that I am working with is going to be a
template file. We have a thousand or so files that in an old revision. I
am going to write a subroutine to open each excel file, copy out specific
information, and copy it to the template. What if I just wrote a function
in VBA for the template file, then it would be there since all the other
files are going to be based from that template? Or would that be about the
same concept?

Thanks again,
Brian


"Gareth Roberts" <[email protected]> wrote in message
news:[email protected]...
> Hi Brian,
>
> Open your AddIn and the workbook that needs to reference it.
> Open the VBE (SHIFT F11)
> Select Tools \ References from the Menu Bar.
> Your AddIn should be listed there (that's why we opened it before -
> otherwise you might have to browse for it)
> Tick the checkbox for your AddIn and click OK.
> Save your workbook (for good measure).
> You should now be able to access the function.
>
> Note that if you didn't give your AddIn's VBA project a specific name it
> will just be called VBAProject in the References list. You may like to
> change it: Select Tools \ VBAProject Properties
>
> Note further that, whilst Excel is smart at working out which project is
> referenced even if you mess around with paths and things you can get into
> bit of trouble should people have different mapped drives. Even
referencing
> by browsing through network neighourhood to make sure you get the UNC path
> can be problematic if one person saves the referencing workbook on a
mapped
> drive - it somehow changes the references... but on the wholeit should be
> fine.
>
> HTH,
> Gareth
>
> ----- Original Message -----
> From: "Brian K. Sheperd" <[email protected]>
> Newsgroups: microsoft.public.excel.programming
> Sent: Tuesday, March 15, 2005 6:00 PM
> Subject: Re: sharing an add-in function for multiple users
>
>
> Gareth,
> Yes... Please explain how to accomplish this.
> Thanks,
> Brian
>
>
> "Gareth Roberts" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Brian,
> >
> > If you want to use a custom function in another workbook you need to
> > reference the workbook containing that function (in this case your
AddIn)
> in
> > the workbook that will be using the function. Shame ain't it!
> >
> > Post back if you don't know how to do this.
> > HTH,
> > Gareth
> >
> > "Brian K. Sheperd" <[email protected]> wrote in message
> > news:[email protected]...
> > > I needed to access a MS Access database to extract a unit cost that
> > matches
> > > a part number that was in Excel. I created a function, saved it as an
> > XLA,
> > > and used it as an Add-in. I figured that I could copy the XLA file to
> the
> > > users profile (c:\documents and settings\user\application
> > > data\microsoft\addins), and then when another user opened the file,
the
> > > function would work. As it turns out, it looks like when another user
> > opens
> > > the file, it is trying to access my profile for the function. So, I
> > figured
> > > that I would put the XLA file on a shared network folder. Browsed to
> the
> > > addin (did not copy to local HD), and then resaved the spreadsheet. I
> > > opened the file on a different machine (that has access to the network
> > > folder), but it still wasn't working. Is there a way to get this to
> work?
> > > Or do I have to save the function in the actual file?
> > >
> > > Thanks,
> > > Brian
> > >
> > >
> >
> >
>
>
> "Brian K. Sheperd" <[email protected]> wrote in message
> news:[email protected]...
> > Gareth,
> > Yes... Please explain how to accomplish this.
> > Thanks,
> > Brian
> >
> >
> > "Gareth Roberts" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Brian,
> > >
> > > If you want to use a custom function in another workbook you need to
> > > reference the workbook containing that function (in this case your
> AddIn)
> > in
> > > the workbook that will be using the function. Shame ain't it!
> > >
> > > Post back if you don't know how to do this.
> > > HTH,
> > > Gareth
> > >
> > > "Brian K. Sheperd" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I needed to access a MS Access database to extract a unit cost that
> > > matches
> > > > a part number that was in Excel. I created a function, saved it as
an
> > > XLA,
> > > > and used it as an Add-in. I figured that I could copy the XLA file
to
> > the
> > > > users profile (c:\documents and settings\user\application
> > > > data\microsoft\addins), and then when another user opened the file,
> the
> > > > function would work. As it turns out, it looks like when another
user
> > > opens
> > > > the file, it is trying to access my profile for the function. So, I
> > > figured
> > > > that I would put the XLA file on a shared network folder. Browsed
to
> > the
> > > > addin (did not copy to local HD), and then resaved the spreadsheet.
I
> > > > opened the file on a different machine (that has access to the
network
> > > > folder), but it still wasn't working. Is there a way to get this to
> > work?
> > > > Or do I have to save the function in the actual file?
> > > >
> > > > Thanks,
> > > > Brian
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Hi Brian,

I think that's a much better idea. Keeping the code in the workbook itself
would avoid all the referencing problems, letting you email them etc. Of
course.... if you ever wanted to change or add functions to them in the
future that would be awkward. In that case you would have been better
referencing.... I think it depends on your circumstances, how your workbooks
are deployed, likelihood of changes etc.

It is possible to programmatically change the VBA code in workbooks so if
you has to change it for all 1000 files you could do it automatically....
but YIKES!

Cya
Gareth

"Brian K. Sheperd" <[email protected]> wrote in message
news:[email protected]...
> Gareth,
>
> Thank you very much for the info.
>
> Just as a side thought. The file that I am working with is going to be a
> template file. We have a thousand or so files that in an old revision. I
> am going to write a subroutine to open each excel file, copy out specific
> information, and copy it to the template. What if I just wrote a function
> in VBA for the template file, then it would be there since all the other
> files are going to be based from that template? Or would that be about
the
> same concept?
>
> Thanks again,
> Brian
>
>
> "Gareth Roberts" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Brian,
> >
> > Open your AddIn and the workbook that needs to reference it.
> > Open the VBE (SHIFT F11)
> > Select Tools \ References from the Menu Bar.
> > Your AddIn should be listed there (that's why we opened it before -
> > otherwise you might have to browse for it)
> > Tick the checkbox for your AddIn and click OK.
> > Save your workbook (for good measure).
> > You should now be able to access the function.
> >
> > Note that if you didn't give your AddIn's VBA project a specific name it
> > will just be called VBAProject in the References list. You may like to
> > change it: Select Tools \ VBAProject Properties
> >
> > Note further that, whilst Excel is smart at working out which project is
> > referenced even if you mess around with paths and things you can get
into
> > bit of trouble should people have different mapped drives. Even
> referencing
> > by browsing through network neighourhood to make sure you get the UNC
path
> > can be problematic if one person saves the referencing workbook on a
> mapped
> > drive - it somehow changes the references... but on the wholeit should
be
> > fine.
> >
> > HTH,
> > Gareth
> >
> > ----- Original Message -----
> > From: "Brian K. Sheperd" <[email protected]>
> > Newsgroups: microsoft.public.excel.programming
> > Sent: Tuesday, March 15, 2005 6:00 PM
> > Subject: Re: sharing an add-in function for multiple users
> >
> >
> > Gareth,
> > Yes... Please explain how to accomplish this.
> > Thanks,
> > Brian
> >
> >
> > "Gareth Roberts" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Brian,
> > >
> > > If you want to use a custom function in another workbook you need to
> > > reference the workbook containing that function (in this case your
> AddIn)
> > in
> > > the workbook that will be using the function. Shame ain't it!
> > >
> > > Post back if you don't know how to do this.
> > > HTH,
> > > Gareth
> > >
> > > "Brian K. Sheperd" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I needed to access a MS Access database to extract a unit cost that
> > > matches
> > > > a part number that was in Excel. I created a function, saved it as
an
> > > XLA,
> > > > and used it as an Add-in. I figured that I could copy the XLA file
to
> > the
> > > > users profile (c:\documents and settings\user\application
> > > > data\microsoft\addins), and then when another user opened the file,
> the
> > > > function would work. As it turns out, it looks like when another
user
> > > opens
> > > > the file, it is trying to access my profile for the function. So, I
> > > figured
> > > > that I would put the XLA file on a shared network folder. Browsed
to
> > the
> > > > addin (did not copy to local HD), and then resaved the spreadsheet.
I
> > > > opened the file on a different machine (that has access to the
network
> > > > folder), but it still wasn't working. Is there a way to get this to
> > work?
> > > > Or do I have to save the function in the actual file?
> > > >
> > > > Thanks,
> > > > Brian
> > > >
> > > >
> > >
> > >
> >
> >
> > "Brian K. Sheperd" <[email protected]> wrote in message
> > news:[email protected]...
> > > Gareth,
> > > Yes... Please explain how to accomplish this.
> > > Thanks,
> > > Brian
> > >
> > >
> > > "Gareth Roberts" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Brian,
> > > >
> > > > If you want to use a custom function in another workbook you need to
> > > > reference the workbook containing that function (in this case your
> > AddIn)
> > > in
> > > > the workbook that will be using the function. Shame ain't it!
> > > >
> > > > Post back if you don't know how to do this.
> > > > HTH,
> > > > Gareth
> > > >
> > > > "Brian K. Sheperd" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > I needed to access a MS Access database to extract a unit cost
that
> > > > matches
> > > > > a part number that was in Excel. I created a function, saved it
as
> an
> > > > XLA,
> > > > > and used it as an Add-in. I figured that I could copy the XLA
file
> to
> > > the
> > > > > users profile (c:\documents and settings\user\application
> > > > > data\microsoft\addins), and then when another user opened the
file,
> > the
> > > > > function would work. As it turns out, it looks like when another
> user
> > > > opens
> > > > > the file, it is trying to access my profile for the function. So,
I
> > > > figured
> > > > > that I would put the XLA file on a shared network folder. Browsed
> to
> > > the
> > > > > addin (did not copy to local HD), and then resaved the
spreadsheet.
> I
> > > > > opened the file on a different machine (that has access to the
> network
> > > > > folder), but it still wasn't working. Is there a way to get this
to
> > > work?
> > > > > Or do I have to save the function in the actual file?
> > > > >
> > > > > Thanks,
> > > > > Brian
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Gareth,
I will have to run it by a few people to see which path will be better.
Thank you again for all of your help.
Brian



"Gareth Roberts" <[email protected]> wrote in message
news:[email protected]...
> Hi Brian,
>
> I think that's a much better idea. Keeping the code in the workbook itself
> would avoid all the referencing problems, letting you email them etc. Of
> course.... if you ever wanted to change or add functions to them in the
> future that would be awkward. In that case you would have been better
> referencing.... I think it depends on your circumstances, how your
workbooks
> are deployed, likelihood of changes etc.
>
> It is possible to programmatically change the VBA code in workbooks so if
> you has to change it for all 1000 files you could do it automatically....
> but YIKES!
>
> Cya
> Gareth
>
> "Brian K. Sheperd" <[email protected]> wrote in message
> news:[email protected]...
> > Gareth,
> >
> > Thank you very much for the info.
> >
> > Just as a side thought. The file that I am working with is going to be
a
> > template file. We have a thousand or so files that in an old revision.
I
> > am going to write a subroutine to open each excel file, copy out
specific
> > information, and copy it to the template. What if I just wrote a
function
> > in VBA for the template file, then it would be there since all the other
> > files are going to be based from that template? Or would that be about
> the
> > same concept?
> >
> > Thanks again,
> > Brian
> >
> >
> > "Gareth Roberts" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Brian,
> > >
> > > Open your AddIn and the workbook that needs to reference it.
> > > Open the VBE (SHIFT F11)
> > > Select Tools \ References from the Menu Bar.
> > > Your AddIn should be listed there (that's why we opened it before -
> > > otherwise you might have to browse for it)
> > > Tick the checkbox for your AddIn and click OK.
> > > Save your workbook (for good measure).
> > > You should now be able to access the function.
> > >
> > > Note that if you didn't give your AddIn's VBA project a specific name
it
> > > will just be called VBAProject in the References list. You may like to
> > > change it: Select Tools \ VBAProject Properties
> > >
> > > Note further that, whilst Excel is smart at working out which project
is
> > > referenced even if you mess around with paths and things you can get
> into
> > > bit of trouble should people have different mapped drives. Even
> > referencing
> > > by browsing through network neighourhood to make sure you get the UNC
> path
> > > can be problematic if one person saves the referencing workbook on a
> > mapped
> > > drive - it somehow changes the references... but on the wholeit should
> be
> > > fine.
> > >
> > > HTH,
> > > Gareth
> > >
> > > ----- Original Message -----
> > > From: "Brian K. Sheperd" <[email protected]>
> > > Newsgroups: microsoft.public.excel.programming
> > > Sent: Tuesday, March 15, 2005 6:00 PM
> > > Subject: Re: sharing an add-in function for multiple users
> > >
> > >
> > > Gareth,
> > > Yes... Please explain how to accomplish this.
> > > Thanks,
> > > Brian
> > >
> > >
> > > "Gareth Roberts" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Brian,
> > > >
> > > > If you want to use a custom function in another workbook you need to
> > > > reference the workbook containing that function (in this case your
> > AddIn)
> > > in
> > > > the workbook that will be using the function. Shame ain't it!
> > > >
> > > > Post back if you don't know how to do this.
> > > > HTH,
> > > > Gareth
> > > >
> > > > "Brian K. Sheperd" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > I needed to access a MS Access database to extract a unit cost
that
> > > > matches
> > > > > a part number that was in Excel. I created a function, saved it
as
> an
> > > > XLA,
> > > > > and used it as an Add-in. I figured that I could copy the XLA
file
> to
> > > the
> > > > > users profile (c:\documents and settings\user\application
> > > > > data\microsoft\addins), and then when another user opened the
file,
> > the
> > > > > function would work. As it turns out, it looks like when another
> user
> > > > opens
> > > > > the file, it is trying to access my profile for the function. So,
I
> > > > figured
> > > > > that I would put the XLA file on a shared network folder. Browsed
> to
> > > the
> > > > > addin (did not copy to local HD), and then resaved the
spreadsheet.
> I
> > > > > opened the file on a different machine (that has access to the
> network
> > > > > folder), but it still wasn't working. Is there a way to get this
to
> > > work?
> > > > > Or do I have to save the function in the actual file?
> > > > >
> > > > > Thanks,
> > > > > Brian
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> > > "Brian K. Sheperd" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Gareth,
> > > > Yes... Please explain how to accomplish this.
> > > > Thanks,
> > > > Brian
> > > >
> > > >
> > > > "Gareth Roberts" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Brian,
> > > > >
> > > > > If you want to use a custom function in another workbook you need
to
> > > > > reference the workbook containing that function (in this case your
> > > AddIn)
> > > > in
> > > > > the workbook that will be using the function. Shame ain't it!
> > > > >
> > > > > Post back if you don't know how to do this.
> > > > > HTH,
> > > > > Gareth
> > > > >
> > > > > "Brian K. Sheperd" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > I needed to access a MS Access database to extract a unit cost
> that
> > > > > matches
> > > > > > a part number that was in Excel. I created a function, saved it
> as
> > an
> > > > > XLA,
> > > > > > and used it as an Add-in. I figured that I could copy the XLA
> file
> > to
> > > > the
> > > > > > users profile (c:\documents and settings\user\application
> > > > > > data\microsoft\addins), and then when another user opened the
> file,
> > > the
> > > > > > function would work. As it turns out, it looks like when
another
> > user
> > > > > opens
> > > > > > the file, it is trying to access my profile for the function.
So,
> I
> > > > > figured
> > > > > > that I would put the XLA file on a shared network folder.
Browsed
> > to
> > > > the
> > > > > > addin (did not copy to local HD), and then resaved the
> spreadsheet.
> > I
> > > > > > opened the file on a different machine (that has access to the
> > network
> > > > > > folder), but it still wasn't working. Is there a way to get
this
> to
> > > > work?
> > > > > > Or do I have to save the function in the actual file?
> > > > > >
> > > > > > Thanks,
> > > > > > Brian
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Hi,
I share and dropbox file which counts colors in an Excel sheet and call an external Add-in with the following code:
=CountCcolor('MÅN 29 maj'!$C$7:$CN$74;$J$7)*0,25

when another person opens the same file it looks like this:
=C:\Users\Robert\...\RaknaFarg.xlam!CountCcolor('MÅN 29 maj'!$C$7:$CN$74;$J$7)*0,25

The .xlam file is on all of the computers and I tried to put the file in dropbox. The other users are prompted to update the links to the file.

How do I avoid this?

Update: How do I adress the .xlam file on dropbox so other users can use it?