I recently discovered a couple of object properties that can provide a code line count for my VBA projects. These two properties can be easily read and then evaluated to supply a developer with an accurate code line count.

Presented here is a simple function that will return the number of lines of code in the project from which it is run. It counts the VBA code within the worksheets (Excel Objects), the forms, and the code modules. Class modules are also included in the count. The links to Microsoft documentation may suggest that these properties are only available for Microsoft Access, but they similarly work for Excel and Word. The examples in this post were written for Excel, but they can be simply pasted into Word VBA.

A link to an outside post at the end of this post better describes the two properties that are read to accumulate the returned figures. It also shows methods to display other values.

The LinesOfCode function…

The following function only returns the procedural line count (CountOfLines). If you are a purist you can add the declarative lines (CountOfDeclarationLines), too. The second example does that and more.

Public Function LinesOfCode()
' Return the number of lines of code within a VBA project
Dim vbeModule As Object

    LinesOfCode = 0
        For Each vbeModule In Application.VBE.ActiveVBProject.VBComponents
            LinesOfCode = LinesOfCode + vbeModule.CodeModule.CountOfLines
        Next vbeModule

End Function

You can also write a procedure to show the number of modules counted within the project. The VBComponents properties do not provide the module count. I merely added a loop counter to get the number. I have checked this code with several VBA projects in Excel and Word and it appears to be a sound value.

Add a Module Counter and other Return Values…

It is easy to display the number of VBA modules queried. Add a counter for each time the loop runs and increment the variable. In the following example, I added a message box to pop up and show the module count. Originally this was a check on the number of modules that were counted to ensure that all of the code was considered.

This Sub procedure ends with a Message Box that displays:

• The total lines of VBA code
• The number of the Declarative lines
• The number of Procedural lines
• The number of the modules queried (Basically the number of loops)

Copy the code and try these for yourself…

 Public Sub LinesOfCode()
       Dim vbaModule As Object   ' The VBA module object
        Dim lngNumMods As Long      ' The number of modules counted
        Dim LinesOfCode As Long     ' Total lines
        Dim lngDecLines As Long     ' Declarative lines
        Dim lngProcLines As Long    ' Procedural lines
        
        ' Zero out the variables and the counter
        lngDecLines = 0     ' Declarative lines (at the top of each module or form only)
        lngProcLines = 0    ' Lines of code (Procedural), including the Dim statements
        LinesOfCode = 0     ' Total of the Declarative and Procedural lines
        lngNumMods = 0      ' Number of modules and forms checks, actually the number of loops
        
        ' Check each module in the active VBE project(VBComponents)
        For Each vbaModule In Application.VBE.ActiveVBProject.VBComponents
            With vbaModule
                lngNumMods = lngNumMods + 1
                lngDecLines = lngDecLines + .CodeModule.CountOfDeclarationLines
                lngProcLines = lngProcLines + .CodeModule.CountOfLines
                LinesOfCode = LinesOfCode + (.CodeModule.CountOfLines + .CodeModule.CountOfDeclarationLines)
            End With
        Next vbaModule

        ' Send a message with the results
        MsgBox "Total lines of code: " & LinesOfCode & vbCrLf & "Declarative lines: " & lngDecLines & vbCrLf & "Procedural lines: " & lngProcLines & vbCrLf & "Modules: " & lngNumMods

End Sub

This article discusses the difference between the CountOfLines and the CountOfDeclarationLines properties fairly well. And that is that…