Robin Hammond
12/12/2006 11:41:00 PM
Emilio,
I wrote this a while back as a catch all function for workbooks, sheets or
charts. I'd probably do it differently now, but it seems to work, so why fix
it.
To make it work, you need to have the Trust Access to VB Project option on
in your security settings.
Function fnContainsMacros(vSheetOrBook As Variant) As Variant
Dim W As Workbook
Dim S As Worksheet
Dim C As Chart
Dim T As Variant
Dim cmpComponent As VBComponent
On Error Resume Next
Set W = vSheetOrBook
Set S = vSheetOrBook
Set C = vSheetOrBook
On Error GoTo 0
fnContainsMacros = False
If W Is Nothing And S Is Nothing And C Is Nothing Then
fnContainsMacros = False
GoTo EndRoutine
End If
If W Is Nothing Then
If S Is Nothing Then
Set T = C
Else
Set T = S
End If
On Error GoTo VBAccessDisabled
With T.Parent.VBProject
If .VBComponents.Count > 0 Then
On Error GoTo 0
For Each cmpComponent In .VBComponents
If cmpComponent.CodeModule = T.CodeName Then
If cmpComponent.CodeModule.COUNTOFLINES > 0 Then
fnContainsMacros = True
GoTo EndRoutine
End If
End If
Next cmpComponent
End If
On Error GoTo 0
End With
Else
On Error GoTo VBAccessDisabled
If W.VBProject.VBComponents.Count > 0 Then fnContainsMacros = True
On Error GoTo 0
End If
EndRoutine:
Set C = Nothing
Set W = Nothing
Set S = Nothing
Set T = Nothing
Exit Function
VBAccessDisabled:
Err.Clear
On Error GoTo 0
fnContainsMacros = "#N/A"
Resume EndRoutine
End Function
--
Robin Hammond
www.enhanceddatasystems.com
<emilio.ramirez.jr@gmail.com> wrote in message
news:1165962359.276949.49840@80g2000cwy.googlegroups.com...
> Hello all,
>
> I'm not so familiar with Excel macro programming (as is perhaps evident
> by my question).
>
> Is there a way to determine which Excel files have macros?
>
> The reason for this request is that our firewall/antivirus appears to
> be rejecting emails that have Excel attachments - only certain ones,
> though. I'm thinking that the reason some Excel files are being
> rejected is because they have a macro (or macros).
>
> Thanks for your input,
> Emilio
>