PDA

View Full Version : sum a table



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

Bob Phillips
05-21-2006, 01:56 AM
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, 1).Value = i - 1
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
Cells(i, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Exit Sub
errhandler:
If Err.Number = 9 Then
Worksheets.Add.Name = "components"
With Worksheets("components")
Cells.Clear
End With
End If