Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Finding Workbooks Containing Macros.

Finding Workbooks Containing Macros

by Allen Wyatt
(last updated June 5, 2017)

9

Richard's company, like many others, uses Excel quite a bit. In fact, they have thousands and thousands of Excel workbooks that they have collected over the years. Richard needs a way to find out which of those workbooks have VBA macros in them, without the need to open and inspect each workbook individually. He wonders if there is an easy way to do this.

One rather simplistic way to find all your workbooks containing macros is to just look for any files that use the XLSM or XLSB extensions. Workbooks that contain macros must be stored in files using these extensions. While not 100% foolproof, it is a good place to start.

You could also use the search capabilities of Windows (outside of Excel) and search for any file that contains the text "End Sub" or "End Function". That will quickly identify any potential candidate workbooks, as any VBA procedure must use one of these two statements at its end.

If you are using legacy workbooks (those developed using Excel 2003's file format), then you actually need to look inside each of the workbooks. This can be done programmatically, meaning that you could have a macro that opens each workbook in a folder and examines it to see if there are any macros within it.

As an example, you could create a macro that steps through each of the files in a directory and determines if the file is an Excel workbook. It can then open the file and check to see if it has a VBA project within it.

Sub FindMacros()
    Dim sPath As String
    Dim sFile As String
    Dim sFoundFiles As String

    'specify directory to use - must end in "\"
    sPath = "C:\MyData\Excel Data\"

    sFile = Dir(sPath)
    Do While sFile <> ""
        If InStr(sFile, ".xls") > 0 Then
            Workbooks.Open (sPath & sFile)
            If Workbooks(sFile).HasVBProject Then
                sFoundFiles = sFoundFiles & sFile & vbCrLf
            End If
            Workbooks(sFile).Close (False)
        End If
        sFile = Dir     ' Get next filename
    Loop
    If Len(sFoundFiles) = 0 Then
        MsgBox "No workbooks found that contain macros"
    Else
        sFoundFiles = "The following workbooks contain macros:" & _
          vbCrLf & vbCrLf & sFoundFiles
        MsgBox sFoundFiles
    End If
End Sub

This example uses the HasVBProject property (introduced to the Excel object model in Excel 2007) to determine whether the file has any macros or not. When complete, the macro displays a message box that lists those worksheets containing macros.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12466) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Finding Workbooks Containing Macros.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Merging Cells to a Single Sum

One way to make your worksheets less complex is to get rid of detail and keep only the summary of that detail. Here's how you ...

Discover More

Sign-in Sheets

Printed sign-in sheets are a staple at many meetings and seminars. Word can create them lickety-split just by using a few ...

Discover More

Changing the Time Zone

There are several times in Drive where the program needs to keep track of the time at which certain events occur. Here's how ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Displaying the Selected Cell's Address

Need to know the address of the cell that is currently selected? The function and macro highlighted in this tip will come in ...

Discover More

Self-Aware Macros

Sometimes it may be helpful for a macro to know exactly where it is being executed. This tip provides a way that you can ...

Discover More

Debugging a Macro

Part of writing macros is to make sure they work as you expect. This involves a process known as debugging. Here's how you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2013-03-03 04:47:06

Trevor

Hi Brian, I too would be interested in seeing your workbooks. I recently tried using MZ Tools to analyse a workbook. Brilliant piece of kit but it seemed to make Excel a little unstable so I've taken it down for the moment.

Please contact me at Trevor at "excelaid dot co dot uk"

Thanks


2013-02-24 06:56:01

Brian Hershman

Hi Peter,

The location of the various pieces of code that make up this procedure is important, so I will send you a complete workbook that does everything I mentioned.

I used the workbook by putting it into my XLSTART directory alongside Personal.xlsb so that it was automatically loaded each time Excel was started.

Much of the code and concepts is borrowed from various questions I asked in several Excel forums, and the final structure is derived from John Walkenbach's "Excel 2010 Power Programming" wthout which I would probably have got nowhere!

Expect the workbook email shortly.


2013-02-23 11:30:58

Peter Atherton

Hi Brian,

I would be interested in seeing these files (or just the code it self)

Regards
Peter

Email Peter_Atherton AT hotmail.com


2013-02-22 08:40:31

Brian Hershman

For my own personal interest, I have worked on this problem for the two weeks since it appeared and I finished my solution only yesterday evening.

Unfortunately it looks as if I misinterpreted the problem!

My solution is, in fact, a way to display the number of macros in every workbook when it is OPENED. It is derived from an earlier procedure that displays all the macros in any of the open workbooks and referenced addins. It includes SUBs and FUNCTIONs that Excel would not normally list.
If any reader is interested, I would gladly pass on either of the workbooks that contain these procedures.


2013-02-13 11:41:37

Peter Atherton

Hi Trevor

The problems I found were, the msgbox only gave me about 90 files before it ran out of lines, and if the macro is in a saved file then the code closes it before completion.

I made the changes you suggested and copied the code into a new unsaved book and ran it from there. The only remaining problem is that it picked up a gif and a txt file. here the edited code.

Option Explicit

Sub FindMacros()
'xltips Jacques Raubenheimer, Roger Mason, Leonard Gordon, _
and Brandon Moxley
Dim sPath As String
Dim sFile As String
Dim sFoundFiles As String
Dim i As Long, sFType As String
Dim counter As Long
Dim sTime As Double, eTime As Double

' specify directory to use - must end in "" _
change to suit
sPath = "C:Documents and SettingsPeterMy Documents"

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With


sFile = Dir(sPath)
counter = 1

On Error Resume Next

sTime = Timer
Do While sFile <> ""
i = i + 1

bHasMacros = False
If InStr(sFile, ".xls") > 0 Then
Workbooks.Open (sPath & sFile)
If Workbooks(sFile).HasVBProject Then
bHasMacros = True
counter = counter + 1
If counter Mod 3 = 0 Then
sFoundFiles = sFoundFiles & sFile & vbCrLf
Else
sFoundFiles = sFoundFiles & "," & vbTab & sFile
End If
End If
Workbooks(sFile).Close (False)

End If
' Write this in your new book
Cells(counter, 2).Value = sFile
sFile = Dir ' Get next filename
Loop

If Len(sFoundFiles) = 0 Then
MsgBox "No workbooks found that contain macros"
Else
sFoundFiles = "The following workbooks contain macros:" & _
vbCrLf & vbCrLf & sFoundFiles
MsgBox sFoundFiles, vbInformation, counter & " files with macros found"
End If

With Application
.DisplayAlerts = True
.EnableEvents = True
End With

End Sub



2013-02-09 06:57:14

Trevor

Oops, heh heh, senior moment ... hadn't actually looked at the order of the posts.

Maybe you should just delete the last post and this one ...


2013-02-09 06:54:32

Trevor

Interesting that the time looks as though it's going backwards ...


2013-02-09 06:52:49

Trevor

Update: I also had a few problems with it not being happy with some workbooks ... not investigated why yet. This can be "glossed over" with On Error Resume Next but this might miss some workbooks out of the list. There was also a file with a "password to open" which paused the execution (and, off the top of my head, I couldn't remember the password).

And, unfortunately, the MshBox, as anticipated only copes with about 32-33 file names ... and I did have, literally, hundreds.

As the macro has to "live" somewhere, I'd suggest that it lists the file names in a sheet in the macro's home workbook and any issues could be highlighted in the next column.

But, for all it might look like I'm complaining, I think the code forms the basis of a useful tool and it wouldn't take much to address the issues identified so far ... apart from the passworded workbook :)

Thanks


2013-02-09 05:53:08

Trevor

Couple of concerns about this. One is a little cosmetic in that a number of the workbooks I processed displayed a message "This workbook contains links that cannot be updated" which paused the execution. This can probably be overcome using DisplayAlerts=False. The next is perhaps more serious: my understanding is that if you open a workbook under macro control, macros are automatically enabled for the workbook being opened. This means that w Workbook Open event can/will be executed. This can be avoided using EnableEvents=False before opening the workbook. And finally, I chose to run the macro on a workbook with hundreds of workbooks in it (probably a mistake) but the string being generated might be huge. I didn't find out because the code crashed because of the other problems :)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.