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 which 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 up 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. Simply 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 actually 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…