Question posted 2012 · +7 upvotes
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?
Accepted answer +10 upvotes
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.
Excel VBA objects referenced (4)
VBComponents.Item— Allow users to add items to an unbound combo boxVBComponents.Item— Create a Sendable Item for a Specific Account Based on the Current Folder (Outlook)Workbook— Workbooks and WorksheetsWorkbook— Add a Table of Contents to a Workbook
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.