Using office 2013. If have written several functions and need all users in the office to be able to use the functions. We have a third party program that prevents use from using macro enabled spreadsheets, so I created an addin with a .xlam extension and distributed it to the other users in the office. In a spreadsheet, the function is called as =MinIf(A1,B1,C1). When the same spreadsheet is opened from another computer, the cell now contains =SomePath!MinIff(A1,B1,C1) where SomePath is the path to the copy to where the addin is installed on the original computer (ie c:\users\..\addins).

We need to be able to distribute the addin and have it not add the path to the function calls. What is the right way to do this?

Thanks Ken

Re:  function tries to go home

The functions should be in a standard module.
(not a Sheet module or the ThisWorkbook module)

The functions must be Public...
  Public Function MinIf(x, y, z) as Double
  'calculate code
  End Function

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

The function is located in a standard module and is a public function. The functions are located in the module called functions. The spreadsheet calling the function is a .xlsx. The Addin is a .xlam file.

Public Function MinIf(ParamArray Args() As Variant) As Double

Code
End Function

Re:  add-in problems

I had add-ins all over a large company, years back, and never saw that issue.
That, of course, was before xl2013.
Does each user get his own copy of the add-in and install it in the "add-ins" folder?
Is the add-in file loaded on each machine by using the browse button in the add-ins dialog?

That's all I got.
'---
Jim Cone

Re:  function tries to go home

The functions should be in a standard module.
(not a Sheet module or the ThisWorkbook module)

The functions must be Public...
  Public Function MinIf(x, y, z) as Double
  'calculate code
  End Function

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
or
http://jmp.sh/K95N3ee

• Edited by James Cone 14 hours 22 minutes ago

Re:  function tries to go home

The functions should be in a standard module.
(not a Sheet module or the ThisWorkbook module)

The functions must be Public...
  Public Function MinIf(x, y, z) as Double
  'calculate code
  End Function

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
or
http://jmp.sh/K95N3ee

• Edited by James Cone Monday, May 18, 2015 5:06 PM

Re:  add-in problems

I had add-ins all over a large company, years back, and never saw that issue.
That, of course, was before xl2013.
Does each user get his own copy of the add-in and install it in the "add-ins" folder?
Is the add-in file loaded on each machine by using the browse button in the add-ins dialog?
[Edit]
Do not use Option Private Module
[End Edit]

That's all I got.
'---
Jim Cone

• Edited by James Cone 17 hours 51 minutes ago another thought

Re:  add-in problems

I had add-ins all over a large company, years back, and never saw that issue.
That, of course, was before xl2013.
Does each user get his own copy of the add-in and install it in the "add-ins" folder?
Is the add-in file loaded on each machine by using the browse button in the add-ins dialog?
[Edit]
Do not use Option Private Module
[End Edit]

That's all I got.
'---
Jim Cone

• Edited by James Cone Friday, May 15, 2015 1:39 PM another thought

Re:  add-in problems

I had add-ins all over a large company, years back, and never saw that issue.
That, of course, was before xl2013.
Does each user get his own copy of the add-in and install it in the "add-ins" folder?
Is the add-in file loaded on each machine by using the browse button in the add-ins dialog?
[Edit]
Do not use Option Private Module
[End Edit]

That's all I got.
'---
Jim Cone

• Edited by James Cone Friday, May 15, 2015 1:39 PM another thought

Re:  add-in problems

I had add-ins all over a large company, years back, and never saw that issue.
That, of course, was before xl2013.
Does each user get his own copy of the add-in and install it in the "add-ins" folder?
Is the add-in file loaded on each machine by using the browse button in the add-ins dialog?
[Edit]
Do not use Option Private Module
[End Edit]

That's all I got.
'---
Jim Cone

• Edited by James Cone Friday, May 15, 2015 1:39 PM another thought

Yes, well, I have tried it both ways: copy to local machine and kept it on a shared drive. The add-in in is selected by going to developer\add-ins\Browse and navigate to the add-in. I first tried having it copied to each users work station. The result was the path to the add-in on the source computer would get added to the function call when accessed from a different computer. I then removed the add-in and re-added it but not having it copy to the local machine and stay on a network share. In excel\options\add-ins the path would point to the network share. The local path would still get added to the function call, but it would get an error that it could not be found. If the add-in was deselected and re-added, the functions would work again without the path. This almost appears to be a windows 7 opportunity since Win 7 places the add-ins in each users folder. I created the add-in by creating a New worksheet. Opened Visual Basic from the developers tab. Right clicked on the VBA Project for the new file and added a module. In that module I created my function as a public function. I saved the spreadsheet as an excel add-in which resulted in a file with a .xlam extension.

I found a solution, but I have a however. I had not added the Add-In as a reference. Once I added it as a reference, it stopped adding the path to the function call when opened from a different computer.

However, in my office environment, the selection of the reference will not save. In the VBA environment, tools\references, the add-in is selected. Save the VBA and save the Excel. When the excel is reopened, the add-in is no longer selected. It is listed, but not selected.

As a note, I changed the name under properties, so you could tell in the reference list which VBA project it was.Any Ideas why the reference will not save?

Re:  "Any Ideas why the reference will not save?

Might be the third party program you mentioned in your original post.
Suggest you post a new question, as that could get you some wider exposure.
'---
Jim Cone

Hi,

This is the forum to discuss questions and feedback for Microsoft Excel, your issue is more related to Excel DEV, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support