How to programmatically test whether VBA code exists in an Excel file

[Paul Martin]

Hi all

I am writing some code that loops through multiple Excel workbooks (may
be open or not) and I am wondering if there's an Application or
VBProject property that I can access that indicates that a given
workbook has code in it.

It seems logical that there is such a property, as a workbook with code
prompts the user that macros are present. Is this property available
through VBA? And if so, how?

Thanks in advance

Paul Martin
Melbourne, Australia

 

[NickHK]

Paul,
See the recent thread in this NG "Need to scan xls files and identify if
they use macros...wanna use VB".

NickHK

 

[Paul Martin]

Thanks for pointing me to that post, Nick. I'm not sure if it helps
me, because I'm trying to find a relatively easy solution. Rather than
scanning all the modules, I was thinking there must be a byte or
something that Excel reads when opening a file to test whether macros
exist. Is there an API or something that can enable me to do such a
test?

Regards

Paul

 

[NickHK]

Paul,
Unless you read through the Excel file format and understand where such VBA
records are, you are stuck with the suggestions in that thread.
Anyone else can come up with a different approach....

NickHK

 

[Paul Martin]

Hi Nick

I'm not sure I understand your reply. Are you referring to reading the
Open Source PDF?

Regards

Paul

 

[NickHK]

Paul,
Yes. If you want to test a couple of bytes for the presence of VBA code,
you'll need to understand how Excel reads/writes such information in its
format.
Good luck.

NickHK

 

[Paul Martin]

Hi Nick

If not through VBA, do you have any idea by what means I can access
this property?

Regards

Paul

 

[NickHK]

Paul,
The "property" only exists because Excel (or some app that understands
Excel's file format) reads the binary and checks those records.
Unless you do this yourself (or one of the other suggestion in that thread),
you have to leave it to Excel to do the hard work, then work through Excel.

NickHK

 

[Paul Martin]

For anyone interested, the solution I ran with was:

Private Function CodeExists(ByVal intWbkA As Integer) As Boolean
Dim vbc As VBComponent

For Each vbc In Workbooks(intWbkA).VBProject.VBComponents
With vbc.CodeModule
' Only count code lines after declarations
If .CountOfLines - .CountOfDeclarationLines > 0 Then
CodeExists = True
Exit Function
End If
End With
Next vbc
End Function

Thanks for the suggestions

Paul