Remove broken reference from VBAProject

Hi,
I have VBA integrated into accouting application.

I am trying to remove a broken VBA project reference so that I can add the currently available reference. The broken reference is in the VBProject.References collection but when I use the Remove method I get the error "Object library not registered." When I try to add the correct library I get the error "Name conflicts with existing module, project, or object library." I don't want to use late binding.

Here is code that illustrates this problem.

   Const TargetReferenceGUID = "{????????-????-????-????-????????????}" ' Here goes the GUID of my broken reference
  
   Dim TargetReference As Object
   Dim ReferenceIndex As Long
   Dim Index As Long

   ' Find existing reference
   For Index = 1 To VBAHost.VBProject.References.Count
      If VBAHost.VBProject.References(Index).GUID = TargetReferenceGUID Then
         ReferenceIndex = Index
         Exit For
      End If
   Next Index
  
   ' Delete existing reference
   If ReferenceIndex > 0 Then
      VBAHost.VBProject.References.Remove VBAHost.VBProject.References(Index)
   End If
  
   ' Add desired reference
   VBAHost.VBProject.References.AddFromGuid TargetReferenceGUID, ?, ?

Does anyone know the solution?
Thanks in advance.
Ilia

Hi Ilia,

The HostCheckReference Event occurs when VBA loads a project and detects a non-default reference, thereby giving the host application an opportunity to change the version numbers.

Here is some example VB code for the HostCheckReference handler. You will want to implement this in a more robust way:
 
    Private Sub VBAHost_HostCheckReference(ByVal Saving As Boolean, ByVal LibraryGuid As String, MajorVersion As Long, MinorVersion As Long)
        ' Check to see if the reference is Excel
        If (LibraryGuid = "{00020813-0000-0000-C000-000000000046}") Then
            ret = MsgBox("Change Excel TypeLibrary Reference version (currently " & CStr(MajorVersion) & "." & CStr(MinorVersion) & ")?", vbYesNo)
            If (ret = vbYes) Then
                ' Are we saved on an Office2k machine?
                If (MinorVersion = 3) Then
                    ' Update for Office2k3 machines
                    MinorVersion = 5
                ElseIf (MinorVersion = 5) Then
                    ' Update for Office2k machines
                    MinorVersion = 3
                Else   
                    MsgBox "Unexpected Excel Reference Type Library version!"
                End If
            End If
        End If
    End Sub

Jim G

Hi Jim,
Thanks a lot.
You're real guru.

Kind regards,
Ilia