Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Detecting if a macro is present

    Is there a way to detect if a macro is attached to a workbook, without actually open it?
    What I want to do is to loop through a directory, and make a list of all Excel files saved in that directory and add the information: macro attached or not.
    Is there a way to do that with VBA?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    I have some code that could be modified to find out if a workbook file contains a macro, but it would have to open the workbook. I don't know how to do it without opening it.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    I think I'm with Legare on this one. The reason for this is that excel itself thinks you've got a macro if you have a blank module or an empty line in a sheet module. So even if you could look at some file attribute while the file was closed, you'd have to open it to check that excel was telling you the truth as you want to hear it. On top of which, I don't know whether excel itself can tell until the file is open from the observation that if you have a passworded file with code in, you have to go through the password dialog before you get the macro alert.
    But having said that, the question I'd ask is why do you not want to open the files - doing it programatically wouldn't take particularly long, so are there other considerations here that I haven't spotted?

    Brooke

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    I was just wondering how macro anti-virus programs were detecting the presence of a macro.
    On the other hand, opening the workbook, detecting if a macro is present and closing the workbook again is not really a problem; however in some cases I may not know the passwords if password protected (from other users, or the passwords could be workbook dependent).
    Legare, your code interests me. Is it possible to give me some more information on it?

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    In which case, I have to ask, does anyone know if virus detection programmes can actually spot virii in passworded files? (Doesn't say much for the encryption if they can.) My initial response to this was that they must be able to, but if I send you a macro virus in a passworded file, is that a problem to you? You have to know the password to open the file, so the virus macro can't run unless you know the password, so is it a threat? Only if you know the password, which you would have got from me, which implies you know me better than strangers that passed on the information superhighway (though I am reminded here of a certain website - which shall remain nameless for the simple reason that I can't remember it's name - which gives you the password to view the code) and more importantly, this would imply that every virus checking routine available that's worth it's salt has access to the MS encryption code. Now if that were true I'd be a little bit worried. Anyone fancy doing a bit of testing here? Or just telling me I don't know what I'm talking about?
    I'd do the testing myself but I don't have any virii to hand to insert into a file, and don't particularly want any either.

    Just some thoughts.

    Brooke

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    Here is some code that will return the number of lines of code (including blanks, as it stands) in all code mudules, sheets, userforms etc in the active workbook. I know it is not exactly what you wanted, but I too know of no way to detect macros on a disk file.

    It requires that a reference to MSVBA Extensibility 5.* be established. <pre>Function CountCodeLines() As Long
    Dim vbProj As VBIDE.VBProject
    Dim nComps As Long
    Set vbProj = ActiveWorkbook.VBProject
    nComps = vbProj.VBComponents.Count
    For i = 1 To nComps
    CountCodeLines = CountCodeLines + _
    vbProj.VBComponents(i).CodeModule.CountOfLines
    Next i
    End Function</pre>


    Andrew C

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    First, I think that the anti-virus programs open the file as a binary file and scan it looking for patterns. The don't have Excel open the file as a workbook, Excel does not enter the picture. If the scanners had to load every application that had a data file on your disk to scan them, it would take months to do one scan on todays disks. On top of that, it would not be able to scan a file for which you do not have the program online where it could find it.

    The program I have was written to create hardcopy documentation for the code in Excel applications which I write. When the program is run, it asks you to point it at another Excel workbook. It then opens that workbook, and creates a .TXT or .RFT file with all of the code printed in Module and Procedure/Function in alphabetical order, and with each Procedure/Function starting on a new page with the Module and Procedure/Function name at the top of the page. It does not handle Class Modules though. I wrote it to meet a contract requirement to put the source code for a project into Escrow.
    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    (Hi Brooke!)

    I've read as far as 40040.

    I have code that will do a little bit more analysis than just see if there is macro code. I can, for example, list all the procedures in an XLS.

    I hva eno way of doing this WITHOUT haveing Excel loaded, but I have discovered (thanks Brooke!) that once I have loaded the app, the process is very fast and painless.

    I can post the code in VB/VBA if you want it.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    Andrew,

    This is exactly what I was looking for (I think). Where can I find the MSVBA Extensibility 5.* you are talking about?

    I'll write some code to loop through a directory, open the workbook, use your function to count the number of lines of code; if this number is different from zero, I know that there is a macro attached. Then I close the workbook, and I have my information.

    Something else that crossed my mind: there is something as the file properties and customdocumentproperties. Is there not automatically set a property saying a macro is present? I am not aware of any, but it is just a question.

    Thanks to everyone else who responded to my post.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    Hans,

    If you goto to Tools, References, (in the VBA environment) then you should be able to locate Microsoft Visual Basic for Applications Extensibility 5.3 (or similar), just select it and you should be ok. Sorry if my shorthand misled.

    As I mentioned, it includes blank lines so It will report lines for empty modules etc. I suppose you can ignore any case that reports less than 3 lines, as a sub or function would require at least3 lines. You could then loop through each component (sheets, modules etc) and include them only if the number of lines => 3. However that will also include such things as when a button is clicked in design mode and an event procedure is atutomatically inserted with a blank line.

    As far as I recall, Pearson Software have some code dealing with the VBE modules etc at <A target="_blank" HREF=http://www.cpearson.com>http://www.cpearson.com</A> , so you might get further help there.

    Best of luck

    Andrew

  11. #11
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    I don't know if this helps or not, or simply shows how dumb I am, but...

    If I open up an xls with Notepad, I appear to be able to determine if there is a macro by seeing the text<pre>_ V B A _ P R O J E C T</pre>

    quite close to the beginning. My limited knowledge, however, comes into play here. I tried interrogating the file using simple Open and Input instructions, but it appears to reach EOF after one input....

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    Leif,

    Do you know if _ V B A _ P R O J E C T is included even if a module has no code ?. I supect it might be, and that is probably what Excel looks at when opening a file, as it does seem to know before the file is open that macros (or modules) exist - hence the prompt to enable or disable.

    Did you try opening the file for Binary access(Open "Filename.xls" For Binary As #) ?. I think that should get round the EOF problem,

    Andrew

  13. #13
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    I tried opening as "for Input as" and got one input then EOF.
    I tried opening as "for Binary as" and after inputing the first bit of data, the macro seemed to run indefinitely, inputing zilch
    <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  14. #14
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting if a macro is present

    Using a utility called fileview - shareware from cover disc - I can open a blank workbook and then compare this to the same workbook with a simple macro recorded. As Leif says, the word _VBA_PROJECT turns up. If I resave this having stripped the code but left the module, _VBA_PROJECT is still there. Moreover, if I then save the version with a macro still in it as a passworded file, then take a peek at that, I can still see _VBA_PROJECT - the encryption starts further into the file.

    However, I've just opened up another file at random and this doesn't have the _VBA_PROJECT signature near the front but at the back so you can't rely on the word being in the same place all the time. I think I would run with the proposal of opening all files and counting the lines of code.

    Brooke

  15. #15
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Detecting if a macro is present

    Hans (et al),

    Have a look at <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q224/3/51.ASP>KB article Q224351</A> which has information about a downloadable dll for a document property reader that allows you to read/write a variety of file property information (for Office apps) without needing to open the file.

    If memory serves, something along the lines of HasMacros is a read-only property that can be returned using this (and it's this flag that is probably being read by the Office apps to determine the presence of macros before opening the file).

    Gary

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •