Error while Checking for Regular Expressions Library in VBA

[ExcelMonkey]

The following macro is no longer working. I am getting a Run Time Error 1004
saying "Programmatic Access to Visual Basic Project not Trusted". It's
highlighting the line of code:

For Each ref In ThisWorkbook.VBProject.References

I have the Regular Expressions 5.5 clicked off. This was working fine in XL
2000 until I merged two XLAs together and started running it in XL 2003

Why is this?


Private Sub ExternalLinkChkBx_Click()
Dim ref As Object
Dim fRef As Boolean

If ExternalLinkChkBx = False Then
SelectAllChkBx = False
End If

If ExternalLinkChkBx = True Then
'Check to see if user has RegExp library
'installed. If not, prompt user to not
'choose this option.
For Each ref In ThisWorkbook.VBProject.References
If ref.Name = "VBScript_RegExp_55" Then
'Debug.Print ref.Name
'ThisWorkbook.VBProject.References.Remove ref
fRef = True
'MsgBox ("Regular Expressions 5.5 Enabled")
End If
Next
If Not fRef Then MsgBox ("You do not have the Regular Expressions
Library checked off in the Tools Menu. In order for this External Link Audit
to be completed, you need to reference this library. Go to to the VB Editor
and then to Tools/References. Scroll down to Microsoft VBScript Regular
Expressions 5.5 and click on it. Then run this audit again.")
If Not fRef Then ExternalLinkChkBx = False

End If
End Sub

 

[Bernie Deitrick]

ExcelMonkey,

Check your reference list for missing references.

For example, do you have a reference set to MS VBA Extensibility? It may be
a new version, so perhaps you have lost your reference to that - or to
another file.

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

in Tools=>Macros=>Security, have you elected to trust access to the Visual
Basic Project.

 

[ExcelMonkey]

Thanks Tom. I am assuming that this is a new option in XL 2003. I am also
assuming that I will now have to test for this prior to running this line of
code. I tried running the macro recorder but could not seem to record the
click of the "Trust access to the Visual". How do I do this in VBA?

Thanks

 

[Tom Ogilvy]

Chip Pearson posted this a short time ago in response to a related question:

Dim Ref As Object
On Error Resume Next
Set Ref = ThisWorkbook.VBProject.References("Excel")
If Ref Is Nothing Then
Debug.Print "No trust"
Else
Debug.Print "trust"
End If

This tests whether access to the VBA project has been enabled.

 

[Tom Ogilvy]

I think it was introduced in xl2002 - but don't recall for sure.

Recording won't work because it can't be set (or queried directly) with
code.

I posted a copy of a technique to test it posted previously by Chip Pearson.
(wrote it awhile ago, but forgot to sent it).