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

Question

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?

Answer 1

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.

Answer 2

You can loop over the sheets and use the following syntax:

If ActiveWorkbook.VBProject.VBComponents(sheetName).CodeModule.CountOfLines <> 0 Then

sheetName being the name of the sheet.

For more advanced use, you should check this page which gives more advanced examples.