How to identify the excel file contains macro or not

Question

Hi

In my project i need to identify whether the excel file contains the macro or not. I have the content of the excel file while opening it but from that content how can i check if it contains the macro or not?  Please advise.

Thanks

Bino Mathew

Answer 1

Which version of Excel are you using?

If you have Excel 2007 or later, you can use the HasVBProject property of a workbook. For example:

If ActiveWorkbook.HasVBProject Then
    ActiveWorkbook.SaveAs "Test.xlsm", xlOpenXMLWorkbookMacroEnabled
Else
    ActiveWorkbook.SaveAs "Test.xlsx", xlOpenXMLWorkbook
End If

---

Regards, Hans Vogelaar

Answer 2

Unfortunately

.HasVBProject

is an unreliable way to check if the excel file has macros. To test it, create a workbook, say Book1.xlsm and insert a module but don't type any code. Close the file and in a new Excel file run this code from a module.

Sub Sample()
    Dim wb As Workbook
    
    Set wb = Workbooks.Open("C:\BOOK1.XLSM")
    
    If wb.HasVBProject Then MsgBox "CONTAINS MACROS" Else MsgBox "DOES NOT CONTAINS MACROS"
End Sub

You will get "CONTAINS MACROS" which is incorrect as it has a VBComponent but not a macro. Use this method to check if the Excel File has macros

Sub Sample()
    Dim wb As Workbook
    Dim Count_of_Lines As Long
    
    Set wb = Workbooks.Open("C:\BOOK1.XLSM")
    
    With wb.VBProject
        '~~> Components are like sheet1, thisworkbook, module etc.
        If .VBComponents.Count > 0 Then
            For i = 1 To .VBComponents.Count
                Count_of_Lines = Count_of_Lines + .VBComponents.Item(i).CodeModule.CountOfLines
                If Count_of_Lines > 0 Then Exit For
            Next
        End If
    End With
    
    If Count_of_Lines > 0 Then
        MsgBox "The workbook has macro"
    Else
        MsgBox "The workbook doesn't have a macro"
    End If
End Sub

NOTE: The code will not work if the destination file has it's VBA Project Locked.

HTH

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 3

Hi Sid,

Unfortunately, that's inconclusive too. I have "Require Variable Declaration" turned on in Tools > Options... in the VBE. As a consequence the ThisWorkbook and sheet modules will always contain 2 lines, namely "Option Explicit" and a blank line.

So your macro will return "The workbook has macro" for ALL my workbooks, even a newly created blank workbook.

HasVBProject correctly returns False if the ThisWorkbook and sheet modules only contain "Option Explicit".

---

Regards, Hans Vogelaar

Answer 4

>>>As a consequence the ThisWorkbook and sheet modules will always contain 2 lines, namely "Option Explicit" and a blank line.

Yup you have a point Hans. We can always check for "Option Explicit".

>>>HasVBProject correctly returns False if the ThisWorkbook and sheet modules only contain "Option Explicit".

It returns TRUE. I just tested it (with and without "Require Variable Declaration"). I have mentioned the code above.

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 5

It returns TRUE. I just tested it (with and without "Require Variable Declaration"). I have mentioned the code above.

Apparently we experience different behavior...

---

Regards, Hans Vogelaar

Answer 6

Hmm that is strange. What happens if you do as suggested below. What message box do you get?

EDIT: I tested it in Excel 2010 and I get "CONTAINS MACROS".

THE BELOW IS A QUOTE FROM ABOVE

To test it, create a workbook, say Book1.xlsm and insert a module but don't type any code. Close the file and in a new Excel file run this code from a module.

Sub Sample()
    Dim wb As Workbook
    
    Set wb = Workbooks.Open("C:\BOOK1.XLSM")
    
    If wb.HasVBProject Then MsgBox "CONTAINS MACROS" Else MsgBox "DOES NOT CONTAINS MACROS"
End Sub

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 7

Oh OK, we're talking at cross purposes.

If I create a new workbook and create an empty module, both your code and ActiveWorkbook.HasVBProject report that the workbook has macros.

If I create a new workbook and don't do ANYTHING with it, your code still reports that the workbook has macros - even if the workbook is saved as a .xlsx workbook! HasVBProject, however, returns False.

---

Regards, Hans Vogelaar

Answer 8

Ah! I was already looking under the excel hood to see why we were getting these differences. :)

>>>If I create a new workbook and don't do ANYTHING with it, your code still reports that the workbook has macros - even if the workbook is saved as a .xlsx workbook!

That is because of "Require Variable Declaration" as you mentioned earlier. In such a case we can go in for 2 options

1) Check the file type and based on the extension decide whether the file needs to be checked for Macro or not.

2) If the count of line is less then 3 then check for "Option Explicit" and a Blank Line :)

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 9

Not fully tested but Hans (as a favor) could you please check this with xlsx or an xlsm which has "Require Variable Declaration". And if it works then all thanks to you ;) I am sure this can be improved more :)

Sub Sample()
    Dim wb As Workbook
    Dim Count_of_Lines As Long
    Dim StrCode As String
    
    Set wb = Workbooks.Open("C:\Users\Siddharth Rout\Desktop\book2.xlsm")
    
    Select Case UCase(Split(wb.Name, ".")(UBound(Split(wb.Name, "."))))
    '<~~ Excel files which can have a macro.
    Case "XLS", "XLSM", "XLTM", "XLT", "XLA", "XLSB", "XLAM"
        '~~> Taking this approach as there are very few extensions which support macros
    Case Else
        MsgBox "The workbook doesn't have a macro"
        wb.Close SaveChanges:=False
        Exit Sub
    End Select
    
    Count_of_Lines = 0
    
    With wb.VBProject
        '~~> Components are like sheet1, thisworkbook, module etc.
        If .VBComponents.Count > 0 Then
            For i = 1 To .VBComponents.Count
                '~~> get the entire code in the module
                StrCode = Trim(.VBComponents.Item(i).CodeModule.Lines(1, _
                .VBComponents.Item(i).CodeModule.CountOfLines))
                
                If checkstatus(StrCode) = False Then
                    Count_of_Lines = Count_of_Lines + .VBComponents.Item(i).CodeModule.CountOfLines
                End If
            Next
        End If
    End With
    
    If Count_of_Lines > 0 Then
        MsgBox "The workbook has macro"
    Else
        MsgBox "The workbook doesn't have a macro"
    End If
End Sub

Function checkstatus(strg As String) As Boolean
    Dim ar
    Dim strTemp As String
    strTemp = strg
    If InStr(1, strTemp, vbNewLine) Then
        Do While InStr(1, strTemp, vbNewLine) > 0
            strTemp = Replace(strTemp, vbNewLine, "")
        Loop
        strTemp = Trim(strTemp)
    Else
        strTemp = Trim(strg)
    End If
    If Trim(strTemp) = "Option Explicit" or Len(Trim(strTemp))=0 Then checkstatus = True
End Function

EDIT: There can be one more way if it is just to check there are macros or not. This method however will not work if you plan to decide on how to save the file. We can always check for certain keyword like "Private", "Sub", "Function" etc to validate if a "Macro" is present or not. However it is not a reliable method to check if the VBComponent has any "Code" or not as we can just have a single line as "Dim xyz as Long"

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 10

Binary workbooks with extension .xlsb can also contain macros, as can Excel 2007/2010 add-ins with extension .xlam.

You should also take lines such as the following into account:

Option Compare Binary
Option Compare Text
Option Private Module
Option Base 0
Option Base 1

And what about modules that contain only declarations?

But apart from that, your code works fine.

---

Regards, Hans Vogelaar

Answer 11

>>> You should also take lines such as the following into account:

You are absolutely correct. The code that I gave is just a "tester", which can be improved more.

>>> And what about modules that contain only declarations?

They are not "macros" in that case :) Please see my edit in the previous post.

And thanks for testing. Much appreciated. :)

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 12

Here is a sure shot way to check if the file has a macro or not. This method is not for checking if the workbook has any code or not. I am checking for "SUB" and "FUNCTION". Though by strict definition a macro doesn't have a "FUNCTION". You can omit that.

Sub Sample()
    Dim wb As Workbook
    Dim HasMacro As Boolean
    Dim StrCode As String
    
    '~~> Open the file to check if it has any MACRO
    Set wb = Workbooks.Open("C:\Users\Siddharth Rout\Desktop\book2.xlsm")
    
    Select Case UCase(Split(wb.Name, ".")(UBound(Split(wb.Name, "."))))
    
    '<~~ Excel files which can have a macro.
    Case "XLS", "XLSM", "XLTM", "XLT", "XLA", "XLSB", "XLAM"
        '~~> Taking this approach as there are very few extensions which support macros
        With wb.VBProject
            '~~> Components are like sheet1, thisworkbook, module etc.
            If .VBComponents.Count > 0 Then
                For i = 1 To .VBComponents.Count
                    '~~> get the entire code in the module
                    StrCode = .VBComponents.Item(i).CodeModule.Lines(1, .VBComponents.Item(i).CodeModule.CountOfLines)
                    StrCode = " " & Replace(StrCode, vbCrLf, " ") & " "
                    If InStr(StrCode, " Sub ") > 0 Or InStr(StrCode, " Function ") > 0 Then
                        HasMacro = True
                        Exit For
                    End If
                Next
            End If
        End With
    End Select
    
    wb.Close SaveChanges:=False
    
    If HasMacro Then
        MsgBox "The workbook has macro"
    Else
        MsgBox "The workbook doesn't have a macro"
    End If
End Sub

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 13

Finally inspired, I wrote a blog post on it!!!

Thanks Hans :)

Topic: Check if an Excel File has a Macro

Link: http://siddharthrout.wordpress.com/2012/04/12/check-if-an-excel-file-has-a-macro/?preview=true&preview_id=619&preview_nonce=a4b5927d43

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 14

Hi Hans,

Thanks for your quick response. Unfortunately we are using the 2003 Interop of office control. So I am trying to check whether Siddharth's suggestion is working or not in my case.

Thanks again.

Regards

Bino Mathew

Answer 15

Hi Siddharth,

Thank you for your quick response. I tried to implement the suggestion from you to identify the macro in the excel . It is working but one problem is there. The VBProject code is working only if we check the “Trust access to the VBA project object model “ manually (In the trust center -> Developer macro settings) in the excel. Is this the only way to activate vbproject code? or can we do it through code? I am using office 2003 interops.

Thanks in advance

Regards

Bino Mathew

Answer 16

I fear that there is no way to find out in Excel 2003 without trusting access to the VBA project object model.

---

Regards, Hans Vogelaar

Answer 17

>>>Is this the only way to activate vbproject code? or can we do it through code? I am using office 2003 interops.

Yes that is the only way.

Bino, I agree with Hans. Unfortunately you cannot manipulate those settings from Code. And it is for the good in one way as it prevents malicious code from executing.

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.