Reference VBA tweaking

[simico]

Hey all,

Excel 2007 and Windows XP are my particulars.

I have VBA code that works very well, but it needs a little tweaking. I've tried several things myself, but keep getting errors.
The code checks to see if a reference exists; if it does, then it exits the sub. If it does not, it adds it and runs a macro.

Here's what I want it to do:
The code checks to see if a reference exists; if it does, runs a macro and then it exits the sub. If it does not, it adds it and runs a macro.

The code:

Sub e8a2ad2182454361bba1f17debd02f3b_Click()
Dim Reference As Object
          With ThisWorkbook.VBProject
           For Each Reference In .References
                 If Reference.Description Like "Reporter Bridge" Then Exit Sub
           Next
           .References.AddFromGuid "{F8E5D4DE-ED63-43DE-A82F-68DC97B68A5E}", 2, 4
     End With
     
Call Bubba
End Sub

Thanks!

 

[Hermanito]

Try this adapted code (I assumed the macro you want to run is called Bubba? Funny naming conventions you use )

Sub e8a2ad2182454361bba1f17debd02f3b_Click()
   Dim Reference As Object
   Dim ReferenceIsMissing as Boolean
   ReferenceIsMissing = True
   With ThisWorkbook.VBProject
      For Each Reference In .References
         If Reference.Description Like "Reporter Bridge" Then 
            ReferenceIsMissing = False
         End If
      Next
      If ReferenceIsMissing Then
         .References.AddFromGuid "{F8E5D4DE-ED63-43DE-A82F-68DC97B68A5E}", 2, 4
      End If
   End With
   Call Bubba
End Sub

 

[Ruddles]

Sub e8a2ad2182454361bba1f17debd02f3b_Click()

Dim Reference As Object

  With ThisWorkbook.VBProject
     For Each Reference In .References
         If Reference.Description Like "Reporter Bridge" Then 
[COLOR=green]             ' found - call Bubba and exit[/COLOR]
             Call Bubba
             Exit Sub
         End If
     Next
[COLOR=green]     ' not found - add reference, then call Bubba and exit
[/COLOR]     .References.AddFromGuid "{F8E5D4DE-ED63-43DE-A82F-68DC97B68A5E}", 2, 4
 End With
     
Call Bubba

End Sub

 

[simico]

Thanks both of you for taking time to try to help.

Ruddles, yours was one of the ways I was trying myself, and it keeps getting an error at ".References.AddFromGuid "{F8E5D4DE-ED63-43DE-A82F-68DC97B68A5E}", 2, 4"

Hermanito, yours works beautifully! I wouldn't have thought to do it quite like that, which is why I came here for help. As far as the name "Bubba", I just threw that into the post, rather than the actual random letters and numbers that are actually assigned to the action that is being performed.

Thanks!
Simico

 

[Hermanito]

I find it strange that Ruddles' solution doesn't work for you...

Apart from the fact that my code is elegant and robust, while his is more spaghetti-like , they are actually doing the same thing.

Glad you got it working for you...

 

[Ruddles]

Yes, in my defence I thought changing:-

If Reference.Description Like "Reporter Bridge" Then Exit Sub

to:-

If Reference.Description Like "Reporter Bridge" Then 
   Call Bubba
   Exit Sub
End If

was the smallest possible modification to the existing code and that the OP would be able to follow it more clearly that way.

As for 'spaghetti code':-

• Italians like spaghetti!
• I always reckon Exit Subs are allowed. GoTos are not!

 

[Hermanito]

I'll give you that

You chose indeed the smallest change, and much of the spaghetti-ism was inherited from simico

I defend my solution because:
- it is more self-explanatory about what is happening inside the sub
- it works

still wondering though why yours doesn't work for simico, I can't see the reason (which is another reason my code is better, yours is too difficult to debug )