Count all VBA code lines in Workbook (Code)

Greetings all,

Initially Inspired by the_net_2.0 on https://www.access-programmers.co.uk/forums/threads/count-lines-of-code-in-a-vba-project.215677/, I wrote code to count how many lines of code are in your workbook (sheet/workbook events, userforms, modules ...). You can put this code in any Sub and it will work. 🙂 I used it in a message box (MsgBox) to tell the user how many lines of code there are, among other information. Let me know what you think or if you get errors.

Sub CodeCounter()

On Error GoTo CodeLineCount_Err
Dim CodeLineCount As Double
Set CodeLineCount_Var = ThisWorkbook.VBProject

	For Each CodeLineCount_Var In CodeLineCount_Var.VBComponents
		CodeLineCount = CodeLineCount + CodeLineCount_Var.CodeModule.CountOfLines
	Next

CodeLineCount_Total = CodeLineCount

CodeLineCount_Err:
Set CodeLineCount_Var = Nothing
MsgBox CodeLineCount_Total

End Sub

excel

Of course! I should have to begin with. Thank you for your request. Below is the revised/better version.

Option Explicit
Sub CodeCounter()

On Error GoTo CodeLineCount_Err
Dim CodeLineCount_Var As Object, CodeLineCount As Double, CodeLineCount_Total As Integer
Set CodeLineCount_Var = ThisWorkbook.VBProject

    For Each CodeLineCount_Var In CodeLineCount_Var.VBComponents
        CodeLineCount = CodeLineCount + CodeLineCount_Var.CodeModule.CountOfLines
    Next

CodeLineCount_Total = CodeLineCount

CodeLineCount_Err:
Set CodeLineCount_Var = Nothing
MsgBox CodeLineCount_Total

End Sub

Please let me know if you or anyone reading this has even better improvements.

3 Replies

GeorgieAnne

Iron Contributor

Aug 05, 2022

Thank You Kendethar
But would you please revise this code with Option Explicit set on.

Thanks
Georgie Aanne

Kendethar

Iron Contributor

Aug 14, 2022

GeorgieAnne

Of course! I should have to begin with. Thank you for your request. Below is the revised/better version.

Option Explicit
Sub CodeCounter()

On Error GoTo CodeLineCount_Err
Dim CodeLineCount_Var As Object, CodeLineCount As Double, CodeLineCount_Total As Integer
Set CodeLineCount_Var = ThisWorkbook.VBProject

    For Each CodeLineCount_Var In CodeLineCount_Var.VBComponents
        CodeLineCount = CodeLineCount + CodeLineCount_Var.CodeModule.CountOfLines
    Next

CodeLineCount_Total = CodeLineCount

CodeLineCount_Err:
Set CodeLineCount_Var = Nothing
MsgBox CodeLineCount_Total

End Sub

Please let me know if you or anyone reading this has even better improvements.

Juliano-Petrukio
Bronze Contributor
Sep 02, 2021
Congratulations.

Just a highlight that will be necessary the user enable the option "Trust access to the VBA project object model"

File" > "Options" > "Trust Center" > "Trust Center Settings"

And the library Microsoft Visual Basic For Applications Extensibility XX

Forum Discussion

Count all VBA code lines in Workbook (Code)

3 Replies

Resources