problem sharing an excel workbook when my formulas reference user.

[Guest]

Hi, I have wrote several simplifying user defined formulas related to
structural engineering. I have put these formulas in an excel addin and have
successfully used the formulas to create workbooks on my own computer. I
wanted to distribute these workbooks to other engineers in my office so I
sent them the addin file and the workbooks, and got them to install the addin
file before opening the workbook.

However upon opening the workbook the other engineers found that all of the
userdefined formulas in the workbook reference the Microsoft Addin file on my
own computer. Thus the only way for another user to use the workbook is to
delete the references to my own computer, which is really anoying and negates
the time savings that was gained by programming the formulas.

I think that a solution would be to make Excel look in the addin file on the
users computer for the formulas rather than trying to reference the addin
file on my own computer. Does anyone know how to do this? Or any other
solution??

 

[Guest]

You might want to consider publishing your AddIn on a shared file server, if
you have one, and write some VBA code to do the following on the
Workbook_Open event:

* Install your AddIn on a shared file server, where all users have
read-only access, at least
* Test for the presence of the AddIn file on the local machine
* Test to see if the AddIn is installed
* Test for the current AddIn version on the local machine, compared with
that on the server (not sure how to do this now)
* Proceed with whatever the workbook does with the AddIn

Following is some code I threw together for this reply, which is based on
the assumption that the 'master' AddIn file that all users reference being on
the server <AddInPath>:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim AddInName As String
AddInName = "My AddIn"
If AddIns(AddInName).Installed = True Then
AddIns(AddInName).Installed = False
End If
End Sub

Private Sub Workbook_Open()
Dim AddInName As String
Dim AddInExtention As String
Dim AddInPath As String
Dim i As Integer
Dim IsAddInAvailable As Boolean

AddInName = "My AddIn"
AddInExtention = ".xla"
AddInPath = "\\servername\path\"
For i = 1 To AddIns.Count
If AddIns(i).Name = AddInName & AddInExtention Then
IsAddInAvailable = True
Exit For
End If
Next i

If IsAddInAvailable = True Then
If AddIns(AddInName).Installed = False Then
AddIns(AddInName).Installed = True
End If
Else
AddIns.Add(AddInPath & AddInName & AddInExtention, False).Installed
= True
End If

End Sub

It's been a long time since I've dabbled with this concept, and don't recall
all of the quirks.

HTH,
VBA Dabbler