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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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 wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
>

Hi Nick

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

Regards

Paul


NickHK wrote:

> 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,
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" <[email protected]> wrote in message
news:[email protected]...
> Hi Nick
>
> I'm not sure I understand your reply. Are you referring to reading the
> Open Source PDF?
>
> Regards
>
> Paul
>
>
> NickHK wrote:
>
> > 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
> >
>

Hi Nick

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

Regards

Paul


NickHK wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Nick
> >
> > I'm not sure I understand your reply. Are you referring to reading the
> > Open Source PDF?
> >
> > Regards
> >
> > Paul
> >
> >
> > NickHK wrote:
> >
> > > 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,
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" <[email protected]> wrote in message
news:[email protected]...
> Hi Nick
>
> If not through VBA, do you have any idea by what means I can access
> this property?
>
> Regards
>
> Paul
>
>
> NickHK wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Nick
> > >
> > > I'm not sure I understand your reply. Are you referring to reading
the
> > > Open Source PDF?
> > >
> > > Regards
> > >
> > > Paul
> > >
> > >
> > > NickHK wrote:
> > >
> > > > 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
> > > >
> > >
>

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

Thanks, I was looking for that kind of solution.