Solved

Determine all Excel WorkBooks with VBA code

Posted on 2011-03-23
4
366 Views
Last Modified: 2012-06-21
Experts,

I would like to find all Excel WorkBooks with any VBA code in them.
I have the following from
http://www.ozgrid.com/forum/showthread.php?t=58216&p=300667#post300667
But this only finds modules, how would I also determine if a sheet has VBA code?

Thanks
0
Comment
Question by:thydzik
  • 2
  • 2
4 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35200548
Another reference that might help.

Title: Determining If A Worksheet Or Workbook Has Code
Link: http://spreadsheetpage.com/index.php/site/tip/determining_if_a_worksheet_or_workbook_has_code/

Kevin
0
 
LVL 11

Assisted Solution

by:thydzik
thydzik earned 0 total points
ID: 35205344
zorvek, thanks for that;

final solution was

 
Sub getVBAWorkBooks()

Dim s As Worksheet
Set s = ThisWorkbook.Sheets("Listing")

s.Cells.Clear

Dim irow As Long

Dim folderPath As String
folderPath = "D:\"
If InStrRev(folderpatch, "\", -1, vbBinaryCompare) <> Len(folderPath) Then folderPath = folderPath & "\"

Dim fileStr As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False 'prevent workbook_open events

'find modules
Dim vbComp As VBComponent
Dim sht As Worksheet

Dim wb As Workbook
                
fileStr = Dir(folderPath & "*.xls")
Do While Len(fileStr) > 0
    Set wb = Workbooks.Open(folderPath & fileStr, xlUpdateLinksNever)
    For Each vbComp In wb.VBProject.VBComponents
        irow = irow + 1
        s.Cells(irow, "A").Value = fileStr
        s.Cells(irow, "B").Value = CompTypeToName(vbComp)
    Next vbComp
    
    
    For Each sht In wb.Worksheets
        If wb.VBProject.VBComponents(sht.CodeName).CodeModule.CountOfLines > 0 Then
            irow = irow + 1
            s.Cells(irow, "A").Value = fileStr
            s.Cells(irow, "B").Value = sht.Name
        End If
    Next sht

    Application.DisplayAlerts = False
    wb.Close
    Application.DisplayAlerts = True
    
    fileStr = Dir$()
Loop

Application.ScreenUpdating = True
End Sub

Function CompTypeToName(vbComp As VBComponent) As String
    Select Case vbComp.Type
        Case vbext_ct_ActiveXDesigner
            CompTypeToName = "ActiveX Designer"
        Case vbext_ct_ClassModule
            CompTypeToName = "Class Module"
        Case vbext_ct_Document
            CompTypeToName = "Document"
        Case vbext_ct_MSForm
            CompTypeToName = "MS Form"
        Case vbext_ct_StdModule
            CompTypeToName = "Standard Module"
        Case Else
    End Select
End Function

Open in new window

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35211697
I created my own version:

Public Function IsVBACodeInWorkbook( _
        ByVal Workbook As Workbook _
    ) As Boolean

' Return True if any VBA code exists in the workbook.

    Dim VBComponent As VBComponent
   
    For Each VBComponent In Workbook.VBProject.VBComponents
        Select Case VBComponent.Type
            Case vbext_ct_ActiveXDesigner
                IsVBACodeInWorkbook = True
            Case vbext_ct_ClassModule
                IsVBACodeInWorkbook = True
            Case vbext_ct_Document
                If VBComponent.CodeModule.CountOfLines > 0 Then
                    If VBComponent.CodeModule.CountOfLines = 2 Then
                        If VBComponent.CodeModule.Lines(1, 2) <> "Option Explicit" & vbCrLf Then
                            IsVBACodeInWorkbook = True
                        End If
                    Else
                        IsVBACodeInWorkbook = True
                    End If
                End If
            Case vbext_ct_MSForm
                IsVBACodeInWorkbook = True
            Case vbext_ct_StdModule
                IsVBACodeInWorkbook = True
            Case Else
        End Select
        If IsVBACodeInWorkbook Then Exit Function
    Next VBComponent
   
End Function

From a security perspective, Excel considers a workbook contains macros (VBA code) if any regular, form, or class module exists (even if it doesn't contain any code) or the ThisWorkbook or any sheet module contains any code beyond the default "Option Explicit" declaration line.

Kevin
0
 
LVL 11

Author Closing Comment

by:thydzik
ID: 35238998
accepted my complete code as the solution. points awarded to zorvek for the expert help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now