I tried Allen Browne's CountLines solution for Access in Excel VBA, and found it incompatible. So I went off searching elsewhere for capabilities to do such in Excel VBA. Here is the solution I came up with which is similar to his CountLines:
Note: I developed / tested this with Excel 2007.
Note: I developed / tested this with Excel 2007.
Code:
Rem /************************************************************************************/
Rem /* FILENAME : mod_countlines */
Rem /* TYPE : VBA Module */
Rem /* DESCRIPTION : VBA code which preforms a spreadsheet wide LOC analysis, code */
Rem /* based on Allen Browne's: */
Rem /* http://allenbrowne.com/vba-CountLines.html and Microsoft's: */
Rem /* http://msdn.microsoft.com/en-us/library/office/dd890502%28v=office.11%29.aspx */
Rem /* */
Rem /* AUTHOR : Michael D Lueck */
Rem /* mlueck@lueckdatasystems.com */
Rem /* */
Rem /* NEEDS : */
Rem /* */
Rem /* USAGE : Enter "? CountLinesExcel()" into the Immediate window */
Rem /* */
Rem /* REVISION HISTORY */
Rem /* */
Rem /* DATE REVISED BY DESCRIPTION OF CHANGE */
Rem /* ---------- ---------- ------------------------------------------------------- */
Rem /* 12/04/2012 MDL Initial Creation */
Rem /************************************************************************************/
Option Explicit
Public Function CountLinesExcel() As Long
'Declare variables to access the Excel 2007 workbook.
Dim objXLApp As Excel.Application
Dim objXLWorkbooks As Excel.Workbooks
Dim objXLABC As Excel.Workbook
'Declare variables to access the macros in the workbook.
Dim VBAEditor As VBIDE.VBE
Dim objProject As VBIDE.VBProject
Dim objComponent As VBIDE.VBComponent
Dim objCode As VBIDE.CodeModule
'Declare other miscellaneous variables.
Dim objModuleType As vbext_ComponentType
Dim intModuleCount As Integer
Dim lnActiveXDesignerLOCCount As Long
Dim lnClassModuleLOCCount As Long
Dim lnDocumentLOCCount As Long
Dim lnMSFormLOCCount As Long
Dim lnStdModuleLOCCount As Long
Dim lnTotalLOCCount As Long
Set VBAEditor = Application.VBE
'Open Excel and the open the workbook.
Set objXLApp = New Excel.Application
'Get the project details in the workbook.
Set objProject = VBAEditor.ActiveVBProject
'Iterate through each component in the project.
For Each objComponent In objProject.VBComponents
'Find the code module for the project.
Set objCode = objComponent.CodeModule
'Check if the current module is self
If objComponent.Name <> "mod_countlines" Then
'Add one to the module counter
intModuleCount = intModuleCount + 1
'Capture the current module's type
objModuleType = objComponent.Type
'Remember this module's LOC count in the correct category counter
Select Case objModuleType
Case vbext_ct_ActiveXDesigner
lnActiveXDesignerLOCCount = lnActiveXDesignerLOCCount + objCode.CountOfLines
Case vbext_ct_ClassModule
lnClassModuleLOCCount = lnClassModuleLOCCount + objCode.CountOfLines
Case vbext_ct_Document
lnDocumentLOCCount = lnDocumentLOCCount + objCode.CountOfLines
Case vbext_ct_MSForm
lnMSFormLOCCount = lnMSFormLOCCount + objCode.CountOfLines
Case vbext_ct_StdModule
lnStdModuleLOCCount = lnStdModuleLOCCount + objCode.CountOfLines
Case Else
'Oops!
Debug.Print "Unrecognized module type below..."
End Select
'Report the current module
Debug.Print objCode.CountOfLines, objComponent.Name
End If
Set objCode = Nothing
Set objComponent = Nothing
Next
'Report the category totals
Debug.Print
Debug.Print "Totals per module type:"
Debug.Print lnActiveXDesignerLOCCount, "ActiveXDesigner module(s)"
Debug.Print lnClassModuleLOCCount, "ClassModule module(s)"
Debug.Print lnDocumentLOCCount, "Document module(s)"
Debug.Print lnMSFormLOCCount, "MSForm module(s)"
Debug.Print lnStdModuleLOCCount, "StdModule module(s)"
'Calculate the grand total
lnTotalLOCCount = lnActiveXDesignerLOCCount + lnClassModuleLOCCount + lnDocumentLOCCount + lnMSFormLOCCount + lnStdModuleLOCCount
'Report the grand total
Debug.Print
Debug.Print "Grand total project size:"
Debug.Print lnTotalLOCCount & " line(s) in " & intModuleCount & " module(s)"
' Clean up and exit
Set objProject = Nothing
objXLApp.Quit
'Good return code is lnTotalLOCCount
CountLinesExcel = lnTotalLOCCount
End Function
Last edited:
