How to determine quickly which sheets have macros in Excel Workbook?

excel, vba

Solution

I answered a question recently in MSDN and ultimately wrote a blog post on it.

Topic: Check if an Excel File has a Macro

Link: http://www.siddharthrout.com/2012/04/12/check-if-an-excel-file-has-a-macro/

You can then use `.VBComponents.Item(i).Name` with `.VBComponents.Item(i).Type` to check which "Sheets" have "macros".

EDIT

Technically speaking every macro is a piece of code but it's not necessary that every piece of code is a macro. So if you are just checking for macros then see the 1st part of the blog post and if you are checking for any code then look at the 2nd part of the blog post.

Problem

If an Excel Workbook has many sheets and some of them have macros inside, the only way I have found to determine which sheets have macro is by clicking each of them in Project Explorer (Ctrl+R in VBA). Is there any other way to do this?

Original source