Solved: Excel freezes when code to add reference runs

[Danny]

Greetings,
I am trying to use a code by Ken Puls that I found in the KB (http://www.vbaexpress.com/kb/getarticle.php?kb_id=267)
I have put this code in workbook module to run when the workbook opens. The code works perfectly if the TRUST ACCESS TO VISUAL BASIC PROJECT, in the Macro security settings, is checked, but if this option is not checked Excel will freeze up, and i have had to use the task manager to end the program.
So, my first question is, am i doing something wrong? And if not, is there any way to test if access to visual basic project is allowed before this code runs? Several people will use this file, but not all of them will need to reference the Word library, and they will not have the trust access to vb project option checked.

Here is the code:
[VBA]Private Sub Workbook_Open()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library

Dim strGUID As String, theRef As Variant, i As Long

'Update the GUID you need below.
strGUID = "{00020905-0000-0000-C000-000000000046}"

'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 Is = 32813
'Reference already in use. No action necessary
Case Is = vbNullString
'Reference added without issue
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!"
End Select
On Error GoTo 0
End Sub
[/VBA]

[GTO]

Hi Danny,

It looks to me as Excel is not "freezing up", but is entering an infinite loop. In essence, if "Trust Access" is not ticked, then 'ThisWorkbook.VBProject.References.Count' will error and will not return a count. As we have error handling disabled, it just keeps running the For...Next loop.

To check for this, try tacking the below in, immedietely below the 'On Error Resume Next' statement.
[vba]
'Set to continue in case of error
On Error Resume Next

QuickCheck = ThisWorkbook.VBProject.References.Count

If Err.Number > 0 Then
MsgBox Err.Description & vbCrLf & vbCrLf & _
"To fix this, go to Tools|Macro|Security, select the tab (...etc).", vbCritical, _
"Exiting Program"
Err.Clear
Exit Sub
End If
[/vba]

Remember to declare the added variable 'QuickCheck' at the top where the other variables are declared.

Hopefully that helps, but I would ask as to why any of the users will need to load Word's library? If there is other code in the project that is written "early-bound", I believe you could change the code to "late-bound" and avoid the hassle alltogether.

Hope that helps,

Mark

[GTO]

ACK!

I forgot to mention - with the code the way you had it, when it "froze" (entered an endless loop), you could stop it by pressing CTRL + Pause.

[Bob Phillips]

Use this code to check if that option is set, and exit with a warning to the user if not

[vba]


Function VBAIsTrusted() As Boolean
Dim mpVBC As Object
Dim mpAlerts As Boolean
mpAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Set mpVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = mpAlerts
VBAIsTrusted = Not mpVBC Is Nothing
End Function
[/vba]

[Danny]

Thanks guys both of these solutions work well.