lior03
05-20-2006, 10:45 PM
hello
the following macro is fro john walkenbach's book.
it give the number of lines of code of each module or worksheet in a workbook.
how can i sum it up at the bottom ( total number of code lineS)?
how can i number each row to know how many item i have(1 to last)?
On Error GoTo errhandler
Worksheets("components").Select
On Error GoTo errhandler
Dim Wb As Worksheet
For Each Wb In ActiveWorkbook.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, 1) = "number"
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
Exit Sub
errhandler:
If err.number = 9 Then
Worksheets.Add.name = "components"
With Worksheets("components")
Cells.clear
End With
End If
thanks
the following macro is fro john walkenbach's book.
it give the number of lines of code of each module or worksheet in a workbook.
how can i sum it up at the bottom ( total number of code lineS)?
how can i number each row to know how many item i have(1 to last)?
On Error GoTo errhandler
Worksheets("components").Select
On Error GoTo errhandler
Dim Wb As Worksheet
For Each Wb In ActiveWorkbook.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, 1) = "number"
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
Exit Sub
errhandler:
If err.number = 9 Then
Worksheets.Add.name = "components"
With Worksheets("components")
Cells.clear
End With
End If
thanks