My Blog

Check if an Excel File has a Macro

4

Today while answering a question in MSDN Excel Forum, I came across an interesting question. Can we check if the Excel File has a “Macro”. When I say “Macro“, I am not referring to just any “Code“.

I started off with a small piece of code and as the discussion went on in the thread, I realized that I had not taken several scenarios into consideration. For ex. What if the user had “Require Variable Declaration” checked. My code was just counting the lines and then deciding whether there was a macro or not. I would like to thank Hans Vogelaar for suggesting different scenarios which made it possible to narrow down the code to the specifics.

How do we check if the file has a Macro?

Logic: Strictly speaking Macros start with Sub” or Private Sub” or “Public Sub“. So if we check for “Sub” then we can decide if the file has any macros or not.

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim HasMacro As Boolean
    Dim StrCode As String
    Dim i As Long

    '~~> 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"
        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 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

However a Userform might also have “Sub” so how do we ignore that?

All VBComponents have a type. To check that simply run this code in an Excel file which has Sheets, Thisworkbook, Module, Userform and a Class Module.

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim i As Long

    Set wb = ActiveWorkbook

    With wb.VBProject
        '~~> Components are like sheet1, thisworkbook, module etc.
        If .VBComponents.Count > 0 Then
            For i = 1 To .VBComponents.Count
                Debug.Print .VBComponents.Item(i).Name
                Debug.Print .VBComponents.Item(i).Type
            Next
        End If
    End With
End Sub

You will notice that these are the types:

Name : ThisWorkbook Type : 100
Name : Sheet1       Type : 100
Name : Module1      Type : 1
Name : UserForm1    Type : 3
Name : Class1       Type : 2

So, you can actually trap the “type” in the above code to ignore say Userforms.

With wb.VBProject
    '~~> Components are like sheet1, thisworkbook, module etc.
    If .VBComponents.Count > 0 Then
        For i = 1 To .VBComponents.Count
            If .VBComponents.Item(i).Type = 3 Then
                '~~> Rest of your code
            End If
        Next
    End If
End With

You also use the Excel Constants instead of the above numbers for example, when you type.

.VBComponents.Item(i).Type =

Intellisense automatically gives you the options. See picture:

Taking this post a step forward.
  • How to Check if an Excel File has any code?

To check if there is any “Code” or not in an excel file you can use this code. This takes account of only “Option Explicit“. You can amend it to also take into considerations the following. One can also just check if the first word is “Option” to trap all the scenarios mentioned below.

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

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 any Code"
        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 Code"
    Else
        MsgBox "The workbook doesn't have any Code"
    End If
End Sub

'~~> Checking if the code doesn't have just blank lines or "Option Explicit" only
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 Or _
    Left(Trim(strTemp), 1) = "'" _
    Then checkstatus = True
End Function


EDIT

This edit was required as Doug Glancy gave some nice suggestions.
To access the VBA components as shown in the code above, the user needs to have checked “Trust access to the VBA project object model” in Macro Security. To do this, follow these steps:

  • Excel 2003: Click on menu Tools | Macro | Security to access the”Security” dialog Box. Under “Trusted Publisher” tab, check the box which says “Trust access to the Visual Basic Project
  • Excel 2007: Click on the “Office”  icon| Excel Options. You will get a dialog box as shown in the snapshot below. Click on “Trust Center Settings“. Under the “Macro Settings“, check the box which says “Trust access to  the VBA project object model
  • Excel 2010: Click on the “File” Tab | Options. You will get a dialog box as shown in the snapshot below. Click on “Trust Center Settings“. Under the “Macro Settings“, check the box which says “Trust access to  the VBA project object model
  1. Doug Glancy
    Doug Glancy04-15-2012

    Siddharth, I followed a link here from one of your many excellent answers on Stack Overflow. I have three suggestions:

    1. In order to access VBA components, the user needs to have checked “Trust access to the VBA project object model” in Macro Security. If not, they’ll get a runtime error. To check whether they have I use this function (I think it’s from John Walkenbach.)


    Function ProgrammaticAccessAllowed() As Boolean
    10 Dim vbTest As VBIDe.vbComponent

    20 On Error Resume Next

    30 Set vbTest = ThisWorkbook.VBProject.VBComponents(1)

    40 If Err.Number = 0 Then
    50 ProgrammaticAccessAllowed = True
    60 End If
    End Function

    2. Instead of checking file extensions, I’d use the Workbook.FileFormat property. That way if somebody changes an extension from “xls” to “xly” the test would still show it as a workbook that could contain macros. You need to use the numeric constant, not the enmerated one, so that earlier versions don’t have a runtime error when referring to file formats from later versions. From the debug window:

    ? XlFileFormat.xlAddIn
    18

    ? XlFileFormat.xlOpenXMLWorkbookMacroEnabled
    52

    3. The test returns a false positive if I have a workbook with one module that has just this line of code:

    ‘This is a test of a module with no Sub

    In order to fix this change this line:

    StrCode = .VBComponents.Item(i).CodeModule.Lines(1, VBComponents.Item(i).CodeModule.CountOfLines)

    to

    10 With .VBComponents.Item(i).CodeModule
    20 StrCode = .Lines(.CountOfDeclarationLines + 1, .CountOfLines)
    30 End With

    This will skip over the module declaration code. I think you could also have the same problem if a Function, Property, etc., has a comment with the word “Sub” in it. In that case you’d have to check procedure by procedure, or maybe just check for comments.

    • Siddharth Rout
      Siddharth Rout04-15-2012

      Excelllent Suggestions. I will format the code given by you and add it to my above post :) Thanks Doug.

      • Siddharth Rout
        Siddharth Rout04-15-2012

        Regarding your 3rd point we can also tackle it by just amending the line

        If InStr(StrCode, " Sub ") > 0 Then

        to

        If InStr(StrCode, " Sub ") > 0 And Left(Trim(StrCode), 1) PUT_NOT_EQUAL_TO_SIGN_HERE "'" Then

  2. Doug Glancy
    Doug Glancy04-15-2012

    Siddharth, If you could format the code above that would be great.

Leave a Reply