Addin reference

[Guest]

I have an addin I created that has some user defined functions (udf). They
work great. My only problem is that I sent the addin and a spreadsheet that
utilized the udf's in the addin to a friend. When he opened up the related
spreadsheet, it gave errors and pointed to the location of the addin on my
computer. Is there a way to eliminate the direct references so Excel will
see the addin on his computer and use the functions in the local addin and
not return an error?

Thanks!

 

[Jim Rech]

Your friend can use Edit, Links, Change Source to point to the location of
the add-in on his machine.

--
Jim Rech
Excel MVP
|I have an addin I created that has some user defined functions (udf). They
| work great. My only problem is that I sent the addin and a spreadsheet
that
| utilized the udf's in the addin to a friend. When he opened up the
related
| spreadsheet, it gave errors and pointed to the location of the addin on my
| computer. Is there a way to eliminate the direct references so Excel will
| see the addin on his computer and use the functions in the local addin and
| not return an error?
|
| Thanks!

 

[Guest]

Thanks! I should have thought of that. Is there a way to automate it or do
I have to tell anyone who I share the addin with that they may need to do
this?

Thanks again.
Dave

 

[mangesh_yadav]

If you want to automate it, then you have to make the user save th
addin in a particular directory (exception being: if it is not in th
same directory as that of the file which uses it). Anothet solution o
course will be to have your UDFs in the same file as the file in whic
you are using them. This will save you the trouble of adding addins

 

[Stephen Bullen]

Hi Dave,

Is there a way to automate it or do
I have to tell anyone who I share the addin with that they may need to do
this?

Assuming the addin will be installed, it could contain a class module that
hooks application events and detects when a workbook is opened. It could
then check if the workbook contains any links to the addin, and if so change
them to itself:

In a standard module:

'Create an instance of our event-handling class
Dim mclsAppEvents As CAppEvents

Sub Auto_Open()
Set mclsAppEvents = New CAppEvents
End Sub

'A public function to test it with
Public Function AddTwo(d1 As Double, d2 As Double)
AddTwo = d1 + d2
End Function

In a class module called CAppEvents:

Dim WithEvents moXL As Application

Private Sub Class_Initialize()
Set moXL = Application
End Sub

Private Sub moXL_WorkbookOpen(ByVal Wb As Workbook)

Dim vLink As Variant

'Scan through the Excel links in the workbook
For Each vLink In Wb.LinkSources(xlLinkTypeExcelLinks)

'Does it link to this addin name?
If InStr(1, vLink, "\" & ThisWorkbook.Name, vbTextCompare) > 0 Then

'Yes, so update it to point to this file
Wb.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks
Exit For
End If
Next

End Sub

In this case, they'll still get an 'Update Links' prompt, but if they click
'Don't update', the code in the addin will update the links for them.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Guest]

That's it! Thanks for the help.

Dave

Hi Dave,


Assuming the addin will be installed, it could contain a class module that
hooks application events and detects when a workbook is opened. It could
then check if the workbook contains any links to the addin, and if so change
them to itself:

In a standard module:

'Create an instance of our event-handling class
Dim mclsAppEvents As CAppEvents

Sub Auto_Open()
Set mclsAppEvents = New CAppEvents
End Sub

'A public function to test it with
Public Function AddTwo(d1 As Double, d2 As Double)
AddTwo = d1 + d2
End Function

In a class module called CAppEvents:

Dim WithEvents moXL As Application

Private Sub Class_Initialize()
Set moXL = Application
End Sub

Private Sub moXL_WorkbookOpen(ByVal Wb As Workbook)

Dim vLink As Variant

'Scan through the Excel links in the workbook
For Each vLink In Wb.LinkSources(xlLinkTypeExcelLinks)

'Does it link to this addin name?
If InStr(1, vLink, "\" & ThisWorkbook.Name, vbTextCompare) > 0 Then

'Yes, so update it to point to this file
Wb.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks
Exit For
End If
Next

End Sub

In this case, they'll still get an 'Update Links' prompt, but if they click
'Don't update', the code in the addin will update the links for them.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk