Excel VBA Project References

[simora]

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

[Paul_Hossler]

Try this

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

[simora]

Hi Thanks:

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

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.

[snb]

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.

[simora]

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

 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.

[snb]

It should be:

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.

[Paul_Hossler]

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

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

[simora]

Thanks all.