PDA

View Full Version : Adding and Activating Reference



webunit
08-19-2020, 05:54 PM
I have several existing Excel (Microsoft Office 365) files that have a broken reference. I've already figured out how to remove the missing reference and add the proper path to the reference by running a little program. However, even after the reference is corrected, the program doesn't seem to recognize that the reference is corrected UNTIL I open the VBA code and click on Tools, References and then click OK. The code is password protected (I've checked and that doesn't seem to matter) and I would rather not have users need to go into the VB environment just to perform the steps I just mentioned. My question is is there a way to "activate" the reference programmatically without having to open VBA and using Tools, References? Thank you.

snb
08-19-2020, 11:48 PM
Yes, please show the code you are using now.

Paul_Hossler
08-20-2020, 05:54 AM
These are 3 macros that I use for working with references

You need to find the GUID (ListReferencePaths ) for the reference and then AddReference to add it. If it's already there, there's no error





'Macro purpose: To remove missing references from the VBE
Sub References_RemoveMissing()

Dim theRef As Variant, i As Long

On Error Resume Next

For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i

If Err <> 0 Then
MsgBox "A missing reference has been encountered!" _
& "You will need to remove the reference manually.", _
vbCritical, "Unable To Remove Missing Reference"
End If

On Error GoTo 0
End Sub


'Macro purpose: To add a reference to the project using the GUID for the reference library
Function AddReference(strGUID As String) As Boolean
Dim theRef As Variant, i As Long


'Set to continue in case of error
On Error Resume Next

'Remove any missing references
For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
Set theRef = ThisWorkbook.VBProject.References.Item(i)
If theRef.isbroken = True Then
ThisWorkbook.VBProject.References.Remove theRef
End If
Next i

'Clear any errors so that error trapping for GUID additions can be evaluated
Err.Clear

'Add the reference
ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, Major:=1, Minor:=0

'If an error was encountered, inform the user
Select Case Err.Number
Case 0
AddReference = True

Case Is = 32813
'Reference already in use. No action necessary
AddReference = True

Case Is = vbNullString
'Reference added without issue
AddReference = True

Case Else
'An unknown error was encountered, so alert the user
MsgBox "A problem was encountered trying to" & vbNewLine _
& "add or remove a reference in this file" & vbNewLine & "Please check the " _
& "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
AddReference = False

End Select

On Error GoTo 0

End Function


'Macro purpose: To determine full path and Globally Unique Identifier (GUID)
'to each referenced library. Select the reference in the Tools\References
'window, then run this code to get the information on the reference's library
Sub ListReferencePaths()
Dim i As Long
Dim r As Range

On Error Resume Next
Call ActiveWorkbook.Worksheets("References").Delete
ActiveWorkbook.Worksheets.Add.Name = "References"

With ActiveWorkbook.Worksheets("References")
.Range("A1") = "Reference name"
.Range("B1") = "Full path to reference"
.Range("C1") = "Reference GUID"
End With

With ActiveWorkbook.VBProject
For i = 1 To .References.Count
Set r = ActiveWorkbook.Worksheets("References").Rows(i + 1)
r.Cells(1).Value = .References(i).Name
r.Cells(2).Value = .References(i).FullPath
r.Cells(3).Value = .References(i).GUID
Next i
End With

On Error GoTo 0
End Sub

webunit
08-20-2020, 07:35 AM
Yes, please show the code you are using now.

Option Explicit
Sub AddReference()
'Original By Siddharth Rout, Stack Overflow
'https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically
Dim VBAEditor As VBIDE.VBE
Dim vbProj As VBIDE.VBProject
Dim chkRef As VBIDE.Reference
Dim BoolExists As Boolean
Set VBAEditor = Application.VBE
Set vbProj = ActiveWorkbook.VBProject
'~~> Check if "APFlame" is already added
For Each chkRef In vbProj.References
If chkRef.Name = "APFlameDll" Then
BoolExists = True
GoTo CleanUp
End If
Next
vbProj.References.AddFromFile "C:\APFlame\APFlameDLL.tlb"
CleanUp:
If BoolExists = True Then
MsgBox "Reference already exists"
Else
MsgBox "Reference Added Successfully"
End If
Set vbProj = Nothing
Set VBAEditor = Nothing
End Sub