How to trap "Can't find project or library" error messages

[dbKemp]

I was looking for answer to the subject question, but didn't find
exactly what I was looking for. I have included a solution to the
problem: How to trap the "Can't find project or library" error when
an addin referenced by a workbook does not exist on the users
computer. If there is a MISSING: reference, Excel throws an error
that can not be trapped in code.

Do not reference the addin via Tools/References in the VBE. Rather
set the reference via when workbook opens.
Remove the reference to the addin when workbook closes so if it is
passed to someone else without the addin it will not cause an error.


I am using Excel 2003/ WinXP.

'Workbook Project Assumptions:
'Have reference for:
'Microsoft Scripting Runtime
'Microsoft Visual Basic for Applications Extensibility 5.x

'Addin Assumptions:
'The name of the Addin is "My Referenced Addin.xla"
'The Addin is stored in the "UserLibraryPath" folder
'The VBProject Name for the Addin is "My_Ref_Addin"
'There is a Sub in Addin called "SomeSubroutineInAddin"


'The following code is in the ThisWorkbook module of workbook
referencing Addin

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
RemoveReference
End Sub

Private Sub Workbook_Open()
SetAddinReference
End Sub

'===============================================================================================
'The following code is in any standard module of workbook referencing
Addin
Option Explicit

Private Const mcsADDIN_FILE As String = "My Referenced Addin.xla"
Private Const mcsADDIN_VBP_NAME As String = "My_Ref_Addin"

Public Sub SetAddinReference()
Const csMSG As String = "The My Referenced Addin is not available," _
& vbNewLine & _
"therfore the features of this workbook are not functional."
Dim sAddinPath As String

sAddinPath = GetAddinFolder & mcsADDIN_FILE

'Check to see if addin is on computer
If AddinExists(sAddinPath) Then
'If so, see if it is already referenced
If Not ReferenceExists(mcsADDIN_VBP_NAME) Then
'If not, Add a reference so code in addin can be called using
project code name
AddReference sAddinPath
End If
Else
'Let user know that the addin is not avaliable
MsgBox csMSG, vbExclamation
End If
End Sub

Public Function AddinExists(ByVal Path As String) As Boolean
'Use File System Object to see if Addin exists
Dim oFSO As Scripting.FileSystemObject

Set oFSO = New Scripting.FileSystemObject
AddinExists = oFSO.FileExists(Path)

Set oFSO = Nothing
End Function

Private Sub AddReference(ByVal Path As String)
'Add reference to Addin using full file path
ThisWorkbook.VBProject.References.AddFromFile (Path)
End Sub

Private Function GetAddinFolder() As String
'Use Application properties to get addin folder. Could be anywhere
With Application
GetAddinFolder = .UserLibraryPath & .PathSeparator
End With
End Function

Private Function ReferenceExists(ByVal ProjectName As String) As
Boolean
ReferenceExists = Not (GetReference(ProjectName) Is Nothing)
End Function

Private Function GetReference(ByVal Name As String) As Object
'Need reference to VBA Extensibility for this
On Error Resume Next
Set GetReference = ThisWorkbook.VBProject.References.Item(Name)
On Error GoTo 0
End Function

Private Sub RemoveReference()
'This removes the reference prior to saving so problem of
'"MISSING:" reference will not occur on other computer
Dim xObject As Object

Set xObject = GetReference(mcsADDIN_VBP_NAME)
If Not xObject Is Nothing Then
ThisWorkbook.VBProject.References.Remove xObject
End If
End Sub

'===============================================================================================
'The following code is in any standard module of workbook referencing
Addin
Option Explicit

'WHEN THIS IS CALLED IT WILL MAKE CALL TO ADDIN
Private Sub DoSomethingInAddin()
'SomeSutroutineInAddin is a Sub in the referenced Addin
My_Ref_Addin.SomeSubroutineInAddin
End Sub