counting how many lines of code in each workbook - memory issues
 

I am trying but...

Excel doesn't seem to be up to the task.

The aim was to list all workbooks with macros....to plan an upgrade/
s...to office 2007.

If there are a few addins that were converted back to .xls files, then
you get heaps of ghosts in your vb project and it fails.

Will post the rest of the code if anyone thinks they can do it..

Essentially, each time trying to count the lines of code to make sure
that there are some macros....
That is what is creating the memory leaks and ghost projects...

This goes crazy in both excel 2003 and 2007.....

Public Function CountLines(wb As Workbook) As Long Dim i As Integer
Dim lngLines As Long

' Debug.Print wb.VBProject.VBComponents.Count
lngLines = 0
For i = 1 To wb.VBProject.VBComponents.Count
' Debug.Print wb.VBProject.VBComponents.Item(i).Name
' Debug.Print wb.VBProject.VBComponents.Item(i).Type
If wb.VBProject.VBComponents.Item(i).CodeModule.Count OfLines 2
Then
lngLines = lngLines + wb.VBProject.VBComponents.Item
(i).CodeModule.CountOfLines
End If
Next i
CountLines = lngLines
End Function


Regards,
Tom