lior03
03-23-2006, 12:59 AM
hello
the first macro work perfectly - it loops through all sheets in a workbook
find the right sheet ( with the name specified ) and do what i asked it to do.
Dim oVBC As Object
Dim wb As Workbook
Application.StatusBar = "leghty macro ,please wait"
X = 2
For Each wb In Workbooks
If Not SheetExists("projects") Then
Worksheets.add.Name = "projects"
End If
For Each oVBC In Workbooks(wb.Name).VBProject.VBComponents
If Workbooks(wb.Name).VBProject.Protection = vbext_pp_none Then
Call GetCodeRoutines(wb.Name, oVBC.Name)
End If
Next
Next
With Sheets("projects")
.[A1].Resize(, 3).Value = Array("Workbook", "Module", "Procedures")
.[A2].Resize(UBound(aList, 2), UBound(aList, 1)).Value = _
Application.Transpose(aList)
.Columns("A:C").Columns.autofit
With Cells
.Font.Bold = True
.HorizontalAlignment = xlRight
Application.StatusBar = ""
End With
End With
the other macro on the other hand altough defined exactly like the previous one,fail to do it's job. namely-it do not execute the code at the sheet i want it to.it do at the active sheet.
Dim wb As Worksheet
For Each wb In Worksheets
If Not SheetExists("components") Then
Worksheets.add.Name = "components"
End If
With Sheets("components")
Cells.HorizontalAlignment = xlRight
Cells.ClearContents
Dim vbp As VBProject
Dim numcomponents As Integer
Dim i As Integer
Set vbp = ActiveWorkbook.VBProject
numcomponents = vbp.VBComponents.Count
Application.ScreenUpdating = False
On Error Resume Next
End With
Cells(1, 2) = "type"
Cells(1, 3) = "lines"
For i = 2 To numcomponents
Cells(i, 2) = vbp.VBComponents(i).Name
Select Case vbp.VBComponents(i).Type
Case 1
Cells(i, 3) = "module"
Case 2
Cells(i, 3) = "class module"
Case 3
Cells(i, 3) = "userform"
Case 100
Cells(i, 3) = "document module"
End Select
Cells(i, 3) = _
vbp.VBComponents(i).CodeModule.CountOfLines
Next i
Next
what is wrong with the second code?.how do i loop between all seets in a workbook?
thanks
the first macro work perfectly - it loops through all sheets in a workbook
find the right sheet ( with the name specified ) and do what i asked it to do.
Dim oVBC As Object
Dim wb As Workbook
Application.StatusBar = "leghty macro ,please wait"
X = 2
For Each wb In Workbooks
If Not SheetExists("projects") Then
Worksheets.add.Name = "projects"
End If
For Each oVBC In Workbooks(wb.Name).VBProject.VBComponents
If Workbooks(wb.Name).VBProject.Protection = vbext_pp_none Then
Call GetCodeRoutines(wb.Name, oVBC.Name)
End If
Next
Next
With Sheets("projects")
.[A1].Resize(, 3).Value = Array("Workbook", "Module", "Procedures")
.[A2].Resize(UBound(aList, 2), UBound(aList, 1)).Value = _
Application.Transpose(aList)
.Columns("A:C").Columns.autofit
With Cells
.Font.Bold = True
.HorizontalAlignment = xlRight
Application.StatusBar = ""
End With
End With
the other macro on the other hand altough defined exactly like the previous one,fail to do it's job. namely-it do not execute the code at the sheet i want it to.it do at the active sheet.
Dim wb As Worksheet
For Each wb In Worksheets
If Not SheetExists("components") Then
Worksheets.add.Name = "components"
End If
With Sheets("components")
Cells.HorizontalAlignment = xlRight
Cells.ClearContents
Dim vbp As VBProject
Dim numcomponents As Integer
Dim i As Integer
Set vbp = ActiveWorkbook.VBProject
numcomponents = vbp.VBComponents.Count
Application.ScreenUpdating = False
On Error Resume Next
End With
Cells(1, 2) = "type"
Cells(1, 3) = "lines"
For i = 2 To numcomponents
Cells(i, 2) = vbp.VBComponents(i).Name
Select Case vbp.VBComponents(i).Type
Case 1
Cells(i, 3) = "module"
Case 2
Cells(i, 3) = "class module"
Case 3
Cells(i, 3) = "userform"
Case 100
Cells(i, 3) = "document module"
End Select
Cells(i, 3) = _
vbp.VBComponents(i).CodeModule.CountOfLines
Next i
Next
what is wrong with the second code?.how do i loop between all seets in a workbook?
thanks