CODEWITHSUNDEEP
excelvba
Alekzander
Alekzander

Reputation: 946

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

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?

Upvotes: 8

Views: 27253

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

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.

Upvotes: 11

assylias
assylias

Reputation: 328608

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.

Upvotes: 3

Related Questions