VBA: Check if a workbook has macros without having the "Trust access to VBA project" option checked?

[ou81aswell]

Is there a way to determine programatically if a workbook contains macros / VBA without having the "Trust access to VBA project" option checked?

Thanks!

 

[djreiswig]

I don't think you can do anything programatically without first having macros enabled.

 

[ou81aswell]

Thanks but I'm not trying to determine if macros are enabled. Rather, I'm trying to determine if a workbook has macros. The simple way of doing this would appear to be using the VBProject object but if "Trust access to VBA Project" is not checked, I can't use this method.

 

[JoeMo]

Here's an untested function that returns TRUE if a workbook has VBA modules and FALSE if not.

Function TestForVBComponents(wbkName As String) As Boolean
Dim VBCodeMod As Object
Dim ctAll As Long, ctMods As Long, ctShts As Long

Application.Volatile

ctAll = Workbooks(wbkName).VBProject.VBComponents.Count
ctShts = Workbooks(wbkName).Sheets.Count
ctMods = ctAll - ctShts - 1  '-1 for ThisWorkbook component
If ctMods > 0 Then
    TestForVBComponents = True
Else
    TestForVBComponents = False
End If

End Function

 

[ou81aswell]

Thanks but I think I tried that approach but wasn't able to access the VBProject object because of the security setting. I'll double check.

 

[ou81aswell]

As I suspected, you can't access the VBProject object if the trust access to VBA Project option is not set.

I'm going to take a different approach.

Thanks guys.

 

[Glory]

This seems to work.

Public Sub test99()
On Error GoTo Catch
MsgBox ThisWorkbook.VBProject.VBComponents.Count
Exit Sub
Catch:
MsgBox "Hey"

End Sub

 

[Glory]

Oh never mind, I missed the point of this thread. I was too busy looking to see whether I could check whether a project was trusted.

Now this leaves me wondering about the ultimate solution to this question.