Creating Addin that will work regardles saved folder location

[xluser2007]

Hi All,

At work there is acustom addin for a UDF that we all rely on.

The addin, when it gets called correctly works as desired.

However there are issues, depending on where the user stores the Addin.

As I understand the:

• default MS defined Excel ADDIN path is
  • C:\Program Files\Microsoft Office\OFFICE11\ADDINS (C Drive).
• All our computers are set up so that the default Addin in path is
  • D:\Documents and Settings\[user name]\Application Data\Microsoft\AddIns (D Drive).

The functions returns a #NAME? value depending on where individual user stores the Addin and saves the relevant spreadsheet. The person who next opens the spreadsheet should have the Addin stored in the same position else the #NAME? problem occurs.

How can this problem be fixed, I am thinking of recreating the Addin, so that it will work regardless of where the user places the file (rather than ask everyone across the firm to place it in a certain location).

Is this possble? If so could anyone please offer some guidelines?

regards

[xluser2007]

Hi All,

Suppose I take the following simple UDF which I want to convert to an Addin that can be used on a common spreadsheet (on the network server), regardless of where each individual user saves the Addin on their own desktop Excel.

[vba]Option Explicit

Function Squaredvalue(x As Long)

Squaredvalue = x ^ 2

End Function[/vba]
what are the steps required to save this UDF as an Addin so that it will not cause conflicts if it is used in S:\Test.xls (Network drive file), if I place it in C:\Program Files\Microsoft Office\OFFICE11\ADDINS, and if another user places it in D:\Documents and Settings\[user name]\Application Data\Microsoft\AddIns, without causing the #NAME? error.

If anyone could please help clarify/ answer this I would be really thankful.

[TomSchreiner]

I'm betting that your real function has reference types such as ranges. Does it? If not, I do not see how the location of an addin would matter with a function such as this.

Post your actual function...

[xluser2007]

Tom,

You are spot on! It does contain reference to ranges.

Unfortunately, I can't post the actual function, as it is not my work to publish directly.

However, could we just experiment with the following simple alternative, taken from here.

[vba]Function LinkAddress(cell As range, _
Optional default_value As Variant)
'Lists the Hyperlink Address for a Given Cell
'If cell does not contain a hyperlink, return default_value
If (cell.range("A1").Hyperlinks.Count <> 1) Then
LinkAddress = default_value
Else
LinkAddress = cell.range("A1").Hyperlinks(1).Address
End If
End Function [/vba]
Could you please explain how to correct for this range issue that you had suggested was causing the problem?

[mdmackillop]

I don't see how the add-in location could affect the function, unless there was something specific that identified/used the add-in location.

[xluser2007]

malcolm,

Thanks for your response.

So if i were to create an addin from the UDF listed in my previous post, could you please outline any necessary steps to ensure that it would not be affected by where each individual user stored their addin (and thus saved the common file relying on it by using 'thier peronally stored version)?

This was created ages ago, on excel 97 or 2000, not sure if this affects anything given that we all run 2003 now.

[mdmackillop]

I cannot see how the add-in location could affect the function, so no steps are required.

[xluser2007]

I was reading Daily Dose of Excel,

And this article by Jan Karel Piterse is exactly what my problem at hand is.

http://www.jkp-ads.com/articles/FixLinks2UDF00.asp

Could anyone please explain how to apply the seond method i.e. what code is required to make this happen?

regards,

[Bob Phillips]

You mean the copy code to each workbook solution?

[xluser2007]

Yes, Bob, that's the one.

Not trying to sound silly here, but is JKP trying to suggest copying the addin code programattically to each workbook that uses it(as he mentions "Trust Access to Visual Basic Project" is required to be set)?

Or, does he mean physically copying-pasting the code into the workbook causing the #NAME? as PUBLIC UDF?

[Bob Phillips]

I think he is suggesting programmatically adding the functions to the target workbooks, copying from the add-in.

You will still have some logic to determine whether a workbook gets that code.

[Bob Phillips]

Maybe the best way is to have a menu that says 'Copy functions to workbook. But you would still have to test that Trusted access is set.

[xluser2007]

Thanks for that. Then that approach doesn't sound like the most optimal (in terms of maintenance) way to go for solving this issue going forward.

If I could go back a few steps, why does this problem even occur, I thought Addins worked regardless of where they were saved?

If I were to fix a location, how do I determine which is the one to use? I have opened the Add-in code (just does a UDF that takes a NEast pointing diagonal sum across a specified range).

• There is nothing in the code that asks it to be stored in a particular location, so despite my colleagues and I saving it in different places (and then saving the target workbooks), when another colleague opens the book, it refers to the previous users Addin-location. Moving the addin to that location causes it to sometimes refer to the location from which the current user just moved the addin (?).
• Eventually after much fiddling around, we can find-replace out the "’C:\data\FixLink2UDF.xla’!" style problem path with a "" string and get it to work, but this is the issue that I'm trying to fix.

Thanks for your help and interest on this Bob, the above is just more context to help get my head around the problem.

[Bob Phillips]

Even if you don't see a link, there is one, Excel has to know where the file is (and don't forget, what happens if two addins have the same named UDF?), and when it moves and Excel can't find that addin anymore, it shouts.

Optimal, no, but it would work. But have you looked at Jan Karel's third suggestion, that should work well, and he does provide all the code pre-packaged.

[xluser2007]

Even if you don't see a link, there is one, Excel has to know where the file is (and don't forget, what happens if two addins have the same named UDF?), and when it moves and Excel can't find that addin anymore, it shouts.

Very nicely explained indeed.

Optimal, no, but it would work.

Bob, still unsure how to determine the 'correct' location for our addin - what is the default, because our addin has a UDF called "diagonal", and no other one exists of that name, also no other same addin name exists on our server. So this solutioin would work, if I knew how to determine the default location?

But have you looked at Jan Karel's third suggestion, that should work well, and he does provide all the code pre-packaged.

As I understand, I need to go to the actual addin (*.xla file), that everyone copies into their own relevant folders, and paste his code, step by step into the addin file as either a module or Thisworkbook (as instructed), save and then get everyone to copy this addin into their personal location of choice.

Thus we don't need to change the code anywhere else, but the surce addin itself. Is that correct?

[Bob Phillips]

Bob, still unsure how to determine the 'correct' location for our addin - what is the default, because our addin has a UDF called "diagonal", and no other one exists of that name, also no other same addin name exists on our server. So this solutioin would work, if I knew how to determine the default location?

The addin location is easily forund with

[vba]

ThisWorkbook.Path
[/vba]

As I understand, I need to go to the actual addin (*.xla file), that everyone copies into their own relevant folders, and paste his code, step by step into the addin file as either a module or Thisworkbook (as instructed), save and then get everyone to copy this addin into their personal location of choice.

Thus we don't need to change the code anywhere else, but the surce addin itself. Is that correct?

Yes you would, but you would also need to change it to do a lookup-replace on each of your functions. Jan Karel provides an example looking for a UDF called UDFDemo.