sharing an add-in function for multiple users

[Brian K. Sheperd]

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 Roberts]

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]

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

 

[Gareth Roberts]

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

 

[Brian K. Sheperd]

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

 

[Brian K. Sheperd]

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]

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