Excel VBA Project References

I have a sheet with all the Project references that I want installed on the user's system to enable Excel Macros run correctly.
I need to go through the list on the GUID worksheet , Column C, and see if its already installed on the Users system.
If its not installed, I want to install it.

If an error occurs, I want to have the error written to the worksheet in Column H
See attached workbook
Using Office 2007

Try this

Code:

---

Option Explicit

Sub CheckReferences()
    Dim i As Long, j As Long
    Dim rGuids As Range
   
    Set rGuids = Worksheets("GUID").Cells(1, 1).CurrentRegion.Columns(4)
   
    For i = 2 To rGuids.Rows.Count
        For j = 1 To ThisWorkbook.VBProject.References.Count
            If ThisWorkbook.VBProject.References(j).GUID = rGuids.Cells(i, 1).Value Then
                GoTo NextGUID
            End If
        Next j
        Worksheets("GUID").Cells(i, 8).Value = "Missing"
NextGUID:
    Next I
   
End Sub

---

Hi Thanks:

I modified the code to ADD the missing References like this;

Code:

---

Worksheets("GUID").Cells(i, 8).Value = "Missing"
    Gref = Worksheets("GUID").Cells(i, 4).Value
           
    ActiveWorkbook.VBProject.References.AddFromGuid Gref, Worksheets("GUID").Cells(i, 5).Value, Worksheets("GUID").Cells(i, 6).Value
                               
NextGUID:

---

What I can't figure out is why is
Microsoft VBScript_Regular Expressions 5.5 not being installed.
All the other References are OK.

Code:

---

Sub M_snb()
  sn = sheets("GUID").Cells(1, 1).CurrentRegion
    For Each it In ThisWorkbook.VBProject.References
    c00 = c00 & " " & it.GUID
  Next

  For j = 2 To ubound(sn)     
    If instr(c00,sn(j,4))= 0 then thisWorkbook.VBProject.References.AddFromGuid sn(j,4), sn(j,5), sn(j,6)
  Next
  End Sub

---

When you use VBA you should avoid any interaction with a worksheet as much as possible.

Thanks snb:


Good information.
However, What I'm trying to figure out is why
Microsoft VBScript_Regular Expressions 5.5 is not being installed.

Your code did not execute. It gave an error at

Code:

---

ThisWorkbook.VBProject.References.AddFromGuid it, sn(j, 5), sn(j, 6)

---

it = Empty

Didn't fully understand the detailed logic of your code. I couldn't see exactly where its determining if a VBProject.Reference is already installed.

It should be:

Code:

---

thisWorkbook.VBProject.References.AddFromGuid sn(j,4), sn(j,5), sn(j,6)

---

Both Librareis have the same GUID.
Probably VBA finds it hard to choose which one to load.

Quote:

---

Originally Posted by simora

Hi Thanks: What I can't figure out is why is Microsoft VBScript_Regular Expressions 5.5 not being installed. All the other References are OK.

---

I've always used AddFromFile

Code:

---

Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (X86)\Microsoft Office\Office14\MSPPT.OLB"

---

Thanks all.